Entity Framework non null foreign key migration
Full source code is provided.
Overview
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.
Introduction
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.
The Problem
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.
1public class TyreType
2 {
3 public int TyreTypeID { get; set; }
4
5 //snip
6
7 public int TyreColorId { get; set; }
8 public virtual TyreColor TyreColor { get; set; }
9 }
10
11 public class TyreColor
12 {
13 public TyreColor()
14 {
15 this.TyreTypes = new List();
16 }
17 public int TyreColorId { get; set; }
18 public string Name { get; set; }
19 public string Description { get; set; }
20
21 public virtual ICollection TyreTypes { get; set; }
22 }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 Solution
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.
Step 1
Create the app, add the models, the context etc.
Go to the package manager console and type -
Enable-Migrations
This creates Migrations/Configuration.cs file, I’ll come back to this shortly.
Step 2
Add a migration for the models in your project.
Back in the package manager type -
Add-Migration InitialCreateThis 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.
Step 3
Before I push these changes to the database I will add some seeding code in Configuration.cs
1 protected override void Seed(DataAccess.AutomobileContext context)
2 {
3 context.TyreType.AddOrUpdate(tt => new { tt.Name },
4 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber" },
5 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber" },
6 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber" },
7 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber" }
8 );
9 }Back in the package manager call -
Update-DatabaseThe 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.
Step 1
Add TyreColorId and TyreColor to TyreType
1 public class TyreType
2 {
3 //snip
4 public int TyreColorId { get; set; }
5
6 public virtual TyreColor TyreColor { get; set; }
7 }In the package manger run -
Add-Migration AddingTyreColorThis adds a new migration, but I’m going to alter the Up() and Down() methods –
1 public override void Up()
2 {
3 CreateTable(
4 "dbo.TyreColor",
5 c => new
6 {
7 TyreColorId = c.Int(nullable: false, identity: true),
8 Name = c.String(),
9 Description = c.String(),
10 })
11 .PrimaryKey(t => t.TyreColorId);
12
13 AddColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
14
15 //Insert a default TyreColor
16 Sql("Insert INTO dbo.TyreColor (Name, Description) VALUES ('Black', 'Very black')");
17
18 //Set all null TyreType.TyreColorId to the default
19 Sql("UPDATE dbo.TyreType SET TyreColorId = 1 WHERE TyreColorId IS NULL");
20
21 AddForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor", "TyreColorId", cascadeDelete: true);
22 }
23
24 public override void Down()
25 {
26 DropForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor");
27 DropColumn("dbo.TyreType", "TyreColorId");
28 DropTable("dbo.TyreColor");
29 }See the inline notes for details.
I also updated the seed method
1 protected override void Seed(DataAccess.AutomobileContext context)
2 {
3 context.TyreColor.AddOrUpdate(tc => tc.Name,
4 new TyreColor { Name = "Black", Description = "Black" },
5 new TyreColor { Name = "Gray", Description = "A little black" },
6 new TyreColor { Name = "White", Description = "Very white" },
7 new TyreColor { Name = "Black/White", Description = "A mix of black and white" }
8 );
9
10 context.SaveChanges();
11
12 // grab the one that was added
13 TyreColor firstTyreColor = context.TyreColor.First();
14
15 context.TyreType.AddOrUpdate(tt => new { tt.Name, tt.TyreColorId },
16 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
17 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
18 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber", TyreColorId = firstTyreColor.TyreColorId },
19 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber", TyreColorId = firstTyreColor.TyreColorId }
20 );
21 }In the package manager run,
Update-DatabaseThe 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 -
Add-Migration AlterTyreColorToNonNullThis will create a new migration with empty Up() and Down() methods. Add the following
1 public override void Up()
2 {
3 AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: false));
4 CreateIndex("dbo.TyreType", "TyreColorId");
5 }
6
7 public override void Down()
8 {
9 DropIndex("dbo.TyreType", new[] { "TyreColorId" });
10 AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
11 }Now I have the structure I want
And the data I want
Full source code is provided.