Entity Framework non null foreign key migration

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.

Initial DB layout

Now I want to add a TyreColor class which will be referenced by the TyreType class.

public class TyreType
    {
        public int TyreTypeID { get; set; }

        //snip

        public int TyreColorId { get; set; }
        public virtual TyreColor TyreColor { get; set; }
    }

    public class TyreColor
    {
        public TyreColor()
        {
            this.TyreTypes = new List();
        }
        public int TyreColorId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public virtual ICollection TyreTypes { get; set; }
    }

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 -

DB layout after addition

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

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 InitialCreate

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.

Step 3

Before I push these changes to the database I will add some seeding code in Configuration.cs

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreType.AddOrUpdate(tt => new { tt.Name },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber" },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber" },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber" },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber" }
                 );
        }

Back in the package manager call -

Update-Database

The db and tables are created and the seeder run.

Tyre Type Table

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

  public class TyreType
    {
	   //snip
        public int TyreColorId { get; set; }

        public virtual TyreColor TyreColor { get; set; }
    }

In the package manger run -

Add-Migration AddingTyreColor

This adds a new migration, but I’m going to alter the Up() and Down() methods –

        public override void Up()
        {
            CreateTable(
                "dbo.TyreColor",
                c => new
                    {
                        TyreColorId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Description = c.String(),
                    })
                .PrimaryKey(t => t.TyreColorId);

            AddColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
           
            //Insert a default TyreColor
            Sql("Insert INTO dbo.TyreColor (Name, Description) VALUES ('Black', 'Very black')"); 
            
            //Set all null TyreType.TyreColorId to the default
            Sql("UPDATE dbo.TyreType SET TyreColorId = 1 WHERE TyreColorId IS NULL"); 

            AddForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor", "TyreColorId", cascadeDelete: true);
        }

        public override void Down()
        {
            DropForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor");
            DropColumn("dbo.TyreType", "TyreColorId");
            DropTable("dbo.TyreColor");
        }

See the inline notes for details.

I also updated the seed method

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreColor.AddOrUpdate(tc => tc.Name,
                 new TyreColor { Name = "Black", Description = "Black" },
                 new TyreColor { Name = "Gray", Description = "A little black" },
                 new TyreColor { Name = "White", Description = "Very white" },
                 new TyreColor { Name = "Black/White", Description = "A mix of black and white" }
                 );

            context.SaveChanges();
            
            // grab the one that was added
            TyreColor firstTyreColor = context.TyreColor.First();  
            
            context.TyreType.AddOrUpdate(tt => new { tt.Name, tt.TyreColorId },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber", TyreColorId = firstTyreColor.TyreColorId }
                 );
        }

In the package manager run,

Update-Database

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 -

Add-Migration AlterTyreColorToNonNull

This will create a new migration with empty Up() and Down() methods.
Add the following

        public override void Up()
        {
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: false));
            CreateIndex("dbo.TyreType", "TyreColorId"); 
        }
        
        public override void Down()
        {
            DropIndex("dbo.TyreType", new[] { "TyreColorId" });
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
        }

Now I have the structure I want

Data In Tables

And the data I want

Final Table Structure

Full source code is provided.

One thought on “Entity Framework non null foreign key migration

  1. Pingback: Entity Framework Migrations with an existing database | no dogma blog

Leave a Reply

Your email address will not be published. Required fields are marked *