Code First Migrations: Beta 1 ‘No-Magic’ Walkthrough

Artigo
11/29/2011

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 fourth preview of our migrations story for Code First development; Code First Migrations Beta 1. This release includes a preview of the developer experience for incrementally evolving a database as your Code First model evolves over time.

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 ‘no-magic’ 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 Code First Migrations: Beta 1 ‘With-Magic’ Walkthrough that shows how this same set of changes can be applied by making use of automatic migrations.

This post assumes you have a basic understanding of the Code First functionality that is included in EF 4.2, 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 ‘Beta1Demo’ Console application
.
Add the EntityFramework NuGet package to the project
- Tools –> Library Package Manager –> Package Manager Console
- Run the ‘Install-Package EntityFramework’ 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.

Note that we are removing the IncludeMetadataConvention to get rid of that EdmMetadata table that Code First adds to our database. The EdmMetadata table is used by Code First to check if the current model is compatible with the database, which is redundant now that we have the ability to migrate our schema. It isn’t mandatory to remove this convention when using migrations, but one less magic table in our database is a good thing right!
```
 using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

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

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

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

Installing Migrations

Now that we have a Code First model let’s get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the ‘Install-Package EntityFramework.Migrations’ command in Package Manager Console
  
  .

The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. We’ll just edit the Configuration class to specify our BlogContext.

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

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

            // Seed data: 
            //   Override the Seed method in this class to add seed data.
            //    - The Seed method will be called after migrating to the latest version.
            //    - You can use the DbContext.AddOrUpdate() helper extension method to avoid creating
            //      duplicate seed data. E.g.
            //
            //          myContext.AddOrUpdate(c => c.FullName,
            //              new Customer { FullName = "Andrew Peters", CustomerNumber = 123 },
            //              new Customer { FullName = "Brice Lambson", CustomerNumber = 456 },
            //              new Customer { FullName = "Rowan Miller", CustomerNumber = 789 }
            //          );
            //
        }
    }
}

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 it’s 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 Beta1Demo.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 Beta1Demo.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 it’s 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 below:

 namespace Beta1Demo.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", "BlogId");
            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 it’s 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 Beta1Demo.Migrations
{
    using System.Data.Entity.Migrations;

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

            Sql("UPDATE dbo.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.

We’re just going to create a script to deploy to a second database on the local SQL Express instance. Add an App.config file to your project and include a ‘MySecondDatabase’ connection string.

 <?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySecondDatabase"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=AnotherDatabase;Trusted_Connection=True;"/>
  </connectionStrings>
</configuration>

Now let’s run the Update-Database command but this time we’ll specify the –TargetDatabase flag to use the connection string we just added to the configuration file. We’ll also specify the –Script flag so that changes are written to a script rather than applied.
- Run the ‘Update-Database –TargetDatabase:"MySecondDatabase" –Script’ 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 scaffold, edit and run code-based migrations to upgrade and downgrade your database. You also saw how to get a SQL script that represents the pending changes to a database.

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller

Program Manager

ADO.NET Entity Framework

Comments

Anonymous
November 30, 2011
Two questions:

is there a way to define a sequence in which those migrations should run, other than explicitly calling each migration separately by name?? E.g. is there an "Update-Database -ToVersion 2.5.0" or something that would run all migrations that haven't been run yet, up to a given version/label ??
How would EF Migrations know / find out if a given migration has been run already, or not?

Anonymous
November 30, 2011
I'm not a fan of data manipulation using sql. I understand that this feature will be useful, but I'd like to be able to perform operations on data in c# code as schema operations are performed in c# code. If the DbContext derived class would be available in the migration class, then shuffling data around would be as easy as using code first itself.
Anonymous
November 30, 2011
Not sure if this is by design, but I have found an issue. When I add some migrations, and do Update-Database (during development) to the latest version, then decide it's time to push this further. I run Update-Database -TargetMigration:"0" and then Update-Database -Script to get a script that I can then pass to my dba. The resulting script does not contain the sql for creating the __MigrationsHistory table (Update-Database -TargetMigration:"0" does not drop the table) so as a result, I have a list of sql statements containing an insert to the __MigrationsHistory table, which is not in the db. In short:

Create a migration 2.Update-Database 3.Update-Database -TargetMigration:"0" 4.Update-Database -Script -> no __MigrationsHistory creation statement

Anonymous
November 30, 2011
To answer your question Marc, ad 2) Migrations creates a table called __MigrationsHistory that stores a list of migrations that have been performed. That's how it knows if a migration has been executed or not. ad 1) If I'm not mistaking, the migrations are sorted by name and executed in that order, except that it's not actually the file name. When you add a migration you'll also see a .Designer file that stores some metadata. There is a string IMigrationMetadata.Id field in there, and it looks like that's the key by which the order is determined. So if you change just the file name the original order will be preserved. If you modify the Id metadata field, you will change the order, though this of course has it's implications...
Anonymous
December 01, 2011
I found some additional tricky bits. One is that Migrations force you to have a default constructor on the context class - which in case of using dependency injection with some additional parameters passed to the context class constructor becomes a bit tricky to use. Second thing is that if the context class is in a separate project than a startup project which holds the connection strings. So the case here is that I install the Migrations package in the .data project but if I want to use the -TargetDatabase param, I have to add an app.config file just for that purpose. I think it would be useful if it would also be possible to provide a connection string to the TargetDatabase parameter, not just the name of the connection string stored in the app/web.config
Anonymous
December 01, 2011
@Krzysiek S, reguarding the scripting of __MigrationsHistory. This is a known issue. Currently, the create table script is only written if it doesn't exist in the database. We are changing this so that it is always written on the first migration. By the way, you can generate the script for all your migrations without rolling back the database by using 'Update-Database -Script -SourceMigration:"0"' Regarding the app/web.config file. If you want to use a config file other than the default project's, add the -ConfigurationFile to point to the one that you would like connection strings read from. Note: relative paths are from default project's base directory.
Anonymous
December 01, 2011
The comment has been removed
Anonymous
December 03, 2011
I think Microsoft needs to re-brand their set of tools to C# on Rails. What a blatant rip off. But hey, that's Microsoft.
Anonymous
December 04, 2011
Personally, I'm grateful for that Microsoft has looked to Rails for inspiration on these tools.... no need to re-invent the wheel. The Rails APIs have been ground through a number of revisions, and MSFT has plenty on their hands re-doing this in a static typechecking context, so I absolutely don't mind standing on Rails' shoulder(s).
Anonymous
December 04, 2011
I don't like the Update-Database -Script option. It's too easy to forget the -Script, and then you have updated the production database if you have access to do so. I would prefer a Script-Database command instead.
Anonymous
December 07, 2011
Is there a way to name a unique constraint, much like I can name a foreign key? Why is it that it is possible to do .ForeignKey("SomeTable", t => t.SomeColumn, name: "FK_SomeTable_SomeOtherTable"), but I cannot do .Index(t => t.SomeColumn, unique: true, name: "UQ_SomeTable_SomeColumn"). I saw that there is an anonymousArguments property, is there any way to pass the name there?
Anonymous
December 07, 2011
I second Thomas (or one up him), by suggesting to actually add a parameter to the Update-Database command to actually make the update happen. That is the default behavior should only show what would happen or generate a script, not clobber your database. I could see many a person get in big trouble over this convenience.
Anonymous
December 13, 2011
Thanks Brice and Rowan. Great job on the migrations tool - love it so far.
Anonymous
December 13, 2011
I do have one more question - is it possible to use migrations with an existing application? I have an app in version 0.1 in which I haven't used migrations. Now I have a codebase for 0.2 and would like to generate a db diff script... I know that I can always go to the repo, fetch code for 0.1, generate a migration, fetch code for 0.2, copy the migration over, generate the next migration. But maybe there is a quicker way?
Anonymous
December 14, 2011
Hi, i noticed that you can seed the database through migrations, but the way that you guys recomend this, is writting those seeds at the configuration.cs, that works fine but everytime I do Update-Database, those seeds are going to populate my DB, is there is a way that those seeds populate the database only once, maybe implementing the seeds at the migrations or something like that?
Anonymous
December 14, 2011
EF has the initializers that I'm able to configure in .config file and change depending on the environment that the app is run on. Is there anything similar to this in migrations? I'd like to seed the test db with dummy data, but the integration db with only the required stuff
Anonymous
December 15, 2011
@Krzysiek S, If you are using the runtime DbMigrator class to setup your test database, then you could have a separate MigrationsConfiguration with a different Seed method. Otherwise, you could run different seed logic from the one Seed method by examining which database is currently being targetted. Take a look at the properties available on DbMigrationsConfiguration like ConnectionStringName etc.
Anonymous
December 15, 2011
I didn't know about the DbMigrator class, thanks. I think that I can use this with some DI to get the same functionality. Though I must say that it is a really nice feature in code first that I could just change the config value and not having to worry about anything else
Anonymous
December 20, 2011
I wonder what the ideal way to work with (Migration) when working with a (database) existing. I'm deleting the (Up and Down) generated by the first (Add-migration) and giving the next update just starting changes.
Anonymous
January 03, 2012
I love migrations. You guys are doing a great job. I want to echo Leandro's question: if you have an existing database with existing data, how do you do the initial migration? It seems like you must wipe the database to get the first migration.
Anonymous
January 11, 2012
The comment has been removed
Anonymous
February 07, 2012
It is worth putting up a notice at the top of this article that EF 4.3 Beta 1 is released and link to that article: blogs.msdn.com/.../ef-4-3-beta-1-released.aspx

Partilhar via