EF abstract base class with key where derived class has different key

Question

Time for a dumb question. I think the database design is screwy, but there isn't much I can do about that part of it. I have a table in the database "Table1" and then "Table2" which is essentially an extension of Table1 (I also have Table3, Table4, etc). My problem is that Table2 has it's own unique key, even though it's a one for one relationship. Then Table2Component uses Table2Id as it's foreign key. However, when I try to use that in my code I think it's pointing to Table1Id. I get the error message:

System.Data.Entity.Edm.EdmAssociationConstraint: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'Table2Id' on entity 'Table2Component' does not match the type of property 'Table1Id' on entity 'Table2' in the referential constraint 'Table2Component_Table2'.

Here is the code

[Table("Table1")]
public abstract class Table1
{
    [Key]
    [Column("table1_id")]
    public string Table1Id { get; set; }

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

    [Column("type_cd")]
    public string TypeCode { get; set; }
}

[Table("Table2")]
public class Table2 : Table1
{
    [Key]
    [Column("table2_id")]
    public int Table2Id { get; set; }

    [ForeignKey("Table1Id")]
    public virtual Table1 Table1 { get; set; }

    // this table also has a table1_id column
    // but I guess I don't need it here, correct?

    [Column("column1")]
    public string Column1 { get; set; }

    public virtual ICollection<Table2Component> Table2Components { get; set; }
}

[Table("Table2Component")]
public class Table2Component : ISubItem
{
    [Key]
    [Column("table2_component_id")]
    public int Table2ComponentId { get; set; }

    [Column("table2_id")]
    public int Table2Id { get; set; }

    [Column("description")]
    public string Description { get; set; }

    public bool Required { get { return true; } }

    [ForeignKey("Table2Id")]
    public virtual Table2 Table2 { get; set; }
}

Any suggestions? Should I be more forceful in trying to get the database changed?

search for `Entity Framework 1:1 relationship` eg http://stackoverflow.com/a/14997417/1347784 the restriction is both tables must have the same foreign key when using 1:1 — phil soady, Nov 21 '13 at 04:02
Thanks for the help. Generally speaking, is it better database design to have the primary key for Table1 be the primary key for Table2 also? Then I guess Table2Component would have Table1Id as a foreign key. — nickfinity, Nov 21 '13 at 14:03
no not necessarily better database design. Just the why the EF team built the framework. Ive learnt to live with the restrictions. In code first scenario, no big deal. Try the powertool to reverse engineer the alternative approach. EF will use 1:M even though you may see it as 1:1. Also OK in my view. — phil soady, Nov 21 '13 at 22:56

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Started as comment.... finish as simple answer, since no one else jumped in.

Search for Entity Framework 1:1 relationship eg https://stackoverflow.com/a/14997417/1347784 the restriction is both tables must have the same foreign key when using 1:1

No not necessarily better database design. It is Just the why the EF team built the framework. Ive learnt to live with the restrictions. In code first scenario, no big deal. Try the powertool to reverse engineer the alternative approach when you start with the DB. EF will use 1:M even though you may see it as 1:1. Also OK in my view.

edited May 23 '17 at 12:05

Community

1
1

answered Nov 21 '13 at 22:58

phil soady

11,043
5
50
95

Thanks for the help. I think I'm going to share the primary key. It makes more sense in my case and will make things easier down the road. – nickfinity Nov 22 '13 at 13:58

EF abstract base class with key where derived class has different key

1 Answers1