CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6relational-database
user2549803
user2549803

Reputation: 343

Duplicate ForeignKey when using inheritance

I have created these classes in order to generate the database model via EntityFramework 6 code-first approach: 

public class Vehicle 
{
    public long Id { get; set; }

    public long ResponsiblePersonId { get; set; }
}

public class Car: Vehicle {

    public int HorsePower { get; set; }

}

public class Bike: Vehicle {

    public int FrameSize { get; set; }

}

public class Organisation
{
    public Organisation()
    {
        Cars = new List<Car>();
        Bikes = new List<Bikes>();
    }

    public long Id { get; set; }


    public List<Car> Cars { get; set; }

    public List<Bike> Bikes { get; set; }
}

So far this seemed right for me. But unfortunately, the resulting table looks like this:

Id | ResponsiblePersonId | HorsePower | FrameSize | Discriminator | Organisation_Id | Organisation_Id1

Why is the Organisation ForeignKey being generated twice? I expected this table to only have one Organisation_Id column.

Thanks

Upvotes: 4

Views: 2137

Answers (1)

Diana
Diana

Reputation: 2226

There are several ways for EF to implement the physical tables for your inheritance hierarchy. The default one, the one you are using, is called Table Per Hierarchy (TPH). It uses only one table for all the derived entities, with one Discriminator column to specify the type of entity which is contained in the record. EF also adds to the table a column for each property that is included in any of the derived entities.

So as the relationship between the derived entities and Organisation is defined at child level (the lists of Car and Bike properties in Organisation entity) EF decides to create a separate column for each child entity type Organisation_Id, and you don't want that.

How to change this? There are several ways:

  • Don't use TPH. Use instead TPC (Table Per Concrete class). That is, EF creates a separate table for each one of your child entities. How to do this: remove the DbSet<Vehicle> property from your DbContext. If this doesn't make it, set an explicit configuration for the physical table name for each entity derived from Vehicle like this:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    ...
    modelBuilder.Entity<Car>().ToTable("Cars");
    modelBuilder.Entity<Bike>().ToTable("Bikes");
}

  • If you need to continue using TPH, I don't know of any way to implementing this that will generate only one OrganisationId column in the database and only one Foreign Key between Vehicle and Organisation. Common sense would say that you might define the Organisation foreign key at the Vehicle base entity level. But then you get errors when generating the migration:

    Organisation: FromRole: NavigationProperty 'Organisation' is not valid. Type 'Car' of FromRole 'Organisation_Cars_Target' in AssociationType 'Organisation_Cars' must exactly match with the type 'Vehicle' on which this NavigationProperty is declared on.

    It seems that when the relationship is defined at base level then EF expects the lists in Organisation to be defined of type Vehicle and not Car or Bike. And this does not fit with your model.

    And if you try to define OrganisationId or Organisation properties in your derived classes then you get a different error when generating the migration, because you are not allowed to use the same name for the properties in the different child entities. You can use different names, but then you get two columns again. There is no way to get one column this way either.

    So if you stick with TPH, as far as I know, you have to put up with having two columns for your OrganisationId. At least you can name them in a more verbose way with some fluent configurations:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    ...
    modelBuilder.Entity<Organisation>()
        .HasMany(o => o.Bikes)
        .WithRequired()
        .Map(x => x.MapKey("OrganisationIdBike"));

    modelBuilder.Entity<Organisation>()
        .HasMany(o => o.Cars)
        .WithRequired()
        .Map(x => x.MapKey("OrganisationIdCar"));
}

I would recommend you to change to TPC, as with your model the fluent mappings are a bit less complex to write.

For a better understanding of TPH, TPC and TPT (Table Per Type, yet another implementation of inheritance hierarchies) read this post.

Upvotes: 4

Related Questions