none
EDM - TPH with Foreign key as Discriminator Column

    Question

  • The question is, can I have a EDM where Car, Cycle, and Boat inherits from Vehicle, and where the mapping conditions for each of the sub-classes is based on the VehicleTypeID, which is a foreign key? 
     
    I have been successful creating an EDM where Car, Cycle and Boat inherits from Vehicle, but where VehicleTypeID is not a foreign key.  As soon as I make VehicleTypeID a foreign key, I start to get errors.  I fix one error then a different one is shown. 
     
    I would go into details of the errors I'm receiving, but first want to make sure this is possible before I waste yours and mine time. 
     
    I have searched the web for example that would relate to my situation, but have not found a solution.  I may have just been using the wrong keywords.
     
    Below is the proof of concept that I have been working on.
     
    Image of ERD
     
    Image of EDM Designer
     
    My table is in TPH format.  The main table is Vehicles, which contain records for Cars, Boats, and Bikes.  There is also a lookup table for Vehicle (LookupVehicleType) and it contains the types of vehicles (cars, boats, and bikes).  The two tables are joined and the Vehicle table contains a Foreign Key (VehicleTypeID) to LookupVehicleType table. 
     
    Vehicle Table
    VehicleID    VehicleTypeID      Model                    Year    Doors    HullType     BikeType
    1001                                1                      Roller                    2005          2          NULL            NULL
    1002                                2        2 wheel Rocket            2001       NULL        NULL           Road
    1003                                2        Hover                                  2003      NULL       Wood            NULL
     
    LookupVehicleType Table
    VehicleTypeID        Title
                    1                Car
                    2                Cycle
                    3                Boat
     
     
     
    Any Help is greatly appreciated
    BarDev
    • Edited by BarDev Friday, May 01, 2009 12:24 AM Reformat
    Friday, May 01, 2009 12:22 AM

Answers

  • The answer is yes and no.
    Entity Framework can map TPH to any column in this case it happens to be a foreign key column. The key point is a column that will act as discriminator cannot be mapped to an assocation unless it particpates in not null condition.  Basically VehicleTypeId is used in the discrminator and you are also wanting to map it to an assocation called VehicleType. This could invalidate the model. Imagine if both scenarious were possible, then u can create an instance of Car giving it a vehicleTypeId of 1 and then later change the VehicleType property on the Car to 2.

    This is why a column mapped in a discriminator cannot be mapped. Remove the VehicleType entity and you should be good.

    Zeeshan Hirani

    Friday, May 01, 2009 1:08 AM