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?

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.