Entity Framework non null foreign key migration
This post gives a quick overview of how to use Entity Framework migrations and a detailed example of how to handle the addition of a new non null foreign keyed column to an existing table with data in it. Not a trivial process as you will see. Full source code is provided, there are three versions of the same solution, one for each of the phases described below.
Having source control over your SQL database has been a challenge for a while, I’ve seen companies with the attitude - the database is the source control, and they were not joking! I’ve seen a few companies use a dedicated SQL directory within their source code for table creation, stored proc and seeding scripts and this worked ok, but it seemed to put up a barrier between development and dbeng.
You can also go down the route of a dedicated piece of software that is supposed to manage it all for you but they are expensive, and in my experience, buggy.
Entity Framework Migrations
Enter Entity Migrations to fill the gap! It allows you to use your source code as the source control for your database! If you haven’t already worked with EF code first, you should probably stop here and review that topic. I’ve been using EF migrations for a while, there are plenty of tutorials out there but none deal with the scenario I’m handling.
I have a Vehicle class, and a TyreType class. This is my starting structure, after my first migration I will have tables like those shown here. Assume that the application has been used for a while and there will be data in both tables.
Now I want to add a TyreColor class which will be referenced by the TyreType class.
There is also some plumbing in the context, you can see that in the source code provided.
This is a pretty standard one to many relationship. In the database it would look like -
The tricky part is dealing with the existing data in TyreType. I want to add a non null column, but I can’t because there are already rows of data.
The quick answer is - firstly add a nullable TyreColorId column, then set some default value in all existing rows, then set the column to non null for future inserts. If that is enough to help you, good, if not, see the detailed explanation below.
Phase 1 - standard EF Migration creating the first two tables.
Create the app, add the models, the context etc.
Go to the package manager console and type -
This creates Migrations/Configuration.cs file, I’ll come back to this shortly.
Add a migration for the models in your project.
Back in the package manager type -
This adds a file named something like - 201504280200272_InitialCreate.cs to the Migrations directory. Inside this file are Up() and Down() methods. The Up() will create the tables, set the primary key, create the foreign key and create an index.
And Down() drops the foreign key, index and tables.
Before I push these changes to the database I will add some seeding code in Configuration.cs
Back in the package manager call -
The db and tables are created and the seeder run.
Phase 2 – Adding the new table and foreign key fields
After creating this initial schema I added a TyreColor to the TyreType.
I want to make TyreColorId a required filed on TyreType and this is where everything starts to get complicated. I’ve already seeded the TyreType with four rows and now I want to add a new column that is non null and will act as a foreign key. This is a problem, the existing four rows will have empty TyreColorId columns and SQL server won’t allow this.
To overcome this I first add the new column as a nullable, set a default value in the existing rows, and finally set the column to non null.
Add TyreColorId and TyreColor to TyreType
In the package manger run -
This adds a new migration, but I’m going to alter the Up() and Down() methods –
See the inline notes for details.
I also updated the seed method
In the package manager run,
The changes are applied and the seed method is called (I’ve changed the seed method a little, you can see that in the source code).
I now have very close to the table structure I want, I just need to set the TyreColorId column in TryeType to non nullable and add an index.
Phase 3 – Making the new column non null
In the package manger run -
This will create a new migration with empty Up() and Down() methods. Add the following
Now I have the structure I want
And the data I want
Full source code is provided.