Inheritance with EF Code First: Part 2 – Table per Type (TPT)

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).

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 { getset; }
    public string Owner { getset; }
    public string Number { getset; }
}
 
[Table("BankAccounts")]
public class BankAccount : BillingDetail
{
    public string BankName { getset; }
    public string Swift { getset; }
}
 
[Table("CreditCards")]
public class CreditCard : BillingDetail
{
    public int CardType { getset; }
    public string ExpiryMonth { getset; }
    public string ExpiryYear { getset; }
}
 
public class InheritanceMappingContext : DbContext
{
    public DbSet<BillingDetail> BillingDetails { getset; }
}
If you prefer 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 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. For example, consider the BillingInfo property of User in the following domain model. It references one particular BillingDetail object, which at runtime can be any concrete instance of that class.
In fact, because BillingDetail is abstract, the association must refer to an instance of one of its subclasses only—CreditCard or BankAccount—at runtime.

Implement Polymorphic Associations with EF Code First

We don’t have to do anything special to enable polymorphic associations in EF Code First; The user needs a unidirectional association to some BillingDetails, which can be CreditCard or BankAccount so we just create this association and it would be naturally polymorphic:
public class User
{
    public int UserId { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }
    public int BillingDetailId { getset; }
 
    public virtual BillingDetail BillingInfo { getset; }
}
In other words, as you can see above, a polymorphic association is an association that may refer instances of a subclass of the class that was explicitly specified as the type of the navigation property (e.g. User.BillingInfo).

The following code demonstrates the creation of an 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 (var context = new InheritanceMappingContext())
{
    User user = context.Users.Find(1);
    Debug.Assert(user.BillingInfo is CreditCard);
}

Polymorphic Associations with TPT

Another important advantage of TPT is the ability to handle polymorphic associations. In the database a polymorphic association to a particular base class will 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 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:
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 Considerations

Even 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.)

Summary

In 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

Published Tuesday, December 28, 2010 11:41 PM by mortezam

Comments

# re: Inheritance Mapping Strategies with Entity Framework Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, December 29, 2010 3:21 PM by Yakup Ipek

Very nice explained article.Thanks

# re: Inheritance Mapping with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, January 5, 2011 8:14 AM by Juliën

Hi Morteza,

Excellent posts on the matter.

However, having read your posts on EF CTP5 mapping associations and several posts on the MSDN Forums I’m having difficulty with a “simple” mapping approach you mention. I hope you are willing to shed some light on it.

It regards a TPT project with EF Code First, CTP5. With this, there is a simple concept where every object (model) is derived from a BaseEntity. This works fine, except the 1-to-1 mapping strategy.

The question is quite straightforward: is the concept below possible?

 [Table("BaseEntity")]
  public abstract class BaseEntity
  {
      [Key, DatabaseGenerated(DatabaseGenerationOption.Identity)]
      public Guid Id { get; set; }
      public Guid Owner { get; set; }
      public DateTime DateAdd { get; set; }
      public DateTime DateMod { get; set; }
  }

  [Table("Customer")]
  public class Customer : BaseEntity
  {
      public string DisplayName { get; set; }
      public Address DeliveryAddress { get; set; }
      public Address BillingAddress { get; set; }
  }

  [Table("Address")]
  public class Address : BaseEntity
  {
      public string Streetname { get; set; }
      public string HouseNumber { get; set; }
      public string City { get; set; }
  }

Example - DbContext

My DbContext would look something like:

  public class MagicContext : DbContext
  {
      public DbSet<BaseEntity> Objects { get; set; }
      public MagicContext(string connectionString) : base(connectionString) { }
      protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
      {
          modelBuilder.Entity<Customer>()
                  .HasOptional(c => c.BillingAddress)
                  .WithRequired();

          // ??? How to map Address, if needed

      }
  }

In a nutshell:

1.      A Customer can have an optional –not required- DeliveryAddress and/or BillingAddress

2.      The properties DeliveryAddress as well as BillingAddress can refer to the same – or separate – records in the “Address” table

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, March 9, 2011 11:28 AM by Mickael

Hello,

Firstable I want to thank you for great posts you made about EF Code First CTP5.

Allow me to ask you a question about Table Per Type (TPT) strategy.

For example if I have a class hierarchy with a class named "SuperClass" and other class named "SubClass", the TPT strategy will create database table "SuperClass" and table "SubClass". How can I force the foreign key created to associate the two tables to have delete rule as Cascade?

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, March 9, 2011 5:46 PM by mortezam
@Mickael: As of CTP5, there is no way to switch on cascade delete between the related tables in the hierarchy. In fact, you don`t really need it since Code First will always delete both records in the Subclass and SuperClass tables once you remove a SubClass object. If you are interested to have cascade deletes turned on to preserve the database referential integrity, then you have to manually switch it on after Code First creates the database. I realize this isn't a great answer and I hope we would be able to do this by using fluent API in the RTM. Thanks :)

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Thursday, March 10, 2011 5:45 AM by Mickael

Thanks Morteza for the fast answer. Yesterday I recheck all my domain code and found that I was making some mistakes. I also disabled OneToManyCascadeDeleteConvention and manually delete child instances. That solved my problem.

Like you said in your last comment, deleting a record of a subclass automatically deletes the record of its superclass.

Thanks again.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Friday, March 18, 2011 10:13 AM by von

I have a problem with a class inheriting from an abstract class. If I set a property as HasOptional the mapping does not work. However, if I change it to HasRequired then everything works. This is the error message I get:

(13,10) : error 3032: Problem in mapping fragments starting at lines 13, 35:EntityTypes CodeFirstNamespace.Employee are being mapped to the same rows in table Employee. Mapping conditions can be used to distinguish the rows that these types are mapped to.

Here's the code:

public abstract class Person
{
    public Guid Id { get; set; }
}

public class Employee : Person
{
    public DateTime? DateOfBirth { get; set; }
    public virtual Department Department { get; set; }
}

public class Department
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Department> Departments { get; set; }

    protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>().ToTable("Person");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Entity<Employee>().HasRequired(e => e.Department);
    }
}

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Friday, March 18, 2011 11:27 AM by mortezam
@von: This was a bug in CTP5 which has been fixed in EF 4.1 RC. You can find the latest EF release from here. Once you install it, your code will perfectly create the desired schema. The only modification you need in your code is to change ModelBuilder to DbModelBuilder, which is the new name they choose for ModelBuilder class to align with the other core classes. Hope this helps :)

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Saturday, March 19, 2011 6:28 AM by von

Thanks Morteza! I'll give it a try.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Sunday, March 20, 2011 4:57 AM by von

I just like to say my issue has been resolved. Microsoft has done a good job on that one. Thanks to you again Morteza.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Sunday, March 20, 2011 10:24 AM by mortezam
@von: This seems to be a bug in EF 4.1 RC. You should be able to create a schema from this object model as you did in CTP5. One workaround would be to rename the Employee navigation property on Note1 and Note2 subclasses:

public class Note1 : Note
{   
    public virtual Employee Employee1 { get; set; }
}
 
public class Note2 : Note
{   
    public virtual Employee Employee2 { get; set; }
}

While this will resolve the issue, a better solution would be to move up the Employee property to the Note base class which not only creates the desired schema but also offers a better OO design. The generated schema would be a bit different though since in this way the Note table will hold the EmployeeId FK instead of Note1 and Note2 tables holding it:

public abstract class Note
{
    public virtual Guid Id { get; set; }
    public virtual string Description { get; set; }
    public virtual Employee Employee { get; set; }
}
 
public class Note1 : Note {  }
 
public class Note2 : Note {  }

Hope this helps :)

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Sunday, March 20, 2011 12:49 PM by mortezam
@von: I realize that my first suggested workaround is not a great solution, in fact, I was about to suggest another solution which was to change your independent associations to foreign key associations by exposing the FK properties (EmployeeId) in Note1 and Note2 subclasses like the following:

public class Note1 : Note
{
    public Guid EmployeeId { get; set; }
    public virtual Employee Employee { get; set; }
}
 
public class Note2 : Note
{
    public Guid EmployeeId { get; set; }
    public virtual Employee Employee { get; set; } 
}

However, I didn’t bring this up due to another bug in EF 4.1 RC and that is if you run this object model, you wouldn’t get an exception anymore but Code First creates the FK constraint on EmployeeId only on one of the subclass tables (Note1 in this case) and fails to create it on the other one. You may want to save this for the final RTM since foreign key associations are always recommended regardless.

More to the point, from what I can see, probably the best solution here is to replace inheritance with aggregation since you can well find that the inheritance isn’t adding any value to your object model except that it creates your desired DB schema which is well achievable in other ways:

public class Note
{
    public virtual Guid Id { get; set; }
    public virtual string Description { get; set; }       
}
 
public class Note1
{
    public virtual Guid Id { get; set; }
    public Guid EmployeeId { get; set; }
 
    public virtual Employee Employee { get; set; }
    public virtual Note Note { get; set; }       
}
 
public class Note2
{
    public virtual Guid Id { get; set; }
    public Guid EmployeeId { get; set; }
 
    public virtual Employee Employee { get; set; }
    public virtual Note Note { get; set; }       
}
 
public abstract class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
 
public class Employee : Person
{
    public DateTime? DateOfBirth { get; set; }
}
 
public class Context : DbContext
{
    public DbSet<Person> Persons { get; set; }
    public DbSet<Note> Notes { get; set; }
    public DbSet<Note1> Notes1 { get; set; }
    public DbSet<Note2> Notes2 { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>().ToTable("Person");
        modelBuilder.Entity<Employee>().ToTable("Employee");          
    
        modelBuilder.Entity<Note1>().HasRequired(x => x.Note).WithRequiredDependent();
        modelBuilder.Entity<Note2>().HasRequired(x => x.Note).WithRequiredDependent();       
    }
}

Here I created two Shared Primary Key Associations between Note and Note1 and Note and Note2 which at the end creates the very same database schema, of course without any exception :)

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Sunday, March 20, 2011 9:10 PM by Von

Okay so the code below did the trick. I think the problem before was, I declared EmployeeId as virtual in Note1 and Note2 and that instructed EF to create a column for it.

 public abstract class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Employee : Person
{
    public DateTime? DateOfBirth { get; set; }
    public virtual IList<Note1> SomeNotes { get; set; }
    public virtual IList<Note2> SomeOtherNotes { get; set; }
}

public abstract class Note
{
    public virtual Guid Id { get; set; }
    public virtual string Description { get; set; }
}

public class Note1 : Note
{
    public Guid EmployeeId { get; set; }
    public virtual Employee Employee { get; set; }
}

public class Note2 : Note
{
    public Guid EmployeeId { get; set; }
    public virtual Employee Employee { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Note> Notes { get; set; }
    public DbSet<Note1> Notes1 { get; set; }
    public DbSet<Note2> Notes2 { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>().ToTable("Person");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Entity<Employee>().HasMany(e => e.SomeNotes);
        modelBuilder.Entity<Employee>().HasMany(e => e.SomeOtherNotes);
        modelBuilder.Entity<Note>().ToTable("Note");
        modelBuilder.Entity<Note1>().ToTable("Note1");

        // If I put the .WithMany() here, EF complains about:
        // There is no property with name 'Employee_Id1' defined in type referred by Role 'Note'.
        // And if I remove SomeOtherNotes and leave SomeNotes the error will go away.

        modelBuilder.Entity<Note1>().HasRequired(x => x.Employee);
        modelBuilder.Entity<Note2>().ToTable("Note2");
        modelBuilder.Entity<Note2>().HasRequired(x => x.Employee);
    }
}

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, March 23, 2011 10:15 AM by David

Is there any way to add Include() clauses for TPT (or TPH) class hierarchies to a query?

I have 4 classes ServiceSpec_PhysicalAudio, ServiceSpec_PhysicalSDVideo, ServiceSpec_PhysicalHDVideo, ServiceSpec_Digital that inherit from a ServiceSpec base class.

I naively tried to do this with the following code, but it (obviously) didn't work.  (I could only imagine what the SQL statement would look like if it did!).

_dbContext.Destinations
.Include(destination => destination.ServiceSpecs)
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalAudio>().Select(serviceSpec => serviceSpec.Format))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalSDVideo>().Select(serviceSpec => serviceSpec.Format))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalSDVideo>().Select(serviceSpec => serviceSpec.Standard))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalHDVideo>().Select(serviceSpec => serviceSpec.Format))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalHDVideo>().Select(serviceSpec => serviceSpec.Standard))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_PhysicalHDVideo>().Select(serviceSpec => serviceSpec.FrameRate))
.Include(destination => destination.ServiceSpecs.OfType<ServiceSpec_Digital>().Select(serviceSpec => serviceSpec.Service));

Is there any way to force the loading of references and collections of subtypes of a polymophic type during query execution?  If not, what's the best workaround? Load them  in _ObjectContext_ObjectMaterialized()?

In general, in many/most cases I want to load my full object graph when I load an entity.  I wish EF had a context-level switch that forced full load, or an alternate opt-out - instead of opt-in (Include()) - model to control object graph loading.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Thursday, March 24, 2011 12:10 PM by mortezam
@David: You should be aware that the lambda expression in the new Include method overload is merely a property selector and you can't have any sort of filtering logic in it. In fact, you cannot have any filtering/ordering logic when eager loading navigation properties with Include method in general. There are 2 solutions that you can apply in this scenario; you can either use anonymous projections:

var query = (from d in context.Destinations where d.DestinationId == 1
             select new
             {
                 Destination = d,
                 ServiceSpec_PhysicalAudio = d.ServiceSpecs.OfType<ServiceSpec_PhysicalAudio>(),
                 ServiceSpec_PhysicalSDVideo = d.ServiceSpecs.OfType<ServiceSpec_PhysicalSDVideo>(),
                 ServiceSpec_PhysicalHDVideo = d.ServiceSpecs.OfType<ServiceSpec_PhysicalHDVideo>(),
             })
            .Select(d => d.Destination);

Or you can use the new Query method defined on DbCollectionEntry class which is not eager loading anymore which means you will have 2 round trips to the database:

Destination destination = context.Destinations.Single(d => d.DestinationId == 1);
context.Entry(destination)
            .Collection(d => d.ServiceSpecs)
            .Query()
            .Where(s => s is ServiceSpec_PhysicalAudio
                                 || s is ServiceSpec_PhysicalAudio
                                 || s is ServiceSpec_PhysicalSDVideo
                                 || s is ServiceSpec_PhysicalHDVideo)
        .Load();

Hope this helps.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Monday, March 28, 2011 1:57 PM by Vincent-Philippe Lauzon

Excellent blog post!

It's the only place I found information about table inheritance working with the RC API.

Thanks a lot for the content!

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Thursday, March 31, 2011 10:36 AM by Tony

Has the performance issue with creating the crazy number of joins and unions been solved yet?

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Thursday, April 7, 2011 10:26 AM by mortezam
@Tony: Like I described in the post, it’s not an issue, it’s just how TPT works by nature in essence that queries always require a join across many tables. Even other ORM frameworks generate pretty much the same SQL query when it comes to polymorphic associations. What’s new in EF 4.1 RC is that now you can mix inheritance mapping strategies in your hierarchy to come up with the best performance possible.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, April 13, 2011 12:05 AM by von

I just noticed a strange thing again with the RC version. The following issue was working with CTP5.

If I have the configurations below I would get an Order table with the following columns:

- Id
- DateOrdered
- OrderedById
- ApprovedById
- Person_Id (this one is an extra column created by EF)

If I remove the ApprovedBy fields in the Order class. The Order table will be like this:

- Id
- DateOrdered
- OrderedById

The extra Person_Id went away.

public class Order
{
    public Guid Id { get; set; }
    public DateTime DateOrdered { get; set; }
    public Person OrderedBy { get; set; }
    public Guid OrderedById { get; set; }
    public Person ApprovedBy { get; set; }
    public Guid? ApprovedById { get; set; }
}

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Person> People { get; set; }
       
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().ToTable("Orders");
        modelBuilder.Entity<Order>().HasRequired(o => o.OrderedBy);           
        modelBuilder.Entity<Order>().HasOptional(o => o.ApprovedBy);
        modelBuilder.Entity<Person>().ToTable("People");
        modelBuilder.Entity<Person>().HasMany(p => p.Orders);
    }
}  

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, April 13, 2011 11:50 AM by mortezam
@von: This one is a bit tricky. When you have both the ApprovedBy and OrderedBy properties as well as the Orders on the Person entity, you virtually defnining three associations between Order and Person entities:

1. (Person) 1 – * (Order) via Order.ApprovedBy and ApprovedById
2. (Person) 1 – * (Order) via Order.OrderedBy and OrderedById
3. (Person) 1 – * (Order) via Person.Orders (an Independent Association inferred by Code First, hence the Person_Id column on Orders table)

The reason for that is because you did not specify Person.Orders to be the InverseProperty for any of the other two associations so Code First creates a third one for you. However, when you delete the first association by removing the ApprovedBy property, you left with one association via OrderedBy. Now based on conventions, Code First will infer Person.Orders as the other end for this association instead of creating a new one since there is only one association exists and Code First can safely assume Person.Orders is the navigation property for this association on the Person end, something that was not possible in the previous scenario. Hope this helps.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Saturday, May 21, 2011 4:23 PM by mortezam
@von: Your code is perfectly fine and it always cause one and only one SQL query being submitted to the database to bring back all the related orders. I think you have something else going on in there, my wild guess is that you are iterating through the Orders collection, let’s say in a for each loop (which cause EF to lazy load the Orders) and then access an unrelated navigation property like ApprovedBy in the loop which of course will cause another SQL query to lazy load the Person entity if it’s not already in the cache. If that’s the case then you can be explicit and eager load the target navigation property to avoid this lazy loading behavior. Please let me know if you need help on that. Thanks :)

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Monday, May 23, 2011 1:16 AM by von

Thanks for responding mortezam. What I actually did is identify the collection (or property for that matter) that I want to eagerly load. And I used the "Include" method. Now that call to the Include method is inside a method (let's call it service method) that can be called by other codes. Other codes that may not want to eagerly load. So, I was thinking what could be an elegant way to tell my service method to eagerly load or not. Supplying a string to tell the method which path to include is not elegant, neither is having a bool parameter telling it to eagerly load a collection. Can you share with me the elegant code I am looking for? Thanks.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Monday, May 23, 2011 2:54 PM by mortezam
@von: I think the most elegant way is to have 2 different methods for doing so, or at least that’s how I implement it. For example you might have a method like GetOrders that basically just loads some orders and another one such as GetOrdersWithDetails that retrieves not only the orders but also eager loads all their relationships.

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, May 25, 2011 12:28 AM by von

@mortezam, that make sense. Thanks again for your help!

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Friday, August 19, 2011 3:46 PM by WarrenLaFrance

I took the Contoso University Demo and applied your changes here.. Could not have been easier... Very nice!!!

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, September 21, 2011 12:49 PM by Mark Phillips

Thanks for the article.  It was very helpful.

I noticed that the BillingDetail ForeignKey column in the User table is called "BillingDetail_BillingDetailID".  How can it be changed to just "BillingDetailID"

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Friday, September 23, 2011 9:39 PM by mortezam
@Mark Phillips: One way to change the FK column name is to define a new foreign key property on the User entity, which essentially change the association to be a foreign key association, like the following code:

public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int BillingDetailId { get; set; }

    public virtual BillingDetail BillingInfo { get; set; }
}

# re: Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)

Wednesday, November 16, 2011 10:33 AM by maximusmd

Shouldn't you have a BillingDetailID declared in the User class? How are you obtaining the BillingInfo ?

I thought you had to have a foreign key in the User entity. I am confused.... thanks

# re: Inheritance with EF Code First: Part 2 – Table per Type (TPT)

Wednesday, November 16, 2011 11:51 AM by mortezam
@maximusmd: Your thought is correct; the User entity does have a foreign key to the BillingDetail class, however, explicitly defining that foreign key as a property on the User is just a matter of having a foreign key association instead of an independent association. Having said that, I added the foreign key property to the User class to avoid any confusion. Hope it helps :)

# re: Inheritance with EF Code First: Part 2 – Table per Type (TPT)

Friday, December 23, 2011 8:54 AM by edmondthieffry

Thank you for the clear article.

But I still have a question. I use the technique above to implement subtables/classes that inherit from an abstract(!) base table/class. When I try now to build an MVC view I can't figure out how to design the view model that serves as the base for my view. This view needs to be able to display data from the base class (billingdetail) as well from the derived classes depending on the type of base class chosen in the view (e.g. radiobuttons to choose between bankaccount and creditcard).

I suppose the viewpage would inherit of model X with X defining the TPT designed derived classes. But if I want to display common attributes that are defined in the abstract class, I'm unable to refer directly to the base class properties; I have to duplicate my markup depending on the derived class e.g. bankaccount.owner and creditcard.owner.

If I have a lot of common properties: is there a way not to duplicate and have the view markup refer to the abstract base class properties directly? Imagine having 5 different subclasses: the common data is displayed on top of the view (screen), the specific data follows below that:  you really do not want to have to write 5 times the same but slightly different markup for the common data...

Thx for any help.

# re: Inheritance with EF Code First: Part 2 – Table per Type (TPT)

Wednesday, January 18, 2012 4:09 PM by mortezam
@edmondthieffry: You don’t really need to duplicate your common markup related to the base class view in each and every sub class view. You can simply create a Partial View for your abstract base class (e.g. _BillingDetailPartial.cshtml) and then ask the HtmlHelper object to render it in each sub class view like the following code:

@model Models.CreditCard
@Html.Partial("_BillingDetailPartial", Model)
@*Your markup for Credit Card specific properties...*@