Table-per-hierarchy and composite primary key

Question

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

two legacy tables

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

i think your model of table 1 is wrong, you said you have a composite key of code and abbrev but in your entity you have just one key part (code). — loiti, Feb 03 '15 at 22:42
In a way, that's right but I can't have both discriminator mapping in the DataContext class and the same property in the model (as a part of a composite key), EF refuses it. — Tomaz Tekavec, Feb 03 '15 at 22:55
Do you need to modify the data? If you're just reading from these you could define views that make it more straightforward to use EF against and nicer to code against. — Rory, Feb 06 '15 at 14:26
You are starting from a legacy database, but if you were to try the feature of code first to make it create its own database, you will find that loiti is right, your first model of table 1 is wrong.Your key of table1 is no longer correct because it has a single key, not a composite. You must keep the composite field in that table. How else is EF going to know that it is a composite key, now it thinks there is only a single key to table one and is going to act accordingly. — Philip Stuyck, Feb 07 '15 at 09:19
I think therefore the conclusion is that what you are trying to do is impossible. — Philip Stuyck, Feb 07 '15 at 09:31

score 1 · Accepted Answer · answered Feb 12 '15 at 20:49

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(),
}

score 0 · Answer 2 · answered Feb 12 '15 at 18:16

Create a new table that has the "Abbrev" column as its primary key. (This table would only have two rows per your example with "Abbrev" column values of "A" and "B".) Then define a foreign key relationship between this new table and the existing Table1. In the code, update the MyBaseClass for Table1 by appending the "Abbrev" column as part of the existing primary key definition.

This should resolve the "must have unique primary keys" error generated by MyBaseClass.

Table-per-hierarchy and composite primary key

2 Answers2