Entity Framework 4 (EF4) currently supports three inheritance models: Table per Type (TPT), Table per Hierarchy (TPH), and Table per Concrete Type.  Given that the existing database schema I am working with mainly implements TPT, we’ll take a look at some of the issues you may experience when trying to map this to your model.

It’s worth mentioning that most ‘issues’ I experienced are because I have an existing database schema I am trying to map to.  I have found that working with Model First and generating a database schema is a good way to resolve such issues as it shows you quite clearly what EF4 is expecting.

No doubt, we will find that working with an existing database schema becomes more flexible in the future, but in the meantime: beware!

Incorrect Relationships in Database Schema

Take a look at the following MS SQL diagram:

Our existing code then had an abstract base class defined for Contact, and Student as a derived…and we populated this by ‘translating’ the LINQ-SQL into our own defined business objects.

However, when we attempt to define the same using an EDM we run into a problem:

We remove existing association and define Contact as base of Student

Now we remove ContactId from Student as it is defined in base (Error 19)

At this point we have gone from 1 error to multiple.

OK…why? Well, we actually have more than one issue in the above scenario.  The first is that our database schema defines a one-to-many relationship.  You need to ensure it is a one-to-one relationship for the inheritance to work. So let’s fix that:

Add unique index on the foreign key

Now we have fixed our database schema to show one-to-one:

Foreign Key TPT Inheritance

So let’s check out the model:

Hum, not what we were after…why? Well it appears that with TPT inheritance, the EDM designer requires that the foreign key ContactId is also the primary key in Student.

Solutions? Well Larry Tenny and Zeeshan Hirani present a recipe to deal with this in chapter 6 of their book Entity Framework 4.0 Recipes.

However, it involves a fair amount of sleeve rolling, and results in a database specific solution due to QueryViews and stored procedures…

The alternative is to change our database schema AGAIN (and someone did point out that “at least you can change your database schema!”)

Implement One-to-One the EF Way

So given the previous statement that ContactId needs to also be the primary key in Student:

OK, looking good so far…

  And now it builds without error.

Conclusion

Given an existing database schema, if you have not implemented it the EF way, then you have work to do :-( 

We had to run a database script to fix up the missing one-to-one relationships (which is fair enough; it should have been properly defined in the first place!), but the ‘bite’ comes from not being able to ‘easily’ use foreign keys to define that relationship.  Having to remove primary keys, and elevate foreign keys to being primary is clearly a bigger impact than constraining via a foreign key index.

Update 20/08/2010

As suggested by Zeeshan Hirani, here are the errors you get:-

• Error 19: A member named BaseId cannot be defined in class Model1.Child. It is defined in ancestor class Model1.Base.
• Error 3003: Problem in mapping fragments starting at line X:All the key properties (Bases.BaseId) of the EntitySet Bases must be mapped to all the key properties (Child.ChildId) of table Child.
• Error 3002: Problem in mapping fragments starting at line X:Potential runtime violation of table Child's keys (Child.ChildId): Columns (Child.ChildId) are mapped to EntitySet Bases's properties (Bases.ChildId) on the conceptual side but they do not form the EntitySet's key properties (Bases.BaseId)