One issue I’ve come across lately while working with Entity Framework Migrations has to do with foreign key relationships. If you’ve ever done any reconfiguration of your schema, you know you probably need to update your migration files to get all the data loaded correctly. Let’s take a simple example:
Let’s say you have an Order model for all of your orders defined as such:
This is obviously a very contrived example and a real order would have a lot of other information, but it works for this example.
Now let’s say you add a new ordering customer and you want to distinguish orders by Customer (probably a good thing to do!). Here is the Customer object:
Now we need to modify our order by adding a CustomerID column. The result looks as you’d expect:
You’ll need to properly set up your mapping where ever you have that defined. There are multiple ways to do this, but the approach I prefer is to have mapping files defined separate from my POCOs (Plain old C# objects, the classes defined above) and then add them in the OnModelCreating function of my Context class like this:
Inside the constructor of my OrderMap class, this line of code will add the relationship between Customer and Order:
Now, with all that setup, if you add a migration, Entity Framework will scaffold the changes required to make all this happen.
You’ll probably want to modify a couple of things, though.
For starters, you’ll want to setup your customers and set all of your existing orders to use the Customer ID associated with the orders that already exist in the system. Also, you have to do this before adding the Foreign Key between Order and Customer because after you add the new column (it is non-nullable), all CustomerID fields will be “0” in your Orders table.
Your migration might look something like this (EF will also add some indexes, I am showing this for the sake of brevity):
You’ll need to add code to update those customers. I usually write a line of Sql like the following, and place it after the create table but before the AddForeignKey call:
This will then get everything in your database ready to add that foreign key constraint. Of course, if you don’t like hardcoding company information into your migrations (not a great practice, really), you can do this after the fact, but sometimes you already have all the data in your database and just need to move it around due to a schema change. This is, again, a bit of a contrived example.
Now, to the tricky part I really want to highlight: you have to be really consistent in the way you add foreign keys.
For example, these two lines are slightly different – the first one uses the schema name in both the dependent and principal tables, while the second only does in for the dependent table:
The foreign key names they generate are as follows:
If you later try to drop a foreign key and don’t use the same exact format as you did when setting it up, you will encounter errors – usually something like:
The object ‘FK_dbo.Orders_dbo.Customers_CustomerID’ is dependent on column ‘CustomerID’.
ALTER TABLE DROP COLUMN CustomerID failed because one or more objects access this column.
So the moral here is to be very consistent with your foreign key naming scheme. If you’ve got an old database that you’ve added code first to after the fact, you’ll probably have a lot of relationships that don’t use the schema name in the key name, so you’ll run into this frequently if you’re modifying your schema.