Inheritance with EF Code first:part 2–table per Type (TPT)

In the previous blog post you saw that there is 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 is too serious for our design since it results in denormalized schemas that CA n 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 SE E. TPT doesn ' t expose us to this problem. Table per Type (TPT)Table per Typeis about representing inheritance relationships asrelational foreign Key associations. Every class/subclass that declares persistent properties-including abstract classes-has its own table. The table forSubclassesContains columns only for eachnoninheritedproperty, declared by the subclass itself, along with a primary key, is also a foreign key of the base C Lass table. This approach was shown in the following figure:

For example, if a instance of the CreditCard subclass is made persistent, the values of properties declared by the Billin Gdetail base class is persisted to a new row of the Billingdetails table. The values of the properties declared by the subclass (i.e CreditCard) is persisted to a new row of the CreditCards tab Le. The both rows is linked together by their shared primary key value. Later, the subclass instance is retrieved from the database by joining the subclass table with the base class table. TPT Advantagesthe Primary advantage of this strategy are thatSQL schema is normalized. In addition, schema evolution are straightforward (modifying the base class or adding a new subclass is just a matter of MO Dify/add one table). Integrity constraint definition was also straightforward (note how Cardtype in creditcards table was now a non-nullable col umn). Implement tpt in EF Code Firstwe can create a TPT mapping simply by placingtable attribute on the subclasses to specify the mapped table name (table attribute is a new data annotation and H As been added ToSystem.ComponentModel.DataAnnotations namespace in CTP5):

Public Abstract classbillingdetail{ Public intBillingdetailid {Get;Set; } Public stringOwner {Get;Set; } Public stringNumber {Get;Set; }} [Table ("bankaccounts")] Public classbankaccount:billingdetail{ Public stringBankname {Get;Set; } Public stringSwift {Get;Set; }} [Table ("creditcards")] Public classcreditcard:billingdetail{ Public intCardtype {Get;Set; } Public stringExpirymonth {Get;Set; } Public stringExpiryyear {Get;Set; }} Public classinheritancemappingcontext:dbcontext{ PublicDbset<billingdetail> Billingdetails {Get;Set; }}

If you prefer the fluent API, then you can create a TPT mapping by using ToTable () method:

protected Override void onmodelcreating (Dbmodelbuilder modelBuilder) { modelbuilder.entity<BankAccount> (). ToTable ("bankaccounts"); Modelbuilder.entity<CreditCard> (). ToTable ("creditcards");}

Polymorphic Associationsa 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. For example, consider the Billinginfo property of the User in the following domain model. It references One particular Billingdetail object, which at runtime can is any concrete instance of the that class.

In fact, because billingdetail are abstract, the association must refer to a instance of one of its subclasses Only-credit Card or Bankaccount-at runtime. Implement polymorphic Associations with EF Code Firstwe don ' t has to does anything special to enable polymorphic Associatio NS in EF Code first; The user needs a unidirectional association to some billingdetails, which can is CreditCard or BankAccount so we just crea Te this association and it would is naturally polymorphic:

Public classuser{ Public intUserId {Get;Set; } Public stringFirstName {Get;Set; } Public stringLastName {Get;Set; } Public intBillingdetailid {Get;Set; } Public VirtualBillingdetail Billinginfo {Get;Set; }}

in the other words, as can see above, a polymorphic association are an association so may refer instances of a Subclass of the class that is explicitly specified as the type of the navigation property (e.g. user.billinginfo).  < br> The following code demonstrates the creation of a association to an instance of the CreditCard subclass:

using (var context = new Inheritancemappingcontext ()) {CreditCard CreditCard = new CreditCard () {Number = " 987654321 " , Cardtype = 1 }; User User = new user () {UserId = 1 , Billinginfo = CreditCard}; Context. Users.add (user); Context. SaveChanges ();}

Now, if we navigate the association in a second context, EF Code first automatically retrieves the CreditCard instance:

using (varnew Inheritancemappingcontext ()) { = context. Users.find (1); is CreditCard);}

Polymorphic associations with Tptanother important advantage of TPT are the ability to handle polymorphic associations. In the database a polymorphic association to a particular base class would be represented as a foreign key referencing the Table of that particular base class. (e.g. Users table has a foreign key, that references billingdetails table.)

Generated SQL for Queries Let's take an example of a simple non-polymorphic query that returns a list of all the Bankaccou Nts

var from inch 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-polymorphic query that requests all the billingdetails which includes both Ban Kaccount and CreditCard types:

var from inch Select b;

This LINQ query seems even more simple than the previous one but the resulting SQL query was not as simple as you might exp Ect:

As you can see, EF Code first relies on An inner join to detect the existence (or absence) of the rows in the SUBCLA SS Tables CreditCards and bankaccounts so it can determine the concrete subclass for a particular row of the Billingdetail s table. Also the sql case statements that's see in the beginning of the query is just to ensure columns that's Irrel Evant for a particular row has NULL values in the returning flattened table. (e.g. bankname for a row, represents a CreditCard type) TPT Considerationseven Though this mapping strategy are deceptively simple, the experience shows that performance can be UN Acceptable for complex class hierarchies because queries always require a join across many tables. In addition, this mapping strategy are more difficult to implement by Hand-even Ad-hoc reporting are more complex. This is a important consideration if you plan to use handwritten SQL in your application (for ad hoc reporting, database Views provide a-to offset tHe complexity of the TPT strategy. A view May is used to transform the Table-per-type model into the much simpler table-per-hierarchy model.)

Summaryin This post we learned on 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 key S is needed). This situation changes with the Table per concrete Type (TPC) that we'll discuss in the next post.

Original address: http://weblogs.asp.net/manavi/ Inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt

Inheritance with EF Code first:part 2–table per Type (TPT)