ADO.Net EF - how to define foreign key relation in model first approach?

I have been having previous issues regards to class inheritance and structuring a database around this and using the entity framework to no success. So I have tried to create the entities inside visual studio and see what database tables it creates automatically.

I have a Entity MediaItem which is Abstract and Game Inherits from this. Game has a Console (Int) which corresponds to ConsoleID. However, when I generate the database I get an extra unwanted column (ConsoleTypes_ConsoleID) inside MediaItems_Game table. Why is this and how can I prevent this from happening? Thanks.

Solution 1:

First of all your model is probably wrong. ConsoleType and Game is not in one-to-one relation (unless you have single game for each console type). I expect 1 console can have multiple games. So it should be one-to-many. In reality game can be released on multiple platforms so it should be many-to-many.

You got unvanted column because your relation between ConsoleType and Game doesn't know that it should use Console property as a foreign key. This happens if you use independent association. Independent associations are used by default when you draw them from one entity to other entity in Entity designer. You must use foreign key association.

Start with this set up (draw association from ConsoleType to Game - you must have one-to-many relation):

Select relation between ConsoleType and Game an in properties click on Referential Constraint:

In Referential Constraint dialog just set up relation:

Save your model and generate database again.