Table-per-hierarchy and composite primary key

Question

I have two tables in a legacy database (which I cannot modify) with data as follows:

Table1 has a composite primary key (Code, Abbrev), but Abbrev is also used as a discriminator (see below). Table2 has two foreign key columns (CodeA, CodeB), both referencing the same field Code in Table1. There are duplicates in the Table1.Code field.

I would like to use table-per-hierarchy approach with Entity framework 6. So, I created the following model classes:

[Table("Table1")]
public class MyBaseClass
{
    [Key]
    public string Code { get; set; }
}

public class MyBaseClassA : MyBaseClass
{
}

public class MyBaseClassB: MyBaseClass
{
}

[Table("Table2")]
public class SubClass
{
    [Key]
    public int Id { get; set; }

    [Required]
    [ForeignKey("MyBaseClassA")]
    public string CodeA { get; set; }

    public virtual MyBaseClassA ClassA { get; set; }

    [Required]
    [ForeignKey("MyBaseClassB")]
    public string CodeA { get; set; }

    public virtual MyBaseClassB ClassB { get; set; }

}

I defined table-per-hierarchy in my DataContext : DbContext class as follows:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyBaseClass>().Map<MyBaseClassA>(m => m.Requires("Abbrev").HasValue("A"))
            .Map<MyBaseClassB>(m => m.Requires("Abbrev").HasValue("B"));
    }

The problem is when I want to use such mapping - I can't use the discriminator field (Table1.Abbrev) as a part of a composite key in the MyBaseClass - I get the following error:

All objects in the EntitySet 'DataContext.MyBaseClass' must have unique primary keys. However, an instance of type 'MyBaseClassA' and an instance of type 'MyBaseClassB' both have the same primary key value, 'EntitySet=MyBaseClass;Code=1'.

Is it possible to map the model above with Entity framework 6 (or newer)?

Answer 1

I'm afraid this isn't possible with Entity Framework.

To begin with, you have to map the full key of Table1, because EF can't possibly identify Table1 objects by Code only. And a discriminator that's part of a compound primary key is just not supported.

So you can't subtype Table1. Now if that was all, you could choose not to use inheritance. But Table2 is the real damper. EF requires foreign keys to reference a full primary key. So, since Table1 should have a compound key, Table2's two foreigns key should also look like { Code, Abbrev }. Well, there isn't even one Abbrev field in Table2.

The only thing you can do is map Table1 as it is (without inheritance) and also Table2 without any association between them. You'll have to manually write joins (of sorts) to get related records from the database in one query.

For instance, to get a Table2 with a Table1 as A:

from t1 in context.Table1s
join t2 in context.Table2s on t1.Code equals t2.CodeA
where t1.Abbrev == "A"
select new { A = t1, t2 }

Or a Table2 with both a Table1 as A and a Table1 as B:

from t2 in context.Table2s
select new 
{
    t2,
    A = (from t1 in context.Table1s 
         where t1.Code == t2.CodeA && t1.Abbrev == "A")
        .FirstOrDefault(),
    B = (from t1 in context.Table1s 
         where t1.Code == t2.CodeB && t1.Abbrev == "B")
        .FirstOrDefault(),
}