CODEWITHSUNDEEP
.netdatabaseentity-frameworkentity-framework-4.1
norbip
norbip

Reputation: 635

Mapping navigation properties in inherited entites (TPH)

I have the following database schema:

database schema I'm using Table-per-Hiearchy (TPH) inheritance. The "Type" field of Cases table determines if a case is an email case or a twitter case. Both of them is an 1:N relation to Cases table.

I'm using database first approach with EF, and this is the model I want to use:

ef

The problem is that I cannot map navigation properties into child table, using the foreign keys (Email.CaseId <-> Cases.Id and Tweets.CaseId <-> Cases.Id). What I would like to achieve that the EmailCase entity have a navigation property to emails, and the TwitterCase entity have one to Tweets.

I can make it work only if I add the association then the navigation manually but of course this won't be reflected in the database.

How can I solve this problem? Should I use Table-per-Type (TPT) instead of TPH? But in this case, for example the EmailCase would only contain an ID to the original case nothing else which sounds strange for me. (This is what would be generated if I use the model first approach.)

Or should I go with manually adding associations and navigation properties?

Upvotes: 2

Views: 1413

Answers (1)

Slauma
Slauma

Reputation: 177163

This is difficult to answer because there are two obvious benefits - one votes for TPH and the other for TPT:

  • You model is perfectly suited for TPH because all properties are in the base class and the derived classes add no property to the table. So, all queries for the three entities (or two if Cases is abstract) only need this table and no joins are involved -> good for performance. On the other hand the database would allow inconsistencies of your model, like having an Emails record with CaseId=1 refering to a Cases record with this Id=1 but the Type discriminator is a TwitterCase. If you only use EF to access the database this sutuation should never occur if EF does its job correctly. If you have other sources which might change the database it could happen.

  • TPT would solve the problem of possible model inconsistencies because CaseId in the Emails table would refer then to the EmailCases table and CaseId in the Tweets table to the TwitterCases table. But the price is that you have these weird tables which only contain a single column for the primary key and every query - no matter how simple it is - will involve a join between the Cases table and the tables for the derived types -> bad for performance.

What is more important for you? Personally I would slightly lean towards TPH in your specific case - due to the performance benefit - if only your EF application accesses the database. But I could not finally decide what to do without knowing the whole context of your application.

Upvotes: 2

Related Questions