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

Gerrilee

is it absolutely essential for Vehicle to be Abstract? Due to the way that Entity Framework derives proxy classes, you can't have navigation properties in Abstract Classes, since they can't be instantiated. This hierarchy would work if Vehicle was not Abstract. – Ethelda

@AndrewCounts: It still won't work with a concrete Vehicle type. – Romans

Looks same: #4389727 – Norri

Answer 1

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

Noggin

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.

Romans

your suggestion wouldn't work either, because it doesn't solve the root issue, that Garage is a navigation property that can't be instantiated on the abstract class Vehicle. – Ethelda

@AndrewCounts: I have working models of this kind in production. The problem has nothing to do with the base type being abstract or not. Of course, when an entity gets materialized the instantiated Vehicle is always a Car or Motorcycle and there is no problem to set the Garage navigation property to a valid reference. – Romans

Answer 3

    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 int GarageId { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }
    }

    public class Car : Vehicle
    {
        [ForeignKey("GarageId")]
        public virtual Garage Garage { get; set; }
        // some more properties here...
    }

    public class Motorcycle : Vehicle
    {
        [ForeignKey("GarageId")]
        public virtual Garage Garage { get; set; }
        // some more properties here...
    }

Gorey

Answer 4

Have you looked at this yet?

Mapping the Table-Per-Hierarchy (TPH) Inheritance

In the TPH mapping scenario, all types in an inheritance hierarchy are mapped to a single table. A discriminator column is used to identify the type of each row. When creating your model with Code First, TPH is the default strategy for the types that participate in the inheritance hierarchy. By default, the discriminator column is added to the table with the name “Discriminator” and the CLR type name of each type in the hierarchy is used for the discriminator values. You can modify the default behavior by using the fluent API.

modelBuilder.Entity<Course>() 
.Map<Course>(m => m.Requires("Type").HasValue("Course")) 
.Map<OnsiteCourse>(m => m.Requires("Type").HasValue("OnsiteCourse"));

Straight from here.

Creath

I have no problem with the way EF is using the Discriminator column the differentiate between cars and motorcycles. – Gerrilee

This isn't answering the question – Aggarwal