Entity Framework 4 Table Per Hierarchy - How To Define Navigational Properties On Children?

Question

I currently have a Entity Framework 4.0 model in place with Table Per Type (TPT), but there are a few performance issues (lots of LOJ's/CASE statements), as well as an issue mapping between two particular domain areas (many-to-many).

I've decided to try out TPH.

I have an entity called "Location" which is abstract, and the base for all other entities.

I then have "Country", "City", "State", "Street", etc which all derive from Location.

"LocationType" is the dicriminator.

That part is working fine, but i'm having issues trying to define navigational properties for the derived types.

For instance, a "State" has a single "Country", so i should be able to do this:

var state = _ctx.Locations.OfType<State>().Include("Country").First();
var countryForState = state.Country;

But this would require a navigational property called "Country" on the "State" derived entity. How do i do this? When i generate the model from the database, i have a single table with all the FK's pointing to records in the same table:

(NOTE: I created those FK's manually in the DB).

But the FK's are placed as nav's on the "Location" entity, so how do i move these navigational properties down to the derived entities? I can't copy+paste the navs across, and i can't "create new navigational property", because it won't let me define the start/end role.

How do we do this?

It's also not clear with TPH if we can do it model-first, or we HAVE to start with a DB, fix up the model then re-generate the DB. I am yet to find a good example on the internet about how to define navs on children with TPH.

NOTE: I do not want to do code-first. My current solution has TPT with the EDMX, and pure POCO's, i am hoping to not affect the domain model/repositories (if possible), and just update the EF Model/database.

EDIT

Still no solution - however im trying to do model-first, and doing Add -> New Association, which does in fact allow me to add a nav to the derived entities. But when i try and "Generate database from Model", it still tries to create tables for "Location_Street", "Location_Country" etc. It's almost like TPH cannot be done model first.

EDIT

Here is my current model:

The validation error i am currently getting:

Error 1 Error 3002: Problem in mapping fragments starting at line 359:Potential runtime violation of table Locations's keys (Locations.LocationId): Columns (Locations.LocationId) are mapped to EntitySet NeighbourhoodZipCode's properties (NeighbourhoodZipCode.Neighbourhood.LocationId) on the conceptual side but they do not form the EntitySet's key properties (NeighbourhoodZipCode.Neighbourhood.LocationId, NeighbourhoodZipCode.ZipCode.LocationId).

Just thought i'd keep editing this question with edit's regarding where i am currently at. I'm beginning to wonder if TPH with self-referencing FK's is even possible.

EDIT

So i figured out the above error, that was because i was missing the join-table for the Neighbourhood-ZipCode many to many.

Adding the join table (and mapping the navs to that) solved the above error.

But now im getting this error:

Error 3032: Problem in mapping fragments starting at lines 373, 382:Condition members 'Locations.StateLocationId' have duplicate condition values.

If i have a look at the CSDL, here is the association mapping for "CountyState" (a State has many counties, a County has 1 state):

<AssociationSetMapping Name="CountyState" TypeName="Locations.CountyState" StoreEntitySet="Locations">
   <EndProperty Name="State">
      <ScalarProperty Name="LocationId" ColumnName="StateLocationId" />
   </EndProperty>
   <EndProperty Name="County">
      <ScalarProperty Name="LocationId" ColumnName="LocationId" />
   </EndProperty>
   <Condition ColumnName="StateLocationId" IsNull="false" />
</AssociationSetMapping>

It's that Condition ColumnName="StateLocationId" which is complaining, because ZipCodeState association also this condition.

But i don't get it. The discriminators for all entities are unique (i have triple checked), and i would have thought this was a valid scenario:

1. County has a single State, denoted by StateLocationId (Locations table)
2. ZipCode has a single State, denoted by StateLocationId (Locations table)

Is that not valid in TPH?

Quilting

Just curious: have you tried tuning your database and sticking with table per type? – Rochellrochella

@Paul Keister - performance is only one of the issues. My domain model also includes "Posts", which have a M-M with "Locations". With TPT, because each location has it's own table, it was very difficult to map a "Post" to many "Locations". Very hard to explain - but let it be said if i have 1 single "Locations" table, it will make the associations much more straightforward. – Quilting

I have also just tried adding extra FK's for the duplicated relationships EF was complaining about - e.g FK_County_State and FK_ZipCode_State as opposed to just FK_State shared by both), then rebuilt the model - still the EXACT same error. Don't tell me i have to create the additional actual columns too? (trying that now) – Quilting

RE my above comment - yep, i DID have to create those actual additional columns too. Which solved the above error - now of course i have the NEXT one. :) – Quilting

@Alex James - if your out there, can you please advise if there is a solution to this issue? (see my below answer). Otherwise i'm going to have to stick with TPT. – Quilting

Answer 1

So i solved a few of my issues, but i hit a brick wall.

First of all, when you create self-referencing FK's in the database side, when you try and "Update Model from Database", Entity Framework will add these navigational properties to the main base type, as it has no explicit sense of TPH - you need to do this in the model side.

BUT, you can manually add the navigational properties to the child types.

WRT this error:

Error 3032: Problem in mapping fragments starting at lines 373, 382:Condition members 'Locations.StateLocationId' have duplicate condition values.

That was because i had an FK called "Location_State" which i was attempting to use for the "ZipCode_State" relationship, AND the "City_State" relationship - which does not work (still no idea why).

So to solve that, i had to add extra columns and extra FK's - one called "ZipCode_State", and another called "City_State" - obviously it has to be a 1-1 between navs and physical FK's.

Location.LocationType has no default value and is not nullable. A column value is required to store entity data.

That is my discriminator field. In the database side, it is not nullable.

I read threads about this issue, and they said you need to change the relationships from 0..* to 1..* - but my relationships already were 1..*.

If you look at my "Locations" actual database table above, all the FK's are nullable (they have to be). Therefore i started wondering if my relationships should be 0..*.

But they are nullable because of the TPH - not all "Locations" will have a "State". But if that Location is a "City", then it HAS to have a "State".

My feelings were further comforted by this SO question: ADO EF - Errors Mapping Associations between Derived Types in TPH

I was actually trying that workaround (before i even came across it), and the workaround does not work for me. I even tried changing all the relationships from 1..* to 0..*, and still no luck.

Wasting too much time here, I've gone back to TPT.

At the end of the day, with TPH i would have had a ridiculously large table, with lots and lots of redundant, nullable columns. JOIN-wise, it's more efficient. But at least with TPT i am not required to have nullable and self-referencing FK's.

If anyone has a solution to this problem, let me know. But until then, im sticking with TPT.

Quilting

+1 At the moment I have some model with TPT inheritence and I thought about recucing the model with TPH. Now I know that it would be waste of time. – Wycoff

Yup, at the moment i have a table with like 10 nullable FK's. Looks really ugly. Will the JOINS perform better - probably, in the physical sense, but not in the logical sense. The only real difference between select * from tablea inner join table b (TPT) and select * from tablea as one inner join tablea as two (TPH), is TPH goes to the same table, TPT goes to a different one. The cost of the JOIN operation is the same (i think). My main concern is i don't see the point of so many nullable FKs – Quilting