Entity Framework Model Generation with TPH Detection

  • Apr 04, 2009 at 4:44 AM
  • Shawn Wildermuth
  • 6 Comments

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

Data

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:

ER Diagram

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:

EF Inheritance

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

 

 

Comments

Gravatar

Paul Fazzino Sunday, April 05, 2009

This is really cool and thx for the heads up.

I'd also be interested to know if EF is flexible enough to generate a model that works using a vertical table design. A good example for these is in many CMS systems where the structure is extended by the end user of the product rather than in the DB schema.

I know there's lots of advice against this approach but it can really add a huge amount of flexibility if used correctly.

Here was an interesting article regarding FriendFeed and how they ended up using this pattern (http://bret.appspot.com/entry/how-friendfeed-uses-mysql)

Gravatar

Shawn Wildermuth Sunday, April 05, 2009

Paul,

EF is powerful enough to do this but not with a static model (like the EDMX generates). EF allows you to create the models and store them in any way you need, the EDMX route is tooling that supports that but isn't the only way to do it.

Gravatar

Alex James Wednesday, April 29, 2009

Shawn,

Nice post. FYI I work with James T, he is very a switched on cookie. Most people have no idea how hard it is to do this sort of magic. It's beyond most mere morals.

Alex

Gravatar

dsoltesz Wednesday, April 29, 2009

It seems that the hierarchies for EdmGen2 are based off char columns as discriminants for entity hierarchies, we always use integer columns that are foreign keys to other tables that serve as enumerations and think EdmGen2 should support foreignkey and not char columns.

As in your example above, it would not generate the inherit model you created because you had productId as a int (fk)

Gravatar

Shawn Wildermuth Wednesday, April 29, 2009

dsoltesz,

The discriminator is based on the data you use in the tables, it does not need to be string based, it can be int based (and in fact this model is int based, the FK to the ProductType is the discriminator).


Leave a Comment

*
*
*