Entity Framework one-to-many with table-per-hierarchy creates one foreign key column per subclass

Question

I have a Garage which contains Cars and Motorcycles. Cars and motorcycles are Vehicles. Here they are:

public class Garage
{
    public int Id { get; set; }
    public virtual List<Car> Cars { get; set; }
    public virtual List<Motorcycle> Motorcycles { get; set; }

    public Garage()
    {
        Cars = new List<Car>();
        Motorcycles = new List<Motorcycle>();
    }
}

public abstract class Vehicle
{
    public int Id { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
}

public class Car : Vehicle
{
    public int GarageId { get; set; }
    public virtual Garage Garage { get; set; }
    // some more properties here...
}

public class Motorcycle : Vehicle
{
    public int GarageId { get; set; }
    public virtual Garage Garage { get; set; }
    // some more properties here...
}

Why do Car and Motorcycle each have a GarageId and Garage property? If I push those properties up to the Vehicle superclass, EF complains and tells me navigation properties must reside in concrete classes.

Moving on, here's my DbContext:

public class DataContext : DbContext
{
    public DbSet<Garage> Garages { get; set; }
    public DbSet<Vehicle> Vehicles { get; set; }
    public DbSet<Car> Cars { get; set; }
    public DbSet<Motorcycle> Motorcycles { get; set; }

    public DataContext()
        : base("GarageExample")
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
    }
}

And here's a short program to play with my toys:

class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer<DataContext>(new DropCreateDatabaseAlways<DataContext>());

        using (var db = new DataContext())
        {
            var car1 = new Car { Make = "Subaru", Model = "Legacy" };
            var car2 = new Car { Make = "Porche", Model = "911" };

            var bike1 = new Motorcycle { Make = "Suzuki", Model = "GS500" };
            var bike2 = new Motorcycle { Make = "Kawasaki", Model = "Ninja" };

            var garage = new Garage();

            garage.Cars.Add(car1);
            garage.Cars.Add(car2);
            garage.Motorcycles.Add(bike1);
            garage.Motorcycles.Add(bike2);

            db.Garages.Add(garage);

            db.SaveChanges();
        }
    }
}

The program runs, and produces the following Vehicles table:

Id Make     Model  GarageId GarageId1 Discriminator
1  Subaru   Legacy 1        null      Car
2  Porche   911    1        null      Car
3  Suzuki   GS500  null     1         Motorcycle
4  Kawasaki Ninja  null     1         Motorcycle

With both Car and Motorcycle having their own GarageId and Garage properties, it seems that each subclass is creating its own foreign key to garage. How do I tell EF (via the fluent api, if possible) that Car.Garage and the Motorcycle.Garage are the same thing, and should use the same column?

This is the Vehicles table I want, of course:

Id Make     Model  GarageId Discriminator
1  Subaru   Legacy 1        Car
2  Porche   911    1        Car
3  Suzuki   GS500  1        Motorcycle
4  Kawasaki Ninja  1        Motorcycle

Answer 1

Use attribute [Column("GarageId")] on GarageId property on both car and motorcycle class.

Answer 2

The only way I know to get a single foreign key column and the database schema you want is giving up the navigation collections per derived type in Garage and use a single collection for the base type instead:

public class Garage
{
    public int Id { get; set; }
    public virtual List<Vehicle> Vehicles { get; set; }

    public Garage()
    {
        Vehicles = new List<Vehicle>();
    }
}

public abstract class Vehicle
{
    public int Id { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public int GarageId { get; set; }
    public virtual Garage Garage { get; set; }
}

public class Car : Vehicle
{
    // some more properties here...
}

public class Motorcycle : Vehicle
{
    // some more properties here...
}

Of course you are losing the comfortable type filter with lazy or eager loading when you only want to load Cars or Motorcycles of a Garage and you have to either load all Vehicles of a Garage or use projections or explicit loading to load derived types.

In my opinion it's perfectly valid what you are trying to do, but somehow it is not supported with Entity Framework, or mapping to FK columns hasn't been implemented in a way that this scenario can be supported.