EF 4.3 Beta 1: Code-Based Migrations Walkthrough

Article
01/12/2012

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 Code First Migrations see https://msdn.com/data/jj591621

We have released the final preview of the Code First Migrations work as part of Entity Framework 4.3 Beta 1.

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 code-based workflow for using migrations. In this workflow each change is written out to a code-based migration that resides in your project.

There is a separate EF 4.3 Beta 1: Automatic Migrations Walkthrough that shows how this same set of changes can be applied using a mixture of code-based and automatic 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

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 MigrationsCodeDemo Console application.
.
Add the latest prerelease version of the EntityFramework NuGet package to the project.
- Tools –> Library Package Manager –> Package Manager Console.
- Run the ‘Install-Package EntityFramework –IncludePrerelease’ command.
  .

Add a Model.cs class 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 MigrationsCodeDemo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    }

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

Enabling Migrations

Now that we have a Code First model let’s enable Migrations to work with our context.

Run the ‘Enable-Migrations’ command in Package Manager Console.

.

This command has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class. The Configuration class allows you to configure how Migrations behaves for your context.

Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type in the base class and Seed method for you.

 namespace MigrationsCodeDemo.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<MigrationsCodeDemo.BlogContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(MigrationsCodeDemo.BlogContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

Our First 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 haven’t generated any migrations yet so this will be our initial migration that creates the first set of tables (in our case that’s just the Blogs table). We can call the Add-Migration command and Code First Migrations will scaffold a migration for us with its best guess at what we should do to bring the database up-to-date with the current model.

The Add-Migration command allows us to give these migrations a name, let’s just call ours MyFirstMigration.
- Run the ‘Add-Migration MyFirstMigration’ command in Package Manager Console.
  
  .

In the Migrations folder we now have a new MyFirstMigration migration. The migration is pre-fixed with a timestamp to help with ordering.

 namespace MigrationsCodeDemo.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class MyFirstMigration : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Blogs",
                c => new
                    {
                        BlogId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.BlogId);

        }

        public override void Down()
        {
            DropTable("Blogs");
        }
    }
}

We could now edit or add to this migration but everything looks pretty good. Let’s use Update-Database to apply this migration to the database.
- Run the ‘Update-Database’ command in Package Manager Console.
  
  .
Code First Migrations has now created a MigrationsCodeDemo.BlogContext database on our local SQL Express instance. We could now write code that uses our BlogContext to perform data access against this database.

Customizing Migrations

So far we’ve generated and run a migration without making any changes. Now let’s look at editing the code that gets generated by default.

It’s time to make some more changes to our model, let’s introduce a Blog.Rating property and a new Post class.

 public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }     
    public int Rating { 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; }
}

Let’s use the Add-Migration command to let Code First Migrations scaffold its best guess at the migration for us. We’re going to call this migration MySecondSetOfChanges.
- Run the ‘Add-Migration MySecondSetOfChanges’ command in Package Manager Console.
  
  .

Code First Migrations did a pretty good job of scaffolding these changes, but there are some things we might want to change:

First up, let’s add a unique index to Posts.Title column.

We’re also 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.

(These changes to the scaffolded migration are highlighted)

 namespace MigrationsCodeDemo.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class MySecondSetOfChanges : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Posts",
                c => new
                    {
                        PostId = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 200),
                        Content = c.String(),
                        BlogId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.PostId)
                .ForeignKey("Blogs", t => t.BlogId, cascadeDelete: true)
                .Index(t => t.BlogId)
                .Index(p => p.Title, unique: true);

            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }

        public override void Down()
        {
            DropIndex("Posts", new[] { "BlogId" });
            DropForeignKey("Posts", "BlogId", "Blogs");
            DropColumn("Blogs", "Rating");
            DropTable("Posts");
        }
    }
}

Our edited migration is looking pretty good, so let’s 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.

Data Motion / Custom SQL

So far we have just looked at migration operations that don’t change or move any data, 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.

Let’s add a Post.Abstract property to our model. Later, we’re going to pre-populate the Abstract for existing posts using some text from the start of the Content column.

 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 the Add-Migration command to let Code First Migrations scaffold its best guess at the migration for us. We’re going to call this migration AddPostAbstract.
- Run the ‘Add-Migration AddPostAbstract’ command in Package Manager Console.

The generated migration takes care of the schema changes but we also want to pre-populate the Abstract column using the first 100 characters of content for each post. We can do this by dropping down to SQL and running an UPDATE statement after the column is added.

 namespace MigrationsCodeDemo.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class AddPostAbstract : DbMigration
    {
        public override void Up()
        {
            AddColumn("Posts", "Abstract", c => c.String());

            Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
        }

        public override void Down()
        {
            DropColumn("Posts", "Abstract");
        }
    }
}

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 MyFirstMigration migration. We can use the –TargetMigration switch to downgrade to this migration.
- Run the ‘Update-Database –TargetMigration:"MyFirstMigration"’ command in Package Manager Console.

This command will run the Down script for our AddBlogAbstract and MySecondSetOfChanges migrations. If you want to roll all the way back to an empty database then you can use the Update-Database –TargetMigration:"0" command.

Getting a SQL Script

Now that we have performed a few iterations on our local database let’s look at applying those same changes to another database.

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.

Now 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’ll also specify a source and target migration to generate the script for. We want a script to go from an empty database (migration “0”) to the latest version (migration “AddPostAbstract”).

Note: If you don’t specify a target migration, Migrations will use the latest migration as the target.
- Run the ‘Update-Database -Script -SourceMigration:"0" -TargetMigration:"AddPostAbstract"’ 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.

NOTE: There are a number of bugs in the scripting functionality in EF 4.3 Beta1 that prevent you generating a script starting from a migration other than an empty database. These bugs will be fixed in the final RTM.

Summary

In this walkthrough you saw how to scaffold, edit and run code-based migrations to upgrade and downgrade your database. You also saw how to get a SQL script to apply migrations to a database.

Rowan Miller

Program Manager

ADO.NET Entity Framework

Comments

Anonymous
January 12, 2012
Great job guys !! BTW, Is there any way to know which migration I am at, without looking at database schema? if I upgrade and downgrade several times I or my colleagues would loose where we are at the moment.
Anonymous
January 13, 2012
@Ray, You can get a list of all the migrations that have been applied to the database using the Get-Migrations command.
Anonymous
January 13, 2012
Still hoping for a way to select data, update it in code, and then dump it back to the database. So for example instead of this: Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL"); We could run something like this: foreach (var row in Sql("SELECT * FROM Posts")) { Sql("UPDATE Posts SET Abstract = '" + GenerateAbstract(row) + "' WHERE PostId = " + row["PostId"]); } I'll keep harping on it until it happens. :) In the meantime, it might not hurt to provide some guidance for mixing selects and updates in a migration so that they all at least run in the same transaction. Looking forward to EF5 and enum support!
Anonymous
January 14, 2012
The comment has been removed
Anonymous
January 16, 2012
@Shawn – This is absolutely on our backlog, but it’s something we want to make sure we get right… and didn’t want to try and rush it into the first release of migrations. We gladly welcome your ‘harping’… it really helps us prioritize features :) Everything in a given migration runs in a transaction, including any raw SQL you specify. @Ben – Did you close and re-open Visual Studio? There are some assembly locking issues during upgrade that we are still trying to find a good solution too.
Anonymous
January 16, 2012
Hi, I have the same error as Ben does. Posted the problem on MSDN here: social.msdn.microsoft.com/.../d5056d61-8e2e-467c-b96e-801e9e7cbec9 I did the reopen VS procedure and later even tried to uninstall EF 4.3 and install EF 4.3 again then reopened VS but to no avail. FYI, my DbContext implementation is located in a different assembly than the project that has been "Migrations enabled", i.e. where my Configuration.cs i located. Could that cause some problem with the assembly reference locking? Any suggestions would be appreciated. Thanks.
Anonymous
January 16, 2012
thanks for listening from the community feedback! I'm happy to know that you guys do listen!
Anonymous
January 16, 2012
@Rowan - Apologies, I didn't see that step in the details - yes, it works now after closing and re-opening VS. @Mads - is it still broken for you?
Anonymous
January 16, 2012
The comment has been removed
Anonymous
January 17, 2012
Hi Great work. I would like to ask however, why AddOperation is marked internal in DbMigration. That way any custom extension to migration operations is not possible (without reflection). And maybe ModelDiffer should be configurable too. Right now it is also internal in DbMigrationsConfiguration. I think those are fairly important extension points.
Anonymous
January 18, 2012
Rowan, how about support for SQL Azure Federation? Do you have any plans to add an attribute to the table declaration to specify that it should be a federated table? Or maybe a generic attribute that could append any SQL string to the CREATE TABLE statement? This is important because the only moment you can declare that a table should be federated is when you create it.
Anonymous
January 18, 2012
@Fernando, Rowan did an excellent post titled "Customizing Code First Migrations Provider" on his blog at RoMiller.com. He walks you through how to extend the CreateIndex call to allow for index ordering. The same method could be used to extend CreateTable for federations.
Anonymous
January 18, 2012
@Fernando: I'll just add to what Brice already said that we are working closely with the SQL Azure Federations team and there are several things we are considering to get a great experience with EF and SQL Azure Federations. In EF 4.3 we already made a minor improvement to association conventions to handle composite key patterns that are very common in federated tables. An change to connection handling is scheduled for EF 5.0 that will allow you do control the connection lifetime with just context.Database.Connection.Open() (right now you need to use the connection on ObjectContext). We don't currently have a complete design for generating federated databases with Code First or Migrations but we think that the sort of attribute that you are suggesting is a very attractive option, in particular for specifying that a table has to be federated, or that a particular column is the federation scheme. In the meanwhile the recommendation is to create the databases manually and then you can use the Code First API to specify the mappings against an existing database (you can also use Code First to get an initial script and then modify it and run it in all your federation members if that helps). There are more details about this in a blog post we published recently: blogs.msdn.com/.../sql-azure-federations-and-the-entity-framework.aspx
Anonymous
January 22, 2012
@Brice, @Diego, thank you for the useful pointers. I'm looking forward to a more integrated experience between Entity Framework and SQL Azure Federations. I added the suggestion to create an attribute to the uservoice site: http://bit.ly/xZuYng
Anonymous
February 02, 2012
The comment has been removed
Anonymous
February 02, 2012
@moranlf, we've done some work to make this more resilient in the next release. Until then, you can generate a script in code using the MigratorScriptingDecorator class's ScriptUpdate method.
Anonymous
February 09, 2012
Is there no way of creating and executing migrations through my code? We are in the very early stages of the project and we have a lot of changes in the data base (code first). I would like to create a mechanism that will automatically upgrade the DB (if required) whenever our application starts, without using Nuget. is this possible?
Anonymous
February 10, 2012
@Idan – Yes, if you enable automatic migrations (in the ctor of the Configuration class in your Migrations folder) and then set the MigrateDatabaseToLatestVersion database initializer. You may also want to set AutomaticMigrationDataLossAllowed to true in the Configuration class, to allow columns/tables to be dropped.

Partager via