Damir's Corner.

Adding Foreign Key Properties to an Existing Entity Framework Model

One of the features introduced in Entity Framework 4 was support for foreign key properties in entity types. No matter what your opinion is about having foreign keys exposed by an ORM, there are some cases where it might be more practical or even more performant to use foreign key properties instead of navigation properties (e.g. when you are creating a new object with a reference for which you know the primary key value but don't have a corresponding object in your ObjectContext). That's the reason why it's usually recommended to include foreign key columns in your model even though it will pollute your entity types with properties you might not need at all.

But what if you decided not to include them in your model when you originally created it, but want to add them at a later time? Although the checkbox is enabled in the model Update Wizard which opens up when you want to Update Model from Database, its value only effects newly created entity types, but leaves existing entity types unchanged. Unless you feel comfortable deleting existing entity types and recreating them from database, you'll have to add the properties by hand. Once you know how to do it, it's just a matter of following a few simple steps. I'll describe them on a simple example from Northwind database. I've only included three entity types: Category, Product and Supplier. We want to add CategoryID and SupplierID properties to the Product entity type.

First add a new scalar property to the entity type:

1. Right click on the Product entity type.
2. Click on the Add > Scalar Property from the context menu.
3. Click on the newly created Property and set its properties: Name to CategoryID, Nullable to True and Type to Int32.

Next you need to map the database column to your new property:

1. Open Mapping Details for Product entity type.
2. Select the CategoryID : Int32 as Value / Property for the CategoryID : int column.

Now it's time to set up the referential constraint:

1. Select the FK_Products_Categories association in the Model Browser.
2. Open the Referential Constraint editor from its properties window.
3. Select Category as Principal and CategoryID as Dependent Property.

Only one step left, deleting the mapping of foreign key to the navigation property which is not allowed when you have a foreign key property in your entity type:

1. Select the Category property of the Product entity type.
2. Click on the Delete mappings link displayed in the Mapping Details window.

You have to repeat the above steps for the SupplierID property and you have created a functionally identical model to the one automatically generated for you if you had selected to include foreign keys in the model before adding the tables to it.

The above process is not only useful when you change your mind about the foreign key properties at a later time. You can also intentionally decide to add the foreign key properties only when you actually need them and avoid having redundant properties at the cost of the consistency of the model.