Getting to know Entity Framework – Table Per Type (TPT) Inheritance

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.

SQL Schema with an Employee joined one-to-one with Contact to demonstrate TPT inheritance
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.

    Mapping Columns in Mapping Details editor
    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 an entity field
    Delete the Employee’s ContactId field
  7. Compile, and all is finally well!
Advertisements

2 thoughts on “Getting to know Entity Framework – Table Per Type (TPT) Inheritance

  1. hi Andrew,
    thanks a lot for this post, it helped me to understand the mapping,
    but i dont know how to do it with 3 tables, i tried to delete the problematic property but i got more than 40 errors.
    im using the functions also.
    and i dont know if im doing it the right way.
    any help or hint will be appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s