Code First Migrations: Alpha 2 Walkthrough

Article
09/06/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 second preview of our migrations story for Code First development; Code First Migrations Alpha 2. 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. This post assumes you have a basic understanding of the Code First functionality that was included in EF 4.1, if you are not familiar with Code First then please complete the Code First Walkthrough.

Note: If you have received the following error when trying to use Code First Migrations Alpha 2 it is most likely due to bugs that were causing a dependency on SQL Compact and a dependency on a local SQL Express instance. We have released an updated package that resolves both these issues, please ensure you have updated the EntityFramework.Migrations package to version 0.6.2. You will need to close and re-open Visual Studio after updating the package, failure to do so will result in an error stating that “The project 'Demo' does not contain any migration contexts”. If you continue to get errors after updating to the latest package and re-starting Visual Studio then please start a thread in our Pre-Release Forum.

Add-Migration : Exception has been thrown by the target of an invocation.
At line:1 char:14
+ Add-Migration <<<< Init2
+ CategoryInfo : NotSpecified: (:) [Add-Migration], TargetInvocationException
+ FullyQualifiedErrorId : System.Reflection.TargetInvocationException,System.Data.Entity.Migrations.AddMigrationCommand

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 ‘Demo’ 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 Demo
{
    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 Settings class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. The Settings class also exposes the provider model for code generation and SQL generation. We’ll come back to these settings a little later on, for the moment we’ll just make edit the setting class to specify our BlogContext (highlighted below).

 using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = false;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}

Scaffolding & Running Migrations

We’re going to start by looking at the purely imperative workflow (that’s where every migration operation is explicitly defined in code). This workflow gives you the most control over the migration process and involves the least magic.

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. Update-Database currently only support migrating your database ‘up’ to the latest version. Migrating ‘up’ and ‘down’ to any version will be supported in the future.

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 being the database up-to-date with the current model. Once it has calculated what needs to change in the database, Code First Migrations will use the CSharpMigrationCodeGenerator that was configured in our Settings class to create the migration. The Add-Migrationcommand 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. There is also a code behind file that stores some metadata about the migration, we won’t cover that just yet but you can take a look if you want. Let’s turn our attention to the code that Code First Migrations has scaffolded and opened for us.

 namespace Demo.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()
        {
            // This preview of Code First Migrations does not support downgrade functionality.
        }
    }
}


 .

We could now edit or add to this migration but in this case 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 Demo.BlogContext database on our local SQL Express instance. We could now write code that uses our BlogContext to perform data access against this database.

.

It’s time to make some more changes to our model. Let’s change Blog.Name to have a maximum length of 100 and add in a Blog.Rating property. We’re also going to introduce the Post class at this time.

 public class Blog
{
    public int BlogId { get; set; }
    [MaxLength(100)]
    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-Migrationcommand 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 can do this inline with the CreateTable call.
We’re also adding a non-nullable Blogs.Rating column, if there is any existing data in the table this will fail because it will try to use null for the value of this new column. Let’s specify a default value of 3 so that existing rows in the Blogs table will get assigned a value.

Finally, let’s also add a non-unique index to the Blogs.Name column.

These changes to the scaffolded migration are highlighted below:

 namespace Demo.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)
                .Index(p => p.Title, unique: true);

            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
            ChangeColumn("Blogs", "Name", c => c.String(maxLength: 100));
            CreateIndex("Blogs", new[] { "Name" });
        }

        public override void Down()
        {
            // This preview of Code First Migrations does not support downgrade functionality. 
        }
    }
}

.

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 –Verboseflag so that you can see the SQL that Code First Migrations is running.
- Run the ‘Update-Database –Verbose’ command in Package Manager Console
  
  .
Code First Migrations has now updated the schema of our Demo.BlogContext database.

Automatic Migrations

So far you have seen how to scaffold, customize and run migrations. For a lot of folks this will be the complete workflow you use with Code First Migrations. There is another piece that you can opt into though, and that is to have some parts of the migration process happen without needing to have a migration file in your project. This is known as automatic migrations.

Automatic migrations can be combined with the imperative workflow we looked at in the last section. Code First Migrations uses that metadata it stored in the code behind file of each migration to replicate any automatic migrations you performed in-between each code-based migration.

Let’s go ahead and add a Blog.Url property:

 public class Blog
{
    public int BlogId { get; set; }
    [MaxLength(100)]
    public string Name { get; set; }
    public int Rating { get; set; }
    public string Url { get; set; }

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

.

We could write this change to a migration file and then execute it, but at this stage we might be feeling pretty comfortable with how Code First Migrations handles column additions. Let’s just try and issue an Update-Databasecommand.
- Run the ‘Update-Database’ command in Package Manager Console
  
  .
The update process gives us a warning letting us know that there are pending model changes that could not be applied to the database:

There are pending model changes but automatic migration is disabled. Either write these changes to a migration script or enable automatic migration. To add a migration script with the pending changes use the Add-Migration command. To use automatic migrations edit the settings class in the Migrations directory to enable automatic migrations then re-run the Update-Database command.

Let’s go ahead and enable automatic migrations in the Settings class:

 using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = true;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}

.

Now let’s try and re-run the update process with automatic migrations enabled.
- Run the ‘Update-Database’ command in Package Manager Console
  
  .
This time the update process succeeds and the Url column is added to the Blogs table. If we now made additional changes we could either continue to use automatic migrations or write those changes out to a code-based migration file. If we opt to use a code-based migration for the next set of changes then Code First Migrations will still replicate this same automatic addition of the Url column between the two code-based migrations when upgrading another database instance. It uses the metadata from the code behind files either side of the automatic addition to ensure the automatic changes are applied in the correct order.

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. In this preview we need to generate a script by pointing to a database to migrate, but in the future you will be able to generate a script between two named versions without pointing to a database.

Getting a script is also an easy way to preview what Code First Migrations is going to do. This is especially useful when using automatic migrations.
1. We’re just going to simulate deploying to a second database on the local SQL Express instance. We can change the database that migrations is targeting in the Settings class. We’re still working through how to handle multiple target databases, so this is an area that will change in future releases.
```
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = true;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();
            TargetDatabase(@"Server=.\SQLEXPRESS;Database=BloggingTest;Trusted_Connection=True");

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}
```
2. 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 just applied.
  - Run the ‘Update-Database –Script’ command in Package Manager Console
    
    .
3. Code First Migrations will run the update database 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 and run code-based migrations. You saw that parts of the migration process can be left up to automatic migrations and that code-based and automatic migrations can be intermingled. 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
September 07, 2011
how do I migrate my data as part of the migration, for example if I am moving data from one table to another?
Anonymous
September 08, 2011
The comment has been removed
Anonymous
September 08, 2011
Hi, Thanks for the quick reply on the above issue. I still keep getting the same issue even after installing the latest version 0.6.1.0. Please help..! Devi.
Anonymous
September 08, 2011
The comment has been removed
Anonymous
September 09, 2011
@Rowan, Please do improve data migration. I shouldn't have to know anything about SQL in order to work with code first - frankly I don't care what sort of database that it is stored in - it should be transparent to me. I should be able to use my existing classes and transfer the data across. ...Stefan
Anonymous
September 10, 2011
I think that database connections are being kept open after a migration is run. I tested the TargetDatabase method by specifying a new database name. After running Update-Database, the new database was created as expected, but I was not able to delete the database from SSMS until I shutdown VS.
Anonymous
September 12, 2011
@Stefan - Good feedback, thank you. @zl1 - Yes, we'll take a look at fixing this, thanks!
Anonymous
September 12, 2011
The comment has been removed
Anonymous
September 12, 2011
@Sam Striano - For anyone else hitting this issue, the related forum thread is: social.msdn.microsoft.com/.../7fab9bf4-c453-4ee5-a132-7c2c408f7166
Anonymous
September 13, 2011
@Sam Striano - We have released an updated package with a bug fix for this issue (see the note at the top of this post).
Anonymous
September 14, 2011
This looks much better than the previous release, definitely going in the right direction now. Is there any way to seed data using the migration framework. I'd like to see samples where you seed data as you migrate. This is helpful when building lookup tables.
Anonymous
September 15, 2011
It seems that ON DELETE CASCADE is the default, but yet when you set up a WillCascadeOnDelete(false) it does not obey this? Am I missing something? When using a DB initializer, I am getting a much different schema created.
Anonymous
September 17, 2011
So do EntityTypeConfigurations not work with Migrations? It seems as though Migrations applies HasMaxLength and IsRequired, but not things like WillCascadeOnDelete(false). How do you configure ON DELETE CASCADE? Do you have to do it in SSMS or modify the script Updata-Database -Script puts out? Are there plans in the future to support the fluent api fully? I am a little confused!
Anonymous
September 19, 2011
Is the EF Migrations supposed to work with EF 4.2 Beta 1? I kept getting an error with 4.2 Beta 1, and after I changed back to 4.1 Refresh 1, it worked.

Share via

Code First Migrations: Alpha 2 Walkthrough

Building an Initial Model

Installing Migrations

Scaffolding & Running Migrations

Automatic Migrations

Getting a SQL Script

Summary

Comments

Additional resources