In the previous blog post you saw that there are three different approaches to representing an inheritance hierarchy and I explained Table per Hierarchy (TPH) as the default mapping strategy in EF Code First. We argued that the disadvantages of TPH may be too serious for our design since it results in denormalized schemas that can become a major burden in the long run. In today’s blog post we are going to learn about Table per Type (TPT) as another inheritance mapping strategy and we'll see that TPT doesn’t expose us to this problem.
Table per Type (TPT)Table per Type is about representing inheritance relationships as relational foreign key associations. Every class/subclass that declares persistent properties—including abstract classes—has its own table. The table for subclasses contains columns only for each noninherited property (each property declared by the subclass itself) along with a primary key that is also a foreign key of the base class table. This approach is shown in the following figure:
For example, if an instance of the CreditCard subclass is made persistent, the values of properties declared by the BillingDetail base class are persisted to a new row of the BillingDetails table. Only the values of properties declared by the subclass (i.e. CreditCard) are persisted to a new row of the CreditCards table. The two rows are linked together by their shared primary key value. Later, the subclass instance may be retrieved from the database by joining the subclass table with the base class table.
TPT Advantages The primary advantage of this strategy is that the SQL schema is normalized. In addition, schema evolution is straightforward (modifying the base class or adding a new subclass is just a matter of modify/add one table). Integrity constraint definition are also straightforward (note how CardType in CreditCards table is now a non-nullable column). Another much more important advantage is the ability to handle polymorphic associations (a polymorphic association is an association to a base class, hence to all classes in the hierarchy with dynamic resolution of the concrete class at runtime). A polymorphic association to a particular subclass may be represented as a foreign key referencing the table of that particular subclass.
Implement TPT in EF Code First We can create a TPT mapping simply by placing Table attribute on the subclasses to specify the mapped table name (Table attribute is a new data annotation and has been added to System.ComponentModel.DataAnnotations namespace in CTP5):
public abstract class BillingDetail
{
public int BillingDetailId { get; set; }
public string Owner { get; set; }
public string Number { get; set; }
}
[Table("BankAccounts")]
public class BankAccount : BillingDetail
{
public string BankName { get; set; }
public string Swift { get; set; }
}
[Table("CreditCards")]
public class CreditCard : BillingDetail
{
public int CardType { get; set; }
public string ExpiryMonth { get; set; }
public string ExpiryYear { get; set; }
}
public class InheritanceMappingContext : DbContext
{
public DbSet<BillingDetail> BillingDetails { get; set; }
}
If you prefer fluent API, then you can create a TPT mapping by using ToTable() method:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<BankAccount>().ToTable("BankAccounts");
modelBuilder.Entity<CreditCard>().ToTable("CreditCards");
}
Generated SQL For QueriesLet’s take an example of a simple non-polymorphic query that returns a list of all the BankAccounts:
var query = from b in context.BillingDetails.OfType<BankAccount>() select b;
Executing this query (by invoking ToList() method) results in the following SQL statements being sent to the database (on the bottom, you can also see the result of executing the generated query in SQL Server Management Studio):
Now, let’s take an example of a very simple polymorphic query that requests all the BillingDetails which includes both BankAccount and CreditCard types:
projects some properties out of the base class BillingDetail, without querying for anything from any of the subclasses:
var query = from b in context.BillingDetails
select new { b.BillingDetailId, b.Number, b.Owner };
--
var query = from b in context.BillingDetails select b;
This LINQ query seems even more simple than the previous one but the resulting SQL query is not as simple as you might expect:
--
As you can see, EF Code First relies on an INNER JOIN to detect the existence (or absence) of rows in the subclass tables CreditCards and BankAccounts so it can determine the concrete subclass for a particular row of the BillingDetails table. Also the SQL CASE statements that you see in the beginning of the query is just to ensure columns that are irrelevant for a particular row have NULL values in the returning flattened table. (e.g. BankName for a row that represents a CreditCard type)
TPT ConsiderationsEven though this mapping strategy is deceptively simple, the experience shows that performance can be unacceptable for complex class hierarchies because queries always require a join across many tables. In addition, this mapping strategy is more difficult to implement by hand— even ad-hoc reporting is more complex. This is an important consideration if you plan to use handwritten SQL in your application (For ad hoc reporting, database views provide a way to offset the complexity of the TPT strategy. A view may be used to transform the table-per-type model into the much simpler table-per-hierarchy model.)
SummaryIn this post we learned about Table per Type as the second inheritance mapping in our series. So far, the strategies we’ve discussed require extra consideration with regard to the SQL schema (e.g. in TPT, foreign keys are needed). This situation changes with the Table per Concrete Type (TPC) that we will discuss in the next post.
References
ADO.NET team blog
Java Persistence with Hibernate book
a
{
text-decoration: none;
}
a:visited
{
color: Blue;
}
.title
{
padding-bottom: 5px;
font-family: Segoe UI;
font-size: 11pt;
font-weight: bold;
padding-top: 15px;
}
.code, .typeName
{
font-family: consolas;
}
.typeName
{
color: #2b91af;
}
.padTop5
{
padding-top: 5px;
}
.padTop10
{
padding-top: 10px;
}
p.MsoNormal
{
margin-top: 0in;
margin-right: 0in;
margin-bottom: 10.0pt;
margin-left: 0in;
line-height: 115%;
font-size: 11.0pt;
font-family: "Calibri" , "sans-serif";
}
