Table-per-hierarchy and composite primary key

c# composite-primary-key entity-framework entity-framework-6 table-per-hierarchy

Question

In a legacy database that I am unable to alter, I have two tables with the following data:

two legacy tables

Code and Abbrev make up Table 1's composite primary key, but Abbrev also serves as a discriminator (see below). CodeA and CodeB are two foreign key columns in Table2 that both refer to the same field, Code, in Table1. In the Table1.Code field, there are duplicate entries.

I want to use Entity Framework 6 with a table-per-hierarchy approach. Consequently, I developed 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; }

}

In my DataContext: DbContext class, I defined table-per-hierarchy 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 issue is that when I try to apply such mapping, I get the following error: I am unable to utilize the discriminator field (Table1.Abbrev) as a component of a composite key in the MyBaseClass.

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'.

Can the model above be mapped using Entity Framework 6 or a later version?

1
3
2/3/2015 11:07:39 PM

Accepted Answer

I'm afraid Entity Framework does not allow for this.

You must first map the complete key ofTable1 since EF is unable of identifyingTable1 by objectsCode only. Additionally, a compound primary key that contains a discriminator is simply not supported.

the inability to subtypeTable1 You could decide not to use inheritance if that were all there was to it. ButTable2 the true damper. Foreign keys must use a full primary key in order to reference EF. thus, givenTable1 a compound key be used,Table2 's two foreign keys key should likewise resemble{ Code, Abbrev } There isn't even one, really.Abbrev area inTable2 .

Maps are the only thing you can do.Table1 currently (without inheritance), as well asTable2 without any connection at all. To retrieve related records from the database in a single query, you must explicitly write joins (of a sort).

For example, to obtain aTable2 with aTable1 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 aTable2 having both aTable1 as A and aTable1 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(),
}
1
2/12/2015 8:49:24 PM

Popular Answer

Make a brand-new table with the "Abbrev" column serving as the main key. (According to your example, this table would only contain two rows with the values "A" and "B" in the "Abbrev" column.) Then establish a foreign key connection between this new table and Table1 already in existence. Update the MyBaseClass for Table1 in the code by adding the "Abbrev" column to the declaration of the primary key that already exists.

This ought to fix the MyBaseClass "must have unique primary keys" problem.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow