ado.net - How to map Table per Type (TPT) inheritance in Entity designer?

Question

I have created a good amount of tables in my database, here are the problem ones:

Table Name -Item
    ItemID - PK (Auto Increment)
    Title        

Table Name -Game
    ItemID - PK
    Console   

Table Name -Film
    ItemID - PK
    Type

I have also added an association between the ItemID of Item and Film-Item and Game-Item

The issue is when I place this in Visual Studio and use the Entity Framework it seems to somewhat collapse the classes and totally remove Film and Game... I then cannot use Game or Film as an entity in my ASP code, any ideas how to solve this?

Answer 1

Yes that is how Entity designer behaves. If you model your structure in the database and use Update model from database it will indeed be modeled as common associations beacuse EF doesn't know yet that you want to model it as inheritance. You will get this:

You must manually modify this model to use TPT inheritance. First delete both relations. It will also remove navigation properties and you will get this:

Use Inheritance from toolbox (as described on previous screenshot) and draw a line from Film to Item and from Game to Item. Now you need to finish this model. The current model will not validate because ItemId is mapped in both parent and child entity. Delete ItemId from both Film and Game entities. You can also make Item entity Abstract and you will get this: