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:

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?

Answer 1

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:

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: