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:

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:



Shawn Wildermuth
Author, Teacher, and Coach

My Courses

Wilder Minds Training
Vue.js by Example (New Lower Price)
Bootstrap 4 by Example (New Lower Price)
Intro to Font Awesome 5 (Free Course)
Building an API with ASP.NET Core (New Course)
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.2)
Less: Getting Started (New)
Using Visual Studio Code for ASP.NET Core Projects
Implementing ASP.NET Web API

Application Name WilderBlog Environment Name Production
Application Ver v4.0.30319 Runtime Framework x86
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.27617.04
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86