EF 4.3 Automatic Migrations Walkthrough

The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Automatic Migrations see http://msdn.com/data/jj554735

We have released the first go-live release of our Code First Migrations work as part of Entity Framework 4.3.

This post will provide an overview of the functionality that is available inside of Visual Studio for interacting with migrations. We will focus on the workflow that combines automatic and code-based migrations. In this workflow most changes can be automatically calculated and applied. More complex changes can be written out to code-based migrations that can reside in your project.

There is a separate Code-Based Migrations Walkthrough that shows how this same set of changes can be applied using purely code-based migrations.

This post assumes you have a basic understanding of Code First, if you are not familiar with Code First then please complete the Code First Walkthrough.

Building an Initial Model & Database

Before we start using migrations we need a project and a Code First model to work with. For this walkthrough we are going to use the canonical Blog and Post model.

Create a new MigrationsAutomaticDemo Console application.
.
Add the latest version of the EntityFramework NuGet package to the project.
- Tools –> Library Package Manager –> Package Manager Console.
- Run the ‘Install-Package EntityFramework’ command.
  .

Add a Model.cs file with the code shown below. This code defines a single Blog class that makes up our domain model and a BlogContext class that is our EF Code First context.

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace MigrationsAutomaticDemo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Now that we have a model it’s time to use it to perform data access. Update the Program.cs file with the code shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MigrationsAutomaticDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BlogContext())
            {
                db.Blogs.Add(new Blog { Name = "Another Blog " });
                db.SaveChanges();

                foreach (var blog in db.Blogs)
                {
                    Console.WriteLine(blog.Name);
                }
            }
        }
    }
}

Run your application and you will see that a MigrationsAutomaticDemo.BlogContext database is created on your local SQLEXPRESS instance.

Enabling Migrations

It’s time to make some more changes to our model.

Let’s introduce a Url property to the Blog class.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
}

If you were to run the application again you would get an InvalidOperationException because the database no longer matches your model.

”The model backing the 'BlogContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).”
.
As the exception suggests, it’s time to start using Code First Migrations. The first step is to enable migrations for our context. Because we want to use automatic migrations we’re going to specify the –EnableAutomaticMigrations switch.
- Run the ‘Enable-Migrations -EnableAutomaticMigrations’ command in Package Manager Console.
  .
This command has added a Migrations folder to our project. This new folder contains a Configuration class. This class allows you to configure how Migrations behaves for your context. For this walkthrough we will just use the default configuration.
Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type that this configuration applies to.

Your First Automatic Migration

Code First Migrations has two commands that you are going to become familiar with.

Add-Migration will scaffold the next migration based on changes you have made to your model.
Update-Database will apply any pending changes to the database.

We are going to avoid using Add-Migration (unless we really need to) and focus on letting Code First Migrations automatically calculate and apply the changes.

Let’s use Update-Database to get Code First Migrations to push the changes to our model (the new Blog.Url property) to the database.
- Run the ‘Update-Database’ command in Package Manager Console.
  .
Code First Migrations has now updated the MigrationsAutomaticDemo.BlogContext database to include the Url column in the Blogs table.

Our Second Automatic Migration

Let’s make another change and let Code First Migrations automatically push the changes to the database for us.

Let’s introduce a new Post class.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }    
    public string Url { get; set; } 
 
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    [MaxLength(200)]
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Now use Update-Database to bring the database up-to-date. This time let’s specify the –Verbose flag so that you can see the SQL that Code First Migrations is running.
- Run the ‘Update-Database –Verbose’ command in Package Manager Console.

Adding a Code Based Migration

Now let’s look at something we might want to use a code-based migration for.

Let’s add a Blog.Rating property.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; } 
    public int Rating { get; set; }

    public List<Post> Posts { get; set; }
}

We could just run Update-Database to push these changes to the database. However, were adding a non-nullable Blogs.Rating column, if there is any existing data in the table it will get assigned the CLR default of the data type for new column (Rating is integer, so that would be 0). But we want to specify a default value of 3 so that existing rows in the Blogs table will start with a decent rating.

Let’s use the Add-Migration command to write this change out to a code-based migration so that we can edit it. The Add-Migration command allows us to give these migrations a name, let’s just call ours AddBlogRating.
- Run the ‘Add-Migration AddBlogRating’ command in Package Manager Console.
  .
In the Migrations folder we now have a new AddBlogRating migration. The migration filename is pre-fixed with a timestamp to help with ordering. Let’s edit the generated code to specify a default value of 3 for Blog.Rating.

The migration also has a code-behind file that captures some metadata. This metadata will allow Code First Migrations to replicate the automatic migrations we performed before this code-based migration. This is important if another developer wants to run our migrations or when it’s time to deploy our application.
```
namespace MigrationsAutomaticDemo.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class AddBlogRating : DbMigration
    {
        public override void Up()
        {
            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }
        
        public override void Down()
        {
            DropColumn("Blogs", "Rating");
        }
    }
}
```
Our edited migration is looking pretty good, so let’s use Update-Database to bring the database up-to-date.
- Run the ‘Update-Database’ command in Package Manager Console.

Back to Automatic Migrations

We are now free to switch back to automatic migrations for our simpler changes. Code First Migrations will take care of performing the automatic and code-based migrations in the correct order based on the metadata it is storing in the code-behind file for each code-based migration.

Let’s add a Post.Abstract property to our model.

public class Post
{
    public int PostId { get; set; }
    [MaxLength(200)]
    public string Title { get; set; }
    public string Content { get; set; }
    public string Abstract { get; set; }     

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Let’s use Update-Database to get Code First Migrations to push this change to the database using an automatic migration.

Run the ‘Update-Database’ command in Package Manager Console.

Data Motion / Custom SQL

So far we have just looked at migration operations that can leave all the data in place, now let’s look at something that needs to move some data around. There is no native support for data motion yet, but we can run some arbitrary SQL commands at any point in our script.

We just added the Abstract column. Let’s pre-populate it for existing posts using text from the Content column.

Use the Add-Migration command to let Code First Migrations add an empty migration for us. We’re going to call this migration PopulatePostAbstract.
(The migration will be empty because there are no pending model changes that haven’t been applied to the database)
- Run the ‘Add-Migration PopulatePostAbstract’ command in Package Manager Console.

Update the migration to run some custom SQL that will populate the Abstract column.

namespace MigrationsAutomaticDemo.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class PopulatePostAbstract : DbMigration
    {
        public override void Up()
        {
            Sql("UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
        }
        
        public override void Down()
        {
        }
    }
}

Our edited migration looks good, so let’s use Update-Database to bring the database up-to-date. We’ll specify the –Verbose flag so that we can see the SQL being run against the database.
- Run the ‘Update-Database –Verbose’ command in Package Manager Console.

Migrate to a Specific Version (Including Downgrade)

So far we have always upgraded to the latest migration, but there may be times when you want upgrade/downgrade to a specific migration.

Let’s say we want to migrate our database to the state it was in after running our AddBlogRating migration. We can use the –TargetMigration switch to downgrade to this migration. This is going to cause some columns that were added as part of an automatic migration to be dropped automatically on the way down. Code First Migrations won’t let this happen without you knowing about it, so we need to specify the –Force switch to acknowledge that we are OK with the potential data loss.
- Run the ‘Update-Database –TargetMigration:"AddBlogRating" –Force’ command in Package Manager Console.
  .
This command will run the Down script for our PopulatePostAbstract migration, then use the automatic pipeline to revert the addition of the Abstract column.

If you want to roll all the way back to an empty database then you can use the Update-Database –TargetMigration:$InitialDatabase –Force command.

Getting a SQL Script

If another developer wants these changes on their machine they can just sync once we check our changes into source control. Once they have our new migrations they can just run the Update-Database command to have the changes applied locally. However if we want to push these changes out to a test server, and eventually production, we probably want a SQL script we can hand off to our DBA.

Let’s run the Update-Database command but this time we’ll specify the –Script flag so that changes are written to a script rather than applied. We want a script to go from an empty database ($InitialDatabase) to the latest version.

Note: You can also specify a target migration to generate a script to the database state at the end of a code-based migration. If you don’t specify a target migration, Migrations will use the latest version as the target (including any automatic migrations that have been applied since the last code-based migration).
- Run the ‘Update-Database -Script -SourceMigration:$InitialDatabase’ command in Package Manager Console.
  .
Code First Migrations will run the migration pipeline but instead of actually applying the changes it will write them out to a .sql file for you. Once the script is generated, it is opened for you in Visual Studio, ready for you to view or save.

Summary

In this walkthrough you saw how to use automatic migrations to push model changes to the database. You saw how to scaffold and run code-based migrations when you need more control. You also saw how to upgrade and downgrade your database. Finally we looked at how to get a SQL script to apply migrations to a database.

Rowan Miller
Program Manager
ADO.NET Entity Framework

ADO.NET Blog

Entity Framework Links #4

SQL Database Connectivity and the Idempotency Issue

EF6 Alpha 3 Available on NuGet

EF Power Tools Beta 3 Available