EF inheritance with TPT - how to specify the foreign key column name?

Question

I want to map two derived classes to two tables (TPT)

[Table("User")]
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

[Table("Employee")]
public class Employee : User
{
    public int UserId { get; set; }
    public string DeptName { get; set; }
}

The tables already exist (i.e. I can't modify the schema) and are defined like this:

enter image description here

Note that the column UserId in table Employee is both its PK and a FK to table User, column Id.

The DbContext is as straight as possible - no fluent mappings defined.

public class TestInheritContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

When trying to access the Users collection, I get the following EF exception:

System.Data.SqlClient.SqlException: Invalid column name 'Id'.

Apparently, it tries to read column Id from the table Employee.

All examples of TPT that I have seen use the same PK column name in all the tables. For instance, this one.

How do I fix it?

Have you tried putting a `[Key("UserId")] ` on your `UserId` property ? — jbl, Jun 23 '15 at 12:16
@jbl That property is already mapped to the column of the same name. The problem is, the `Id` property of the base class is inherited in class `Employee` and EF tries to read it from column `Id` in table `Employee`, which does not exist. — Cristian Diaconescu, Jun 23 '15 at 12:42
So I guess you didn't try ;-) I fluently map TPT hierarchy with different column names for parent and child key columns. The only needed point is specifying the Key and its column name in both types. — jbl, Jun 23 '15 at 13:14
I *did* try that. Same error. Btw, the `[Key]` attribute ctor doesn't take any parameters. I used both `[Key]` and `[Column("UserId")]`. — Cristian Diaconescu, Jun 23 '15 at 13:27
@jbl PS. Yup, fluent mapping is what I have arrived at too - any opinions on my answer below? — Cristian Diaconescu, Jun 23 '15 at 13:30
Seems fine. From my experience trying to access objects in a DbSet crashes. I always have to use DbSet.OfType... Let me know if this works for you — jbl, Jun 23 '15 at 13:39
@jbl I already checked. I was curious if `.OfType()` gets run on the DB or locally. Happy to report that `DbSet` and `DbSet.OfType` both work *and* the generated SQL is identical. Pretty neat. — Cristian Diaconescu, Jun 23 '15 at 14:01

score 6 · Accepted Answer · answered Jun 23 '15 at 13:28

Figured it out.

First, in order to debug this, it was helpful to see the actual mapping that EF creates behind the scenes.

I installed the EF Power Tools VS extension, r-clicked the context file,

Entity Framework -> View Entity Data Model

and got this:

enter image description here

Notice the entity Employee has both its own UserId property and the inherited Id property.

So the first thing I did was remove the UserId property from the derived class:

[Table("Employee")]
public class Employee : User
{
    // not needed
    //public int UserId { get; set; } 

    public string DeptName { get; set; }
}

This isn't enough, though. I now have the Id property from the base class that has to point to two differentlynamed columns, depending on which table/entity it's coming from:

For User: Id => Id
For Employee: Id => UserId

There's no way I can use attributes for that, so I'll go with fluent syntax.

For the User entity, I don't have to do anything, since the column name matches the property name.

For the Employee entity I have to intervene:

public class TestInheritContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .Property(e => e.Id)
            .HasColumnName("UserId");
    }
}

This finally produces the correct mapping: enter image description here

EF inheritance with TPT - how to specify the foreign key column name?

1 Answers1