Left Lobe Logic

Getting to know Entity Framework – Table Per Type (TPT) Inheritance March 2, 2011

I’ve been using LINQ to SQL for quite sometime because until recently I’ve had little need for Entity Framework’s EDMX mappings because we’ve generally been content with one-to-one table to entity mappings that LINQ to SQL gives us. However, with the addition of a new module to our project that is close, but not quite the same as an existing module we’ve had to call on Entity Framework to generate a proper inheritance hierarchy.

What is Table Per Type (TPT) inheritance?

It’s the method of defining your SQL schema such that each sub-type will live in it’s own table and share a primary key column with the parent table via a foreign key. That is, the primary key on the child table will be a one-to-one mapping to the parent table.

Shows how Employee will be related to Contact using a common primary key linked via a foreign key

Note: My naming standard is to name primary key’s “Id” and any foreign key’s with the table name (and potentially a verb) prefixed. E.g. “ContactId”.

Generate your EDMX file

Start your .edmx (ADO.NET Entity Data Model Designer) as described in many tutorials…

Initially your tables will come through as associated entities. This is where we’ll pick up…

1. From the Toolbox, pickup the “Inheritance” and join from Employee to Contact.
2. Delete the Association joining the two tables.
3. Now you’ll find that if you try to compile, you’ll get:
   

   Error 3024: Problem in Mapping Fragment starting at line 79: Must specify mapping for all key properties (Contact.Id) of the EntitySet Contact.

   The problem here lies in the fact that our Employee’s ContactId didn’t auto-map to the Contact’s Id field.

4. Click on Employee, open the Mapping Details view and select the Column Mapping for ContactId
5. Change the mapping using the drop down to “Id”. The drop down now includes all properties from Contact as well.
   

   Map the Employee's ContactId column to the Contact's Id column

6. Again, you’ll find that if you try to compile, you’ll get:
   

   Error 11009: Property ‘ContactId’ is not mapped.

   Wait… Sure it is! Didn’t we just map it to the Contact’s Id column. Ummmm? Well, we won’t need it anyway. So just delete it from the Employee entity’s diagram.

   

   Delete the Employee's ContactId field

7. Compile, and all is finally well!