Introduction to Code First Migrations

public class Program
{
    public static void Main(string[] args)
    {
        var context = new Context();
        var orders = context.Orders
            .Include(o => o.Items)
            .ToList();

        foreach (var order in orders)
            Console.WriteLine(order.ToString());

        Console.WriteLine("Press Enter to continue");
        Console.Read();
    }
}

public class Order
{
    public int OrderID { get; set; }
    public DateTime OrderedOn { get; set; }
    public List<OrderItem> Items { get; set; }

    public Order()
    {
        this.Items = new List<OrderItem>();
    }

    public override string ToString()
    {
        var sb = new StringBuilder();

        sb.AppendFormat("OrderID: {0}\n", this.OrderID);
        sb.AppendFormat("OrderedOn: {0}\n", this.OrderedOn);

        foreach (var orderItem in Items)
            sb.AppendFormat("OrderItemID: {0}, ItemNumber: {1}, Quantity: {2}, Price: {3:c2}\n",
                orderItem.OrderItemID, orderItem.ItemNumber, orderItem.Quantity, orderItem.Price);

        sb.AppendLine();

        return sb.ToString();
    }
}
public class OrderItem
{
    public int OrderItemID { get; set; }
    public int OrderID { get; set; }
    public Order Order { get; set; }
    public string ItemNumber { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public class Context : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Enabling Migrations

To enable Code First Migrations, open the Package Manager Console and execute the “Enable-Migrations” at the command prompt. This command will add a “Migrations” folder to your project. Enabling migrations only needs to be done once.

Within the Migrations folder you’ll find a Configuration class, whose Seed() method will allow us to add some test data to the database. Update the Seed() method to this:

protected override void Seed(CodeFirstMigrations.Context context)
{
    var order1 = new Order()
    {
        OrderID = 1,
        OrderedOn = new DateTime(2012, 10, 1, 8, 0, 0)
    };
    order1.Items.Add(new OrderItem()
    {
        OrderItemID = 1,
        Order = order1,
        ItemNumber = "Item1",
        Quantity = 1,
        Price = 10M
    });

    var order2 = new Order()
    {
        OrderID = 2,
        OrderedOn = new DateTime(2012, 10, 2, 10, 0, 0)
    };
    order2.Items.Add(new OrderItem()
    {
        OrderItemID = 2,
        Order = order2,
        ItemNumber = "Item1",
        Quantity = 2,
        Price = 10M
    });
    order2.Items.Add(new OrderItem()
    {
        OrderItemID = 3,
        Order = order2,
        ItemNumber = "Item2",
        Quantity = 1,
        Price = 20.5M
    });

    context.Orders.AddOrUpdate(order1);
    context.Orders.AddOrUpdate(order2);
}

Notice that we are explicitly setting the ID values for each of the entity instances. Doing so will allow the Context’s AddOrUpdate() extension method to successfully determine if the record already exists in the database or not. The Context’s SaveChanges() method will be called on our behalf, so there’s no need for us to call it directly.

Creating the Initial Migration

Now we’re ready to create the initial migration. To do so, we’ll use the Package Manager Console again, this time executing the “Add-Migration” command along with the name of the migration, “Initial”.

This command adds a class, named “Initial”, to the Migrations folder. The file name will be prefixed with a timestamp, ensuring that the migration classes will display in the correct chronological order. The class itself contains Up() and Down() methods: the Up() method contains the migration code for upgrading the database to this version of the model, while the Down() method contains the migration code for downgrading to the prior version.

Here’s the contents of the class:

public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Order",
            c => new
                {
                    OrderID = c.Int(nullable: false, identity: true),
                    OrderedOn = c.DateTime(nullable: false),
                })
            .PrimaryKey(t => t.OrderID);

        CreateTable(
            "dbo.OrderItem",
            c => new
                {
                    OrderItemID = c.Int(nullable: false, identity: true),
                    OrderID = c.Int(nullable: false),
                    ItemNumber = c.String(nullable: false, maxLength: 100),
                    Quantity = c.Int(nullable: false),
                    Price = c.Decimal(nullable: false, precision: 18, scale: 2),
                })
            .PrimaryKey(t => t.OrderItemID)
            .ForeignKey("dbo.Order", t => t.OrderID, cascadeDelete: true)
            .Index(t => t.OrderID);           
    }

    public override void Down()
    {
        DropIndex("dbo.OrderItem", new[] { "OrderID" });
        DropForeignKey("dbo.OrderItem", "OrderID", "dbo.Order");
        DropTable("dbo.OrderItem");
        DropTable("dbo.Order");
    }
}

For the most part, Code First Migrations will do a reasonable job of scaffolding the Up() and Down() methods, though some modifications will be necessary from time to time. For this migration, we needed to update the OrderItem.ItemNumber column definition to include “nullable” and “maxLength” arguments.

Updating the Database

Now we are ready to update the database, using the third and final Code First Migrations Package Manager Console command, “Update-Database”.

Notice that our Initial migration was applied and the Seed() method was ran. Here’s what our database tables look like from within SQL Server Management Studio:

Code First Migrations uses the “__MigrationHistory” system table to keep track of the database version. Currently, we only have a single row in the table:

If we run the Console Application, here’s the output:

Making a Model Change

Let’s make a change to the model. Storing the item number as a string in the OrderItem entity is not a great choice, so let’s setup an Item entity. We’ll also replace the OrderItem.ItemNumberstring based property with an Item entity based property. This change will also require us to make minor modifications to the Order.ToString() method override and the Program.Main()method.

Here’s the updated Program.cs file:

public class Program
{
    public static void Main(string[] args)
    {
        var context = new Context();
        var orders = context.Orders
            .Include(o => o.Items.Select(oi => oi.Item))
            .ToList();

        foreach (var order in orders)
            Console.WriteLine(order.ToString());

        Console.WriteLine("Press Enter to continue");
        Console.Read();
    }
}

public class Item
{
    public int ItemID { get; set; }
    public string ItemNumber { get; set; }
}
public class Order
{
    public int OrderID { get; set; }
    public DateTime OrderedOn { get; set; }
    public List<OrderItem> Items { get; set; }

    public Order()
    {
        this.Items = new List<OrderItem>();
    }

    public override string ToString()
    {
        var sb = new StringBuilder();

        sb.AppendFormat("OrderID: {0}\n", this.OrderID);
        sb.AppendFormat("OrderedOn: {0}\n", this.OrderedOn);

        foreach (var orderItem in Items)
            sb.AppendFormat("OrderItemID: {0}, ItemNumber: {1}, Quantity: {2}, Price: {3:c2}\n",
                orderItem.OrderItemID, orderItem.Item.ItemNumber, orderItem.Quantity, orderItem.Price);

        sb.AppendLine();

        return sb.ToString();
    }
}
public class OrderItem
{
    public int OrderItemID { get; set; }
    public int OrderID { get; set; }
    public Order Order { get; set; }
    public int ItemID { get; set; }
    public Item Item { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public class Context : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

The Configuration class Seed() method will also need to be updated.

protected override void Seed(CodeFirstMigrations.Context context)
{
    var item1 = new Item() { ItemID = 1, ItemNumber = "Item1" };
    var item2 = new Item() { ItemID = 2, ItemNumber = "Item2" };

    var order1 = new Order()
    {
        OrderID = 1,
        OrderedOn = new DateTime(2012, 10, 1, 8, 0, 0)
    };
    order1.Items.Add(new OrderItem()
    {
        OrderItemID = 1,
        Order = order1,
        Item = item1,
        Quantity = 1,
        Price = 10M
    });

    var order2 = new Order()
    {
        OrderID = 2,
        OrderedOn = new DateTime(2012, 10, 2, 10, 0, 0)
    };
    order2.Items.Add(new OrderItem()
    {
        OrderItemID = 2,
        Order = order2,
        Item = item1,
        Quantity = 2,
        Price = 10M
    });
    order2.Items.Add(new OrderItem()
    {
        OrderItemID = 3,
        Order = order2,
        Item = item2,
        Quantity = 1,
        Price = 20.5M
    });

    context.Orders.AddOrUpdate(order1);
    context.Orders.AddOrUpdate(order2);
}

Now we are ready to scaffold the migration. To do so, we execute the “Add-Migration” command again using the Package Manager Console, this time using the name “AddItemTable”.

Modifying the Migration

We’ll need to make some modifications to this migration in order to for it to successfully complete. Here’s a breakdown of what we want to do:

Up() Method

• Add “nullable” and “maxlength” arguments to the Item.ItemNumber column
• Create a unique index on the Item.ItemNumbember column
• Populate the Item table with a list of distinct OrderItem.ItemNumber value
• Update the OrderItem.ItemID values

Down() Method

• Add “nullable” and “maxlength” arguments to the OrderItem.ItemNumber column
• Update the OrderItem.ItemNumber column
• Drop the unique index on the Item.ItemNumber column

Here’s the updated contents of the migration class:

public partial class AddItemTable : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Item",
            c => new
                {
                    ItemID = c.Int(nullable: false, identity: true),
                    ItemNumber = c.String(nullable: false, maxLength: 100),
                })
            .PrimaryKey(t => t.ItemID);
        CreateIndex("dbo.Item", "ItemNumber", true);

        // populate the Item table
        Sql(@"
            insert Item (ItemNumber)
            select distinct ItemNumber
            from OrderItem
            order by ItemNumber
        ");

        AddColumn("dbo.OrderItem", "ItemID", c => c.Int(nullable: false));

        // update the OrderItem.ItemID column
        Sql(@"
            update oi
            set oi.ItemID = i.ItemID
            from OrderItem oi
            join Item i on i.ItemNumber = oi.ItemNumber
        ");           

        AddForeignKey("dbo.OrderItem", "ItemID", "dbo.Item", "ItemID", cascadeDelete: true);
        CreateIndex("dbo.OrderItem", "ItemID");
        DropColumn("dbo.OrderItem", "ItemNumber");
    }

    public override void Down()
    {
        AddColumn("dbo.OrderItem", "ItemNumber", c => c.String(nullable: false, maxLength: 100));

        // update the OrderItem.ItemNumber column
        Sql(@"
            update oi
            set oi.ItemNumber = i.ItemNumber
            from OrderItem oi
            join Item i on i.ItemID = oi.ItemID
        ");

        DropIndex("dbo.OrderItem", new[] { "ItemID" });
        DropForeignKey("dbo.OrderItem", "ItemID", "dbo.Item");
        DropColumn("dbo.OrderItem", "ItemID");
        DropIndex("dbo.Item", new[] { "ItemNumber" });
        DropTable("dbo.Item");
    }
}

Updating the Database

Now we’re ready to update the database. Simply execute the “Update-Database” command at the Package Manager Console prompt:

Here are updated looks at the database tables, the contents of the “__MigrationHistory” table, and the Console Application’s output:

Downgrading the Database

With Code First Migrations, we can just as easily downgrade a database as we can upgrade it. To do so, just execute the “Update-Database” command while specifying the “-TargetMigration” parameter.

With the “AddItemTable” migration successfully reverted, our database is now back at our initial state (as specified by the “Initial” migration).

Deployment Options

When it comes time to deploy your application into production, you have a couple of options for handling your Code First Migrations. First, you can set a database initializer so that all pending migrations are applied when the application starts up. For our Console Application, we just need to add the following line of code to the beginning of the Program.Main() method:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<Context, CodeFirstMigrations.Migrations.Configuration>());

Code First Migrations also allows you to generate a SQL script for the pending migrations. Just include the “-Script” parameter switch when executing the “Update-Database” Package Manager Console command, and Code First Migrations will generate and display a SQL script in Visual Studio. From there you can review the script, save it to disk, deliver it to your DBA, or execute it yourself against the production database.

Code First Migrations also work seamlessly with Windows Azure Web Sites. When publishing your site, just make sure that you’ve checked the “Execute Code First Migrations” check box listed underneath your database’s section within the Publish Web dialog’s Settings tab. When your web site starts up, all pending migrations will be applied to the site’s database.

Additional Resources

Additional information can be found on Microsoft’s MSDN Data Developer Center web site at:

http://msdn.microsoft.com/en-US/data/jj591621
http://msdn.microsoft.com/en-us/data/jj193542.aspx

A complete copy of the source code for this blog post can be downloaded from GitHub athttps://github.com/smashdevcode/code-first-migrations.