c# - How to share common column names in a Table per Hierarchy (TPH) mapping

Question

I'm using Entity Framework 4 CTP5 code first approach and I have a Table per Hierarchy (TPH) mapping. Some of my classes in the hierarchy have properties in common.

public class BaseType
{
    public int Id { get; set; }
}

public class A : BaseType
{
    public string Customer { get; set; }
    public string Order { get; set; }
}

public class B : BaseType
{
    public string Customer { get; set; }
    public string Article { get; set; }
}

public class C : BaseType
{
    public string Article { get; set; }
    public string Manufacturer { get; set; }
}

The default convention maps this to the following columns:

• Id
• Article1
• Article2
• Customer1
• Customer2
• Manufacturer
• Order
• Type

I want to have EF4 share the common properties to end up with the following:

• Id
• Article
• Customer
• Manufacturer
• Order
• Type

Apart from the reduced number of columns, this has the advantage of being able to search for records based on Article for example, without having to know which types exactly have an Article property.

I tried mapping each common property to the same column:

modelBuilder.Entity<B>().Property(n => n.Article).HasColumnName("Article");
modelBuilder.Entity<C>().Property(n => n.Article).HasColumnName("Article");

but this threw the following exception:

Schema specified is not valid. Errors: (36,6) : error 0019: Each property name in a type must be unique. Property name 'Article' was already defined.

Does anyone know how to get around this validation rule?

Answer 1

There is no workaround to bypass this validation. In TPH a column is either belongs to the base class which is inherited by all childs or is specialized to the child class. You cannot instruct EF to map it to two of your childs but not for the other. Attempting to do so (for example by putting [Column(Name = "Customer")] on both A.Customer and B.Customer) will be causing a MetadataException with this message:

Schema specified is not valid. Errors: (10,6) : error 0019: Each property name in a type must be unique. Property name 'Customer' was already defined.

TPH Solution:

One solution to this would be to promote Customer and Article properties to the base class:

public class BaseType {
    public int Id { get; set; }
    public string Customer { get; set; }
    public string Article { get; set; }
}

public class A : BaseType {
    public string Order { get; set; }
}

public class B : BaseType { }

public class C : BaseType {
    public string Manufacturer { get; set; }
}

Which results to the desired schema:

TPT Solution (Recommended):

That said, I recommend to consider using Table per Type (TPT) since it's a better fit for your scenario:

public class BaseType
{
    public int Id { get; set; }
}

public class A : BaseType
{
    [Column(Name = "Customer")]
    public string Customer { get; set; }
    public string Order { get; set; }
}

public class B : BaseType
{
    [Column(Name = "Customer")]
    public string Customer { get; set; }

    [Column(Name = "Article")]
    public string Article { get; set; }
}

public class C : BaseType
{
    [Column(Name="Article")]
    public string Article { get; set; }
    public string Manufacturer { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<BaseType> BaseTypes { get; set; }        

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BaseType>().ToTable("BaseType");
        modelBuilder.Entity<A>().ToTable("A");
        modelBuilder.Entity<C>().ToTable("C");
        modelBuilder.Entity<B>().ToTable("B");          
    }
}