CODEWITHSUNDEEP
c#entity-frameworktable-per-typetable-per-hierarchy
user3170736
user3170736

Reputation: 531

Entity Framework Multi-level Inheritance with TPH

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.

Upvotes: 10

Views: 1546

Answers (3)

Kahbazi
Kahbazi

Reputation: 15015

You could use this structure

enter image description here

 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
        ///
    }

Upvotes: 1

jrivor
jrivor

Reputation: 79

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.

Upvotes: 0

jjj
jjj

Reputation: 4997

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.

Upvotes: 0

Related Questions