Entity Framework Multi-level Inheritance with TPH

Question

I am working with a legacy system that implements a TPH for a certain number of items. So the current structure looks like this

 Abstract Class 1     Abstract Class 2     Abstract Class 3
         |                    |                    |
     ---------            ---------            ---------
    |    |    |          |    |    |          |    |    |
    T1   T2   T3        T4    T5   T6         T7   T8   T9

So Type (T*) is a discriminator across all tables but since certain types share common columns, there are a significant number of different tables. The problem is that all of these items actually share a small commonality but there is no way to gather all of these items into a collection. In reality the hierarchy actually should look more like this.

          --------------- Base Abstract 1 ---------- 
         |                    |                    |
 Abstract Class 1     Abstract Class 2     Abstract Class 3
         |                    |                    |
     ---------            ---------            ---------
    |    |    |          |    |    |          |    |    |
    T1   T2   T3        T4    T5   T6         T7   T8   T9

So essentially what we have is a TPT where each Table per type is a TPH. For a real world example, here is what we need.

          ---------------  Vehicle   --------------- 
         |                    |                    |
        Car                 Boat                 Plane
         |                    |                    |
     ---------            ---------            ---------
    |    |    |          |    |    |          |    |    |
   BMW Toyota Fiat      T4   T5   T6         T7    T8   T9

Obviously there are some design flaws with the initial design and no one anticipated needing to grab a list of all vehicles without querying 3 different tables. So my question is, with the existing structure is there a way add this new hierarchy to entity framework. I was thinking something like this

  Vehicle
  -------
  VehicleId
  TypeId (Boat, Plane, Car, etc)
  ItemFK (BoatID, PlaneId, CarId)

Is this possible? Is there a way to map these in entity framework? I cant seem to match them correctly. It seems it could possibly work if we were to replace BoatId, PlaneId, and CarId with VehicleId (like Conditional Mapping in Entity Framework - OR operation with TPH) but at that point we would be doing a really invasive schema change which is not really an option and I'm not sure that would even work. Essentially I need a way to map existing keys into a new hierarchy. Any help is greatly appreciated. I'm at a loss and can't seem to find any solution that answers my question.

Figurine

So Vehicle, Car, etc, are tables and BMW ... Tx are discriminators? – Privy

That is correct. The biggest issue really here is that there are Id collisions in Car, Boat, and Plane. – Figurine

Why do you need all those types to be in the same type hierarchy? Could you achieve what you're looking for with interfaces? – Frentz

Conceivably? The benefit of having them in a type hierarchy would be the ability to query them and treat them like one collection. As it stands now, to get all of the objects I would need 15 different queries, and then put them in a common interface list to iterate over them. Not very maintainable. – Figurine

Have you tried combining both the TPH and TPC fluent API? It sounds like you've tried something that didn't work. Also, you're talking about 15 different queries over properties with the same name? Since there are separate tables, the generated query won't be any simpler, so you just want to make the C# development side more maintainable, right? – Frentz

Answer 1

You could use this structure

 public class Vehicle
    {
        [Key]
        public int Id { set; get; }

        ///
        // common properties
        ///

        public Car Car { set; get; }
        public Boat Boat { set; get; }
        public Plane Plane { set; get; }
    }

    public class Car
    {
        [Key, ForeignKey("Vehicle")]
        public int VehicleId { set; get; }
        public Vehicle Vehicle { set; get; }

        ///
        // Car properties
        ///
    }

    public class Boat
    {
        [Key, ForeignKey("Vehicle")]
        public int VehicleId { set; get; }
        public Vehicle Vehicle { set; get; }

        ///
        // Boat properties
        ///
    }

    public class Plane
    {
        [Key, ForeignKey("Vehicle")]
        public int VehicleId { set; get; }
        public Vehicle Vehicle { set; get; }

        ///
        // Plane properties
        ///
    }

Balm

Answer 2

The problem is that all of these items actually share a small commonality but there is no way to gather all of these items into a collection.

Perhaps you could have types in each hierarchy implement a common interface? Since each hierarchy is already a separate table, it doesn't seem like you would gain much -- and it seems like it would not be worth the hassle -- by adding a common base class.

Frentz

Answer 3

The TPH/TPC conventions can be defined based on the DbSet<> that you define in your DbContext. For example, instead of declaring a DbSet<> per derived type T, you only declare a DbSet<> for each abstract type. You can then query abstract classes individually with their corresponding DbSet<> or all abstract classes with the DbSet<> of the base abstract type.

The base abstract class will have to have at least one field defined so Code-First Migrations will generate a table for the type. The most logical field to define would be the PK. However, a migration of the current data won't work because of the PK collisions between the abstract classes (as you stated in a comment).

Another possibility is that Entity Framework will properly query all the abstract types when you query a DbSet<> of the base abstract type even if there is no table in the database for the base abstract type (because the base abstract type has no fields defined). However, I haven't run into this scenario before so I can't say with certainty whether it will work or not.

Tacho