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?

Sternson

Turns out that this is a bug in Code First code (still present in EF6), which is producing two columns with the same name when it generates the database schema. The EF runtime and the Ef designer actually support this scenario. Feel free to vote for this issue in CodePlex: entityframework.codeplex.com/workitem/583 – Claxton

The bug I mentioned above is fixed in recent builds of EF6. – Claxton

Solution in EF 6: #19577953 – Samuele

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");          
    }
}

Char

What you're suggesting would probably work but it pollutes the class hierarchy and defeats the purpose of OO. – Metaphosphate

Agree. And that's why I recommend you to go with TPT as shown in my updated answer. – Char

Thanks, I see that you have added the TPT suggestion. It's a good suggestion although I'd still like to have a single table for performance reasons. – Metaphosphate

No problem and I don't think that would be possible: 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. – Char

I just wish Microsoft had used class name prefixes rather than ordinal suffixes for TPH. It would make the schema so much readable! – Wanda

@Daniel: Exactly, even better would be a way to override the convention that applies the suffixes to determine the names yourself, allowing for overlapping names as well. – Metaphosphate

Answer 2

For anyone who was having trouble with this issue, it has now been fixed in EF6: Entity framework - Codeplex

Supersede

The link is outdated. – Erme