A couple of weeks ago I was reviewing the demos of my Entity Framework course. Code-First demos where really poor so I worked to improve them.
While I was working on demos, I decided to create a series of blog post on this subject. In this first subject I discuss about Table Per Hierarchy inheritance mapping strategy.
Table Per Hierarchy Teory
Unlike OOP, database don't support inheritance. This means that we have to find a way to simulate inheritance in a relational database. One way of simulating inheritance in the database is Table Per Hierarchy (TPH) strategy. The TPH strategy is pretty easy. You create one table containing columns for all properties of all the classes in an inheritance chain. Additionally, the table contains a "discriminator" column which specifies what class the table row has data of.
Let's make an example. Suppose you have a PaymentInfo base class with four properties. Then you have a CreditCard class with two properties and a BankTransfer class with two properties. In such case, you create a table (say PaymentDetails) with 9 columns: four for PaymentInfo, two for CreditCard, two for BankTransfer and one for the Discriminator.
Putting it into figures here is the model, the database and their mapping:
If the Type column (the discriminator) is C, the row contains data about a credit card. If the Type column contains B, the row contains data about a bank account. This way you can persist an entire hierarchy of classes inside a single table. It's pretty easy sin't it? Let's see how to create, map and persist such model using Entity Framework Code First and DbContext APIs.
From theory to Practice: creating and mapping the model
To map the model the first thing we have to do is creating the model classes as follows.
public class PaymentDetailBase
{
public int Id { get; set; }
public string Number { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class BankTransfer : PaymentDetailBase
{
public string BIC_SWIFT { get; set; }
public string BankName { get; set; }
}
public class CreditCard : PaymentDetailBase
{
public string CCV { get; set; }
public DateTime ExpiryDate { get; set; }
}
Once the classes are created, we have to override the OnModelCreating method of the DbContext derived class and create the DbSet property:
public class MyContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<PaymentDetailBase>()
.Map<CreditCard>(s => s.Requires("Type").HasValue("C"))
.Map<BankTransfer>(s => s.Requires("BankTransfer").HasValue("B"))
.ToTable("PaymentDetails");
}
public DbSet<PaymentDetailBase> PaymentDetails { get; set; }
}
I have removed the mapping information for the single properties, but that's not important for this discussion. Now we can query the model and persist its data.
Persisting the model
To add a credit card we can write the following code;
using (MyContext ctx = new MyContext())
{
var cc = new CreditCard { ... };
ctx.PaymentDetails.Add(cc);
ctx.SaveChanges();
}
As you see there's no difference between persisting a simple class or a class in an inheritance hierarchy.
Querying the model
Let's see how to query the model:
The following query retrieves all credit cards:
ctx.PaymentDetails.OfType<CreditCard>();
The OfType<T> method is used to retrieve only the object of a given type in the hierarchy.
The following query retrieves the full name used in payment methods:
ctx.PaymentDetails.Select(c => c.FirstName + " " + c.LastName);
Performance considerations
When persisting an entity mapped usign TPH strategy in the database, EF generates a single SQL command because data are in one table. When querying, EF generates a single query for the same reason as before. This means that persisisting a class using the TPH strategy offers great performance.
Conclusion
I hope this post will help you in understanding better how TPH works with Entity framework. In a future post I'll cover the TPT strategy which addresses the same inheritance problem in another way.
Stay tuned...
Subscribe