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

http://cid-98b982e83e8a47af.skydrive.live.com/self.aspx/Everyone/VehicleERD.jpg

 

Image of EDM Designer

http://cid-98b982e83e8a47af.skydrive.live.com/self.aspx/Everyone/Vehicle%20Class%20Diagram.jpg

 

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

Answer 1

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