Entity Framework Model Generation with TPH Detection

Url: http://blogs.msdn.com/adonet/archive/2009/04/04…

One of my favorite patterns in the Entity Framework is a smarter way to use discriminators for data in a database. Using Tables per Hierarchy (TPH) or Tables per Type (TPT) all for more complex modeling of data in a relational data store. In a simple example, you might have a database that looks like this:

Notice that the ProductType is actually helping define what type of product it is (and therefore which decorator table holds additional information about that type). In the Entity Framework I like to model this as inheritance in the model:

Notice the connection between the Product and the Game/Accessory/Console tables is inheritance not a foreign key. A Game contains the properties defined by the Game type plus the properties defined by the Product type.  Its real inheritance. This allows us to make LINQ queries like so:

var qry = from g in ctx.Products
          where g is Game
          select g;

The second line is the magic part of the query.  This example shows searching through the Products in the model who are actually of the Type “Game”. So the model has knowledge in it that makes it easier to query the real intent of the data without having to trouble with the details.  The user of this model doesn’t need to worry about what properties make it a Game, but can just treat it as such. Very powerful and just using tooling to describe data we’ve always modeled like this.

Up to this point we’ve had to build these models ourselves (not always the easiest to do with the current designer). Today the ADO.NET EF team released a new version of the command-line compiler (EDMGEN) that can look for these patterns and produce the right model for you. The team used some resources over at MS Research to help provide heuristics to detect these patterns and do the right thing.  Check it out:

http://blogs.msdn.com/adonet/archive/2009/04/04/edmgen2-now-with-reverse-engineering-options.aspx