Inheritance with EF Code First: Part 3 – Table per Concrete Type (TPC)

This is the third (and last) post in a series that explains different approaches to map an inheritance hierarchy with EF Code First. I've described these strategies in previous posts: In today’s blog post I am going to discuss Table per Concrete Type (TPC) which completes the inheritance mapping strategies supported by EF Code First. At the end of this post I will provide some guidelines to choose an inheritance strategy mainly based on what we've learned in this series.

TPC and Entity Framework in the Past

Table per Concrete type is somehow the simplest approach suggested, yet using TPC with EF is one of those concepts that has not been covered very well so far and I've seen in some resources that it was even discouraged. The reason for that is just because Entity Data Model Designer in VS2010 doesn't support TPC (even though the EF runtime does). That basically means if you are following EF's Database-First or Model-First approaches then configuring TPC requires manually writing XML in the EDMX file which is not considered to be a fun practice. Well, no more. You'll see that with Code First, creating TPC is perfectly possible with fluent API just like other strategies and you don't need to avoid TPC due to the lack of designer support as you would probably do in other EF approaches.

Table per Concrete Type (TPC)

In Table per Concrete type (aka Table per Concrete class) we use exactly one table for each (nonabstract) class. All properties of a class, including inherited properties, can be mapped to columns of this table, as shown in the following figure:
As you can see, the SQL schema is not aware of the inheritance; effectively, we’ve mapped two unrelated tables to a more expressive class structure. If the base class was concrete, then an additional table would be needed to hold instances of that class. I have to emphasize that there is no relationship between the database tables, except for the fact that they share some similar columns.

TPC Implementation in Code First

Just like the TPT implementation, we need to specify a separate table for each of the subclasses. We also need to tell Code First that we want all of the inherited properties to be mapped as part of this table. In CTP5, there is a new helper method on EntityMappingConfiguration class called MapInheritedProperties that exactly does this for us. Here is the complete object model as well as the fluent API to create a TPC mapping:
public abstract class BillingDetail
{
    public int BillingDetailId { getset; }
    public string Owner { getset; }
    public string Number { getset; }
}
        
public class BankAccount : BillingDetail
{
    public string BankName { getset; }
    public string Swift { getset; }
}
        
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; }
        
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BankAccount>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("BankAccounts");
        });
 
        modelBuilder.Entity<CreditCard>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("CreditCards");
        });            
    }
}
The Importance of EntityMappingConfiguration Class
As a side note, it worth mentioning that EntityMappingConfiguration class turns out to be a key type for inheritance mapping in Code First. Here is an snapshot of this class:
namespace System.Data.Entity.ModelConfiguration.Configuration.Mapping
{
    public class EntityMappingConfiguration<TEntityType> where TEntityType : class
    {
        public ValueConditionConfiguration Requires(string discriminator);
        public void ToTable(string tableName);
        public void MapInheritedProperties();
    }
}
As you have seen so far, we used its Requires method to customize TPH. We also used its ToTable method to create a TPT and now we are using its MapInheritedProperties along with ToTable method to create our TPC mapping.

TPC Configuration is Not Done Yet!

We are not quite done with our TPC configuration and there is more into this story even though the fluent API we saw perfectly created a TPC mapping for us in the database. To see why, let's start working with our object model. For example, the following code creates two new objects of BankAccount and CreditCard types and tries to add them to the database:
using (var context = new InheritanceMappingContext())
{
    BankAccount bankAccount = new BankAccount();
    CreditCard creditCard = new CreditCard() { CardType = 1 };
                
    context.BillingDetails.Add(bankAccount);
    context.BillingDetails.Add(creditCard);
 
    context.SaveChanges();
}
Running this code throws an InvalidOperationException with this message:
The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.
The reason we got this exception is because DbContext.SaveChanges() internally invokes SaveChanges method of its internal ObjectContext. ObjectContext's SaveChanges method on its turn by default calls AcceptAllChanges after it has performed the database modifications. AcceptAllChanges method merely iterates over all entries in ObjectStateManager and invokes AcceptChanges on each of them. Since the entities are in Added state, AcceptChanges method replaces their temporary EntityKey with a regular EntityKey based on the primary key values (i.e. BillingDetailId) that come back from the database and that's where the problem occurs since both the entities have been assigned the same value for their primary key by the database (i.e. on both BillingDetailId = 1) and the problem is that ObjectStateManager cannot track objects of the same type (i.e. BillingDetail) with the same EntityKey value hence it throws. If you take a closer look at the TPC's SQL schema above, you'll see why the database generated the same values for the primary keys: the BillingDetailId column in both BankAccounts and CreditCards table has been marked as identity.

How to Solve The Identity Problem in TPC

As you saw, using SQL Server’s int identity columns doesn't work very well together with TPC since there will be duplicate entity keys when inserting in subclasses tables with all having the same identity seed. Therefore, to solve this, either a spread seed (where each table has its own initial seed value) will be needed, or a mechanism other than SQL Server’s int identity should be used. Some other RDBMSes have other mechanisms allowing a sequence (identity) to be shared by multiple tables, and something similar can be achieved with GUID keys in SQL Server. While using GUID keys, or int identity keys with different starting seeds will solve the problem but yet another solution would be to completely switch off identity on the primary key property. As a result, we need to take the responsibility of providing unique keys when inserting records to the database. We will go with this solution since it works regardless of which database engine is used.

Switching Off Identity in Code First

We can switch off identity simply by placing DatabaseGenerated attribute on the primary key property and pass DatabaseGenerationOption.None to its constructor. DatabaseGenerated attribute is a new data annotation which has been added to System.ComponentModel.DataAnnotations namespace in CTP5:
public abstract class BillingDetail
{
    [DatabaseGenerated(DatabaseGenerationOption.None)]
    public int BillingDetailId { getset; }
    public string Owner { getset; }
    public string Number { getset; }
}
As always, we can achieve the same result by using fluent API, if you prefer that:
modelBuilder.Entity<BillingDetail>()
            .Property(p => p.BillingDetailId)
            .HasDatabaseGenerationOption(DatabaseGenerationOption.None);

Working With The Object Model

Our TPC mapping is ready and we can try adding new records to the database. But, like I said, now we need to take care of providing unique keys when creating new objects:
using (var context = new InheritanceMappingContext())
{
    BankAccount bankAccount = new BankAccount() 
    { 
        BillingDetailId = 1                     
    };
    CreditCard creditCard = new CreditCard() 
    { 
        BillingDetailId = 2,
        CardType = 1
    };
                
    context.BillingDetails.Add(bankAccount);
    context.BillingDetails.Add(creditCard);
 
    context.SaveChanges();
}

Polymorphic Associations with TPC is Problematic

The main problem with this approach is that it doesn’t support Polymorphic Associations very well. After all, in the database, associations are represented as foreign key relationships and in TPC, the subclasses are all mapped to different tables so a polymorphic association to their base class (abstract BillingDetail in our example) cannot be represented as a simple foreign key relationship. For example, consider the domain model we introduced here where User has a polymorphic association with BillingDetail. This would be problematic in our TPC Schema, because if User has a many-to-one relationship with BillingDetail, the Users table would need a single foreign key column, which would have to refer both concrete subclass tables. This isn’t possible with regular foreign key constraints.

Schema Evolution with TPC is Complex

A further conceptual problem with this mapping strategy is that several different columns, of different tables, share exactly the same semantics. This makes schema evolution more complex. For example, a change to a base class property results in changes to multiple columns. It also makes it much more difficult to implement database integrity constraints that apply to all subclasses.

Generated SQL

Let's examine SQL output for polymorphic queries in TPC mapping. For example, consider this polymorphic query for all BillingDetails and the resulting SQL statements that being executed in the database:
var query = from b in context.BillingDetails select b;
Just like the SQL query generated by TPT mapping, the CASE statements that you see in the beginning of the query is merely 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).

TPC's SQL Queries are Union Based

As you can see in the above screenshot, the first SELECT uses a FROM-clause subquery (which is selected with a red rectangle) to retrieve all instances of BillingDetails from all concrete class tables. The tables are combined with a UNION operator, and a literal (in this case, 0 and 1) is inserted into the intermediate result; (look at the lines highlighted in yellow.) EF reads this to instantiate the correct class given the data from a particular row. A union requires that the queries that are combined, project over the same columns; hence, EF has to pad and fill up nonexistent columns with NULL. This query will really perform well since here we can let the database optimizer find the best execution plan to combine rows from several tables. There is also no Joins involved so it has a better performance than the SQL queries generated by TPT where a Join is required between the base and subclasses tables.

Choosing Strategy Guidelines

Before we get into this discussion, I want to emphasize that there is no one single "best strategy fits all scenarios" exists. As you saw, each of the approaches have their own advantages and drawbacks. Here are some rules of thumb to identify the best strategy in a particular scenario:
  • If you don’t require polymorphic associations or queries, lean toward TPC—in other words, if you never or rarely query for BillingDetails and you have no class that has an association to BillingDetail base class. I recommend TPC (only) for the top level of your class hierarchy, where polymorphism isn’t usually required, and when modification of the base class in the future is unlikely.
  • If you do require polymorphic associations or queries, and subclasses declare relatively few properties (particularly if the main difference between subclasses is in their behavior), lean toward TPH. Your goal is to minimize the number of nullable columns and to convince yourself (and your DBA) that a denormalized schema won’t create problems in the long run.
  • If you do require polymorphic associations or queries, and subclasses declare many properties (subclasses differ mainly by the data they hold), lean toward TPT. Or, depending on the width and depth of your inheritance hierarchy and the possible cost of joins versus unions, use TPC.
By default, choose TPH only for simple problems. For more complex cases (or when you’re overruled by a data modeler insisting on the importance of nullability constraints and normalization), you should consider the TPT strategy. But at that point, ask yourself whether it may not be better to remodel inheritance as delegation in the object model (delegation is a way of making composition as powerful for reuse as inheritance). Complex inheritance is often best avoided for all sorts of reasons unrelated to persistence or ORM. EF acts as a buffer between the domain and relational models, but that doesn’t mean you can ignore persistence concerns when designing your classes.

Summary

In this series, we focused on one of the main structural aspect of the object/relational paradigm mismatch which is inheritance and discussed how EF solve this problem as an ORM solution. We learned about the three well-known inheritance mapping strategies and their implementations in EF Code First. Hopefully it gives you a better insight about the mapping of inheritance hierarchies as well as choosing the best strategy for your particular scenario.

Happy New Year and Happy Code-Firsting!

References

31 Comments

  • I really enjoyed this series, thanks.

  • @Dimitris Foukas: Great question! Let me clarify it. When talking about polymorphic associations in TPC, we need to consider 2 different scenarios in terms of multiplicities. For example in our example, we are not able to create a one-to-many association from User to BillingDetail because then both subclass tables would need a foreign key reference to the User table and&nbsp;EF does not natively support this scenario. So this is not possible:
    public class User{&nbsp;&nbsp;&nbsp; public int UserId { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;public virtual ICollection&lt;BillingDetail&gt; BillingDetails { get; set; }}&nbsp;public abstract class BillingDetail{&nbsp;&nbsp;&nbsp; public int BillingDetailId { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;public virtual User User { get; set; } }&nbsp; &nbsp;
    That said, the only way to create a polymorphic association in a TPC mapping like this is to create a many-to-one association from User to BillingDetail which means the Users table would need a single foreign key column which would have to refer both concrete subclass tables:
    public class User{&nbsp;&nbsp;&nbsp; public int UserId { get; set; }&nbsp;&nbsp;&nbsp; public virtual BillingDetail BillingInfo { get; set; }}
    public abstract class BillingDetail{&nbsp;&nbsp;&nbsp; public int BillingDetailId { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;public virtual ICollection&lt;User&gt; Users { get; set; }}&nbsp;
    However, this mapping throws an exception due to a bug in CTP5. In fact, I wanted to illustrate this many-to-one association as the way we can implement polymorphic associations in TPC but then I noticed this bug and decided not to do it until it goes away in the RTM. That’s why the title reads as “Polymorphic Associations with TPC are Problematic” and not impossible.&nbsp;&nbsp;
    In a nutshell, a one-to-many association&nbsp;is not possible but many-to-one associations will work. In other words, in TPC you always need to keep the foreign key outside of the inheritance hierarchy. Hope this helps, thanks :)

  • Crystal clear!
    You should really evolve this blog series into a Code first cookbook - there is a window of opportunity there IMO...

  • All your posts in this blog so far are simply great, thanks...

  • @Dimitris: That is actually a very good idea especially because none of the current EF books cover Code First development. I will think about it, thanks!

  • Hi,
    I don't get it to work with a second level of inheritance
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/ \
    &nbsp; &nbsp; &nbsp; &nbsp;AA &nbsp; AB
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/ &nbsp;\
    &nbsp; &nbsp; &nbsp; &nbsp; ABA &nbsp; ABB
    A and AB are abstract, the rest concrete.
    public abstract class A{&nbsp;&nbsp;&nbsp; public A() &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Id = Guid.NewGuid(); &nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp; [DatabaseGenerated(DatabaseGenerationOption.None)]&nbsp;&nbsp;&nbsp; public Guid Id { get; set; }}
    public class AA : A { &nbsp;&nbsp;&nbsp; public string aa { get; set; } }
    public abstract class AB : A { &nbsp;&nbsp;&nbsp; public string ab { get; set; } }
    public class ABA : AB { &nbsp;&nbsp;&nbsp; public string aba { get; set; } }
    public class ABB : AB { &nbsp;&nbsp;&nbsp; public string abb { get; set; } }
    I get an NullReferenceException the moment I add something to the DbSet&lt;A&gt; collection in the context
    Christof

  • @Christof: This is a bug in CTP5, your multi-level hierarchy is designed to work in Code First and hopefully EF team will make sure it is fixed for the RTM. Until then, one possible workaround would be to use TPT mapping instead of TPC.

  • @Guest: This is a bug in CTP5. Basically polymorphic associations like the one you are trying to setup between Publication and CoAuthor throws an exception when using TPC strategy. Please read my answer to Dimitris Foukas above where I discussed this issue in detail. The workaround for now is to avoid polymorphic associations when using TPC or choosing another strategy like Table Per Type (TPT) for this scenario. Hope this helps.

  • I wonder if EF could support a scenario where additionals properties are saved serialized in a xml column. This is usefull when additional properties are not directly queried.
    If no directly, would it be possible with a type that has a complex type property
    class A {&nbsp;&nbsp;&nbsp; public int Id {get;set;}&nbsp;&nbsp;&nbsp; public string Name {get;set;}&nbsp;&nbsp;&nbsp; public Bag CustomProperties {get;set;} // option where A is not an abstract class}
    [ComplexType]public abstract class Bag { }
    public class B1 : Bag { // could be : A
    &nbsp;&nbsp;&nbsp; public string Prop1 {get;set}}
    public class B2 : Bag { // could be : A
    &nbsp;&nbsp;&nbsp; public string Prop2 {get;set;}}
    new A { Name = "foo", CustomProperties &nbsp;= new B2 { Prop2 = "bar" } }
    or
    new B2 { Name = "foo", Prop2 = "bar" }
    could be persisted as
    ('foo', 'B2', '&lt;root&gt;&lt;Prop2&gt;bar&lt;/Prop2&gt;&lt;/root&gt;')
    Any clue ? I know it's a scenario not very often supported but it has some advantage.

  • @ali akbar: It seems that you are still using CTP5 assembly, if so, then please do upgrade it to EF 4.1 RC which is the latest EF release. After that have a look at my discussion with Dimitris Foukas above which is around the same question. Basically you cannot create a polymorphic many-to-one association from Product to Category when using TPC mapping. There are a couple of ways that you can work out this limitation though:



    1. You can make Product class abstract and keep the association the same way it is.


    2. You can change the association to be a one-to-many from Product to Category by defining a Product navigation property in Category class which essentially means keeping the FK column out of the inheritance hierarchy.


    3. And Finally you can use TPT strategy instead which is the recommended strategy when polymorphic associations are involved.



    Having&nbsp;said all that, Your Product Category object model issue would be best resolved by the third solution but you may want to consider other ways for your different future scenarios. Hope this helps :)

  • Hi,
    I am trying to do TPC (Table Per Concrete Type) mapping in EF4 - CTP 5. I have three level of hierarchy : type C inherits from type B, which in turn inherits from type A. All classes are concrete and there is one table per type in database. When I try to map it, I am getting error 'Invalid column name 'Discriminator''. Any ideas, what I might be missing? (I have two level inheritance TPC working, I think 3 level inheritance is causing this problem

  • @Sanjay: This is a bug in CTP5 and the best workaround for this would be upgrading to EF 4.1 RC :)

    Please let me know if you have any difficulty in mapping your inheritance hierarchy with EF 4.1. Thanks.

  • Nice tutotial, very clear and easy to follow. However, when I attempt to recreate your sample, I get the following error:

    Invalid object name 'dbo.BillingDetails'.

    Is seems to be wanting a BillingDetails table in the database, but of course there isn't supposed to be one.

    Did something change since the RTW release? I'm using EF 4.1.10331.0 here.

  • @Lee Dumond: Thanks for the update; however, I couldn’t reproduce the exception that you are getting, any chance that you could send your sample that throws when the table is not there to my email bmanavi@gmail.com?

  • I may have deleted it. Let me see if I can recreate it again...

  • I see you answered my inquiry over at MSDN. Turns out you and Diego were correct after all. Thanks for all your help.

  • Hi my friend!
    First of all I would like to congratulate you for your great post series abou EF!
    I read your post about TPC and was wondering why you not implement a dbset for concrete classes and not for the abstract class?!
    I did a test and seems to work good to me.
    namespace TablePerConcreteClass{&nbsp;&nbsp;&nbsp; class Program&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; static void Main(string[] args)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; using (var context = new InheritanceMappingContext())&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var bankAccount = new BankAccount { BankName = "Banco do Brasil" };&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var creditCard = new CreditCard { CardType = 1 };&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; context.BankAccounts.Add(bankAccount);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; context.CreditCards.Add(creditCard);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; context.SaveChanges();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public abstract class BillingDetail&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int Id { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Owner { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Number { get; set; }&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class BankAccount : BillingDetail&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string BankName { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Swift { get; set; }&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class CreditCard : BillingDetail&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int CardType { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string ExpiryMonth { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string ExpiryYear { get; set; }&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class InheritanceMappingContext : DbContext&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public DbSet&lt;BankAccount&gt; BankAccounts { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public DbSet&lt;CreditCard&gt; CreditCards { get; set; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; protected override void OnModelCreating(DbModelBuilder modelBuilder)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;BankAccount&gt;().Map(m =&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.MapInheritedProperties();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.ToTable("BankAccounts");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; });
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;CreditCard&gt;().Map(m =&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.MapInheritedProperties();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.ToTable("CreditCards");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; });&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp; }}

  • @Rafael: The only downside of defining DbSets for the subclasses instead of the base class is that you won't be able to run polymorphic queries against your object model anymore, something that would have achieved by a query like context.BillingDetails.ToList(). That being said, if you are using TPC for the top level of your class hierarchy, where polymorphism isn’t usually required then defining DbSets with subclasses is the way to go. In fact, this way you don't even need to drop down to fluent API to specify a TPC mapping, Code First will use TPC to map your classes by default. So in a nutshell, it’ll work both ways and which one to pick depends on your use case. Again if the subclasses are unrelated and merely share a set of common properties (e.g. CreatedBy, UpdatedDate, etc.) then the code you posted is absolutely fine and recommended. Thanks and hope this helps.

  • Excellent blog post series. Thanks

  • Morteza,

    First of all thanks for taking the time to create this series on Code First Inheritance.

    I was reviewing the db schema from the TPC tutorial. It appears that EF generates a table for the BillingDetails even though no data is ever inserted into it when a CreditCard or BankAccount object is saved. This seems really strange. Is there some way to avoid this?

    Thanks again,
    Mark

  • @Mark Phillips: That is a bug in EF 4.1 RTW that unfortunately hasn’t been fixed yet (EF 4.1 Update 1 still creates this extra table). Please read my answer to “Lee Dumond” above where I explained this bug in detail. Hope this helps.

  • It was indeed useful.

  • I would like to warn anybody who wants to use code first (specifically TPC) to *only* use it when you are going to stick with the simple scenarios that you've already seen proven out through examples on the web. If you begin to stray from the examples, you'll find yourself with code that doesn't work with no way to fix it (since, per the Microsoft way, the important classes are all "private internal"). Just to give one example, if you want an entity base class that is not abstract (to, say, work around a bug in RIA services), forget about it... there is absolutely no way to do it. As another example, generic types are not supported as entities or as entity base types. As another example, if you want to define the properties on your entity at runtime, using ICustomTypeProvider or IDynamicMetaObjectProvider... again, forget about it. I could go on and on, but I've run into more "not supported" scenarios with this library than almost any other I've ever used. That said, I love the concept... and once EF code first becomes at all extensible, I'll fully support it.

  • i tried following code but i got below error,ur have any idea?

    error 3013: Problem in mapping fragments starting at line 17:Missing table mapping: Foreign key constraint 'CountryBase_Cities1' from table Cities (CountryId) to table CountryBase (Id): no mapping specified for the table CountryBase.\r\n"}

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity.Migrations;
    namespace ConsoleApplication1
    {
    &nbsp;&nbsp;&nbsp; class Program
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; static void Main(string[] args)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var db = new Context();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db.Database.Delete();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db.Database.Create();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db.SaveChanges();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db.Cities.ToList();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class Context : DbContext
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public DbSet&lt;CityBase&gt; Cities { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public DbSet&lt;CountryBase&gt; Countries { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public DbSet&lt;Area&gt; Areas { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; protected override void OnModelCreating(DbModelBuilder modelBuilder)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;CountryBase&gt;()
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Property(p =&gt; p.Id)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;CityBase&gt;()
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Property(p =&gt; p.Id)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;City&gt;().Map(m =&gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.MapInheritedProperties();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.ToTable("Cities");
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; });
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; modelBuilder.Entity&lt;Country&gt;().Map(m =&gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.MapInheritedProperties();
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m.ToTable("Countries");
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; });
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; base.OnModelCreating(modelBuilder);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public abstract class Entity : IUniqueable
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Key]
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public virtual int Id { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public abstract class CityBase : Entity
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int CountryId { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Code { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public bool Active { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public CountryBase Country { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public abstract class CountryBase : Entity
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Code { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public bool Active { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public virtual ICollection&lt;CityBase&gt; Cities { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class City : CityBase
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int PointX { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int PointY { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public virtual ICollection&lt;Area&gt; Areas { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class Country : CountryBase
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int PointX { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int PointY { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public class Area : Entity
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public string Name { get; set; }
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public City City { get; set; }
    &nbsp;&nbsp;&nbsp; }
    &nbsp;&nbsp;&nbsp; public interface IUniqueable
    &nbsp;&nbsp;&nbsp; {
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int Id { get; set; }
    &nbsp;&nbsp;&nbsp; }
    }

  • Hello,

    I have the following architecture :
    - In Database : Tables B and C have a foreign key on table A
    - In C# : Classes A, B, C represents tables A, B, C. Classes B and C inherit from class A.

    At execution, i have the error 'Invalid column name 'Discriminator''. I'm using EF 4.3.

    Please help me !!! Or i'll kill myself after the murder of all of you and your families !!!

    No, I'm joking of course, I just need some help, it would be very nice :-)

    Thanks,

    Joseph

  • I have a big problem here. EF 5.0 Code First keeps complaining when using TPC inheritance mapping saying:

    All objects in the EntitySet 'EntitySet' must have unique primary keys

    Even if I make separate sets for the derived types. e.g.:

    I do this:

    public DbSet ProviderCompanies { get; set; }

    public DbSet Banks { get; set; }

    Both, Bank and ProviderCompany derive from Company base class.

    Then I do this:

    var c1 = context.ProviderCompanies.ToList();
    var c2 = context.Banks.ToList();

    I have provider companies and banks with same Ids, and it throws the exception:

    All objects in the EntitySet 'TestContext.Companies' must have unique primary keys
    though I don't have a set named Companies.

    I cannot find the solution, please help.

    Thanks,

    Giorgi

  • With TPC referring to your example can I rename the CreditCard and BankAccount identity columns. At the moment the id column is BillingDetailId on both tables. I want CreditCardId on the CreditCard table and BankAccountID on the BankAccount table or can I only use TPT? I tried experimenting by adding the id fields in the model and then tried to ignore the id on the base (BillingDetail) but to no avail. I even tried using the Column, Key, NotMapped attributes whereever appropriate however, whatever I try I get an exception or the OnModelCreating method has no effect. Also using the Display attribute only changes the UI and not the Database schema. Also the issue with the TPC format not being able to save like minded objects because of the identity seed crisis then I cannot see any point in using TPC inheirtence concept.

    My scenario is I am using SQL Server and I would like to add a UserId (int) and Revision (TimeStamp) column to every single table in my existing database which has been designed with no 1:1 (or 1:0 for that matter) (so 3rd normal form I guess) relationships so I know what user and what time that user changed a record, for tracking purposes. So this would be my base class however, TPC insists I have the ID on the base which means I will no longer beable to save anything because of the identity problem. Even if I named the base class key field as ID so all tables use ID (instead of ID) I still run into the saving issue and with TPT I would still have to add a baseID, and base object to every inherited model class which is not impossible but annoying. Will this identiy issue be resolved in the near future? Not that I have the luxury of seeing under the bonnent but why is this not a problem when using the traditional way of using System.Data.SQLClient objects. Sure it does not set up your database for you as in code first implementation but it does allow you to save to any table without a seed identity issue, so surely behind the scenes is it not using this namespace so then why is it so complicated?

  • Thats great artical serise. Now i am crystal clear with EF mapping startegy. Great work keep it up.

  • very nice series of articles

  • I enjoyed reading the series. I was skeptical on using Code First because of the inheritance in my design. Thanks a lot

  • I want have such class structure with Table per Concrete Type
    public abstract class BillingDetailBase
    {
    public Int32 Id { get; set; }




    }

    public abstract class BillingDetail : BillingDetailBase
    {
    public string Owner { get; set; }
    }

    public class User
    {
    public int UserId { get; set; }
    public virtual BillingDetailBase BillingDetail { get; set; }


    }

    public class BankAccount : BillingDetail
    {



    public string BankName { get; set; }
    public string Swift { get; set; }
    }

    public class CreditCard : BillingDetail
    {



    public int CardType { get; set; }
    public string ExpiryMonth { get; set; }
    public string ExpiryYear { get; set; }
    }


    I get error
    (23,6) : error 0040: Type BillingDetailBase is not defined in namespace CodeFirstDatabaseSchema (Alias=Self).

    (41,8) : error 0100: The referenced EntitySet BillingDetailBase for End BillingDetailBase could not be found in the containing EntityContainer.


    How I could solve problem?

Comments have been disabled for this content.