Implementing Table-Per-Type Inheritance with EF-4

In recent project, we encountered a business model that seemed to naturally lend to implement table inheritance. It was non-trivial to implement in database model; there were quite a few different ways to do this. Finally, we decided to use table-per-type inheritance and we used Entity Framework 4 as our ORM tool.

Here are several things that we learn:

First, in EF-4, there are multiple ways to implement table inheritance (check the third blog post below for more detail). In summary though, if you want performance - go for Table-Per-Hierarchy, but you lose flexibility and database validation among other things (in our case, performance is not yet a paramount issue).

Second, we need to model one-to-either constraints. Here is an example model diagram (not the real one).

For a given item, if it is a Car, our business model require that same itemId can not appear in other sub-tables (Motorbike and Accessory), it can only appear once in its hierarchy tables (Car, Vehicle, and Item). To address this we added a 'type' field in both the parent and the child table, enforce that field to be 'constant' in the child-table (e.g. we use PERSISTED field in SQL Server) and then apply an FK for the ItemId and the 'type' field (check the fourth blog post below for detail).

Third, we encounter several hurdles in implementing the TPT Inheritance in EF-4 - mostly because we need to get the data model just right so we do not have to hack EF-4. In some ways it is a good thing, because later we realize that it is actually kind of forcing our data model to be clean. So, here are a few tips that helped us implement this cleanly in EF-4:

1) Make sure only a single field PK throughout the inheritance chain (e.g. ItemId).

2) Use constraint on the parent to allow the FK from sub to work. (we can't have an FK ItemId, ItemTypeId to the Item table unless those combo is a unique key on the Item table). E.g. on the Item table, apply CONSTRAINT Item_AltPK UNIQUE (ItemId, ItemTypeId).

3) In EF Designer, on the child table, make sure to rename the Model's (not the Store's) xxxTypeId field (e.g. xxxTypeIdPersisted) - and map it to the sub table xxxTypeId field. Then, in code, just don't do anything to that field (it's a PERSISTED field on the sub table).
 
4) You can then have *n-level of inheritance* as well as one-to-either constraint this way... and no single error on the EF designer yay... life is good!!!


Finally, here are a few blog posts that has helped us:

Step-by-step example on how to implement Table-Per-Type Inheritance in Entity Framework 4

Step-by-step example on how to implement Table-Per-Hierarcy Inheritance in Entity Framework 4

A must read before deciding whether to use Table-Per-Type or Table-Per-Hierarchy

A Solution to implement One-to-Either Constraints