Entity Framework Migrations with an existing database

Introduction

If you read my post on Entity Framework migrations, you might be saying, “that’s amazing, but that’s all very well for new projects, but we’ve got eighty tables in place already and that code first migration ship has sailed”.
But no, you can still make use of Entity Framework migrations, it takes a some extra work, but it is well worth the effort.

If you already have POCOs that represent your databases tables and they really match your tables, you might be able to skip this step.

Reversing the Database

If your POCOs don’t match the database you can use the database tables to generate POCOs for you.

There are plenty of tutorials on Entity Framework’s reverse engineering tool. So I won’t go into too much detail.

Create a new solution with a console application as the startup project.
Set the connection string in your app.config to point to the your reference database (this is the one that has the all the tables you are currently using).

Grab the Entity Framework Power Tools Beta 4 (https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d/). Use this to reverse engineer the tables to POCOs. Review the outputted classes, mappings and context to make sure that they were generated the way you expect.

Notice that there is nothing on the POCOs to suggest where there should be indexes. Inferred primary key and foreign keys will be added in by the migration by default. But others will not.

First Migration

Go to the package manager and Enable-Migrations.
Enable-Migrations

Then add the first migration, Add-Migration InitalCreate, this is the migration that can be used to generate the tables.
Add-Migration

But, it is probably not complete, it’s going to be missing indexes as mentioned above, stored procs and more.

For example the Vehicle table looks like this
Table Layout

And has an indexes on VehicleID, TyreTypeIDa and Name, but the migration did not pick up the Name index.

Testing the migration

Change the connection string to point to new database.
And run Update-Database
Update-Database

You now have a new database based on the migration.
Open your SQL Server Management Studio, right click on the reference database and click Tasks, then Generate Scripts…
Generate-Script A

Click next and the choose the tables to script out.
6.Generate-Script B

Click next and then Advanced, scroll to the bottom of the list and turn on Script Indexes and any other feature you may need (eg, triggers).

7.Generate-Script C
Save the output to single file named ReferenceDB.sql

Follow the same steps to generate a script based on the new database.

Compare the files in a text comparison tool and you’ll see differences like this
Comparison

The index on the Name column is missing in the new database.

No problem. Go back to the migration and add it in.

Now you have two choices here, you can add a new migration to alter the existing table, or you can change the initial migration.
I suggest changing the InitalCreate migration, because you should try to get the this first migration to be as close as possible to the existing database.

Adding an index to a CreateTable call is as simple as adding one line at the end.

CreateTable( 
			"dbo.Vehicle", 
			c => new 
				{ 
					VehicleID = c.Int(nullable: false, identity: true), 
					Name = c.String(nullable: false, maxLength: 50), 
					Description = c.String(maxLength: 200), 
					MSRP = c.Double(nullable: false), 
					TyreTypeID = c.Int(nullable: false), 
				}) 
			.PrimaryKey(t => t.VehicleID) 
			.ForeignKey("dbo.TyreType", t => t.TyreTypeID, cascadeDelete: true) 
			.Index(t => t.TyreTypeID) 
                        
                         // this last line is the index I added by hand. 
			.Index(t => t.Name, name: "IX_Name");

Drop the new database the run Update-Database.

The Vehicle will now be created with the new index.
You could add the index to the POCOs, but you might want to consider how this would affect any move away from Entity Framework. See Code First Data Annotations for more.

For a full list of what you can do with migrations see DbMigration Methods.

Remember that you can run arbitrary sql with the Sql method or even from the SqlFile method. This gives you full flexibility when using Entity Framework migrations.

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.

Adding ROWGUIDCOL to Entity Framework Code First using migrations

To add add a ROWGUIDCOL to a unique identifier in a table using code first you have to use code migrations.

Below is the snippet you need. I haven’t covered how to perform a migration because there are plenty of articles available.

public partial class AddRowGuidCol : DbMigration
{
   public override void Up()
   {
      Sql("ALTER TABLE dbo.Address ALTER COLUMN [AddressID] ADD ROWGUIDCOL");
   }

   public override Void Down()
   {
      Sql("ALTER TABLE dbo.Address ALTER COLUMN [AddressID] DROP ROWGUIDCOL");
   }
}

Making a column sparse with Entity Framework Migrations

I have a database built off code first models, but I want to set some of the columns to sparse. There isn’t a way to this with the fluent api or through annotations. But it can be done with a migration.

There are plenty of articles that show how to run a migration so I will not cover that here.

Instead I’ll just show what you need to add to a new migration, I’m calling the migration MakeAddressSparse, and include the below code.

public partial class MakeAddressSparse: DbMigration
{
   public override void Up()
   {
      Sql("alter table dbo.Address alter column [Address1] varchar(100) sparse null");
      Sql("alter table dbo.Address alter column [Address2] varchar(100) sparse null");
      Sql("alter table dbo.Address alter column [Zipcode] varchar(10) sparse null");
   }

   public override Void Down()
   {
      Sql("alter table dbo.Address alter column [Address1] varchar(100) null");
      Sql("alter table dbo.Address alter column [Address2] varchar(100) null");
      Sql("alter table dbo.Address alter column [Zipcode] varchar(10) null");
   }
}