EF 4.3 Code-Based Migrations Walkthrough

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

There is a separate 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 & 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 MigrationsCodeDemo 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 MigrationsCodeDemo
{
    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 MigrationsCodeDemo
{
    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 MigrationsCodeDemo.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 ( https://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.
- Run the ‘Enable-Migrations’ command in Package Manager Console.
  
  . .
This command has added a Migrations folder to our project. This new folder contains two files:
- The 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.
- An InitialCreate migration. This migration was generated because we already had Code First create a database for us, before we enabled migrations. The code in this scaffolded migration represents the objects that have already been created in the database. In our case that is the Blog table with a BlogId and Name columns.

Your 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 need to scaffold a migration to take care of the new Url property we have added. The Add-Migration command allows us to give these migrations a name, let’s just call ours AddBlogUrl.
- Run the ‘Add-Migration AddBlogUrl’ command in Package Manager Console.
  
  .

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

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

    public partial class AddBlogUrl : DbMigration
    {
        public override void Up()
        {
            AddColumn("Blogs", "Url", c => c.String());
        }

        public override void Down()
        {
            DropColumn("Blogs", "Url");
        }
    }
}

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 updated the MigrationsCodeDemo.BlogContext database to include the Url column in the Blogs table.

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

This command will run the Down script for our AddBlogAbstract and AddPostClass migrations.

If you want to roll all the way back to an empty database then you can use the Update-Database –TargetMigration:$InitialDatabase 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’ll also specify a source and target migration to generate the script for. We want a script to go from an empty database ($InitialDatabase) 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:$InitialDatabase -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.

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
February 09, 2012
Great step-by-step instruction!
Anonymous
February 09, 2012
You mentioned in the overview post that Update-Database.exe was renamed to migrate.exe, but you use Update-Database in this post. Did this just get missed when updating the blog post to 4.3 go-live, or have you created a PowerShell alias that allows Update-Database to work as well?
Anonymous
February 09, 2012
The comment has been removed
Anonymous
February 09, 2012
@Rowan - Ah, makes sense. Thanks!
Anonymous
February 10, 2012
This is so cool! I'm already trying to envision how this can be used beyond development and into the implementation phase wherein you often have a modular roll out. A year from now all of this work will be considered an indispensible part of the dev process. Great work! Also the instructions are very precise and so accurate that you may want to update your section "Customizing Migrations", so users also know to add "public DbSet<Post> Posts { get; set; }" to the BlogContext when they add the Post class.
Anonymous
February 10, 2012
Thanks for this post, the instructions are very clear. However I have a question: this Update-Database command is great when you're working in Visual Studio, but it won't be available when you need to update an already deployed database on a user machine (e.g. when the user upgrades the application to a newer version). Is there a way to invoke the migration purely from code? Perhaps with an IDatabaseInitializer ?
Anonymous
February 10, 2012
@gahayden – Thanks! You actually don’t have to add the Post set, because Post is referenced from Blog Code First will include it in the model without a DbSet.
Anonymous
February 10, 2012
@Thomas Levesque – There is a MigrateDatabaseToLatestVersion initializer included in 4.3 (System.Data.Entity namespace). This post has details about invoking migrations from custom code; romiller.com/.../running-scripting-migrations-from-code
Anonymous
February 10, 2012
How do I create a new "starting point"?
Anonymous
February 10, 2012
Great job, and great to use the System.Data.Entity, romiller.com/.../running-scripting-migrations-from-code
Anonymous
February 11, 2012
I can't get this to work on a DbContext in a seperate project: Steps:

Define model
Add EF trough NuGet
Enable-Migrations (ERROR, cannot find connection string)
I create App.config with correct connectionstring
Enable-Migrations -Force
migration dir created. InitialCreate is created.
I edit my model
Add-Migration SomeEdit I keep getting the following error: PM> Add-Migration cmdlet Add-Migration at command pipeline position 1 Supply values for the following parameters: Name: First System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) --- End of inner exception stack trace --- at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Management.Automation.ComMethod.InvokeMethod(PSMethod method, Object[] arguments) Exception has been thrown by the target of an invocation.

Anonymous
February 11, 2012
Ok, so I found out you need to add '-StartupProject "The DbContext project"' to the Add-Migration command. Why can't it figure this out by itself (I selected the correct project in Package Manager Console).
Anonymous
February 12, 2012
Excellent work! One quick question: Prior to Migration, I seeded the database. So I moved that code to Configuration.cs in Seed method. Ran it. Everything looks perfect! Good job. My web site works perfectly on local. However, when I run the "Update-Database -Script -SourceMigration:$InitialDatabase" my seed data is not in the SQL script. The code for my seed data uses CsvHelper to import data from csv files and runs through some iterations to correctly have data required for the website to function. I wouldn't want to rewrite everything into Transact SQL. <strong>Do I have to?</strong> Or, please tell me there is an "Update-Database -Script -SourceMigration:$InitialDatabase -Seed". Thanks and fantastic work folks, Sean
Anonymous
February 12, 2012
My seed code uses "context.People.Add(...)" instead of "context.People.AddOrUpdate(...)". Is this my problem?
Anonymous
February 13, 2012
This is a very good feature, i was waiting for it! Next one? Please let us add our Conventions!!!
Anonymous
February 13, 2012
@Vinicius de Melo Rocha – I’m not sure what you mean by a “new starting point”, can you start up a thread with some more info in the forum and we’ll answer the question there - social.msdn.microsoft.com/.../threads @Remco Ros - PROSCAT – There are two projects that migrations needs to be aware of, the project that contains the migrations configuration etc. and the one that contains the config file etc. If these two projects are different then you need to use –StartupProject to let migrations know which project contains the config. If you run into any issues with this we’d be more than happy to help you out in the forum, social.msdn.microsoft.com/.../threads. @Sean Stenlund - Unfortunately we don’t script seed data at this stage. It is on our backlog but didn’t make it into 4.3. If you use Add then a new entry will get added every time you migrate to the latest version. AddOrUpdate will take care of detecting if the entry already exists and Inserting or Updating the record. @Vincenzo – Pluggable conventions is a very common request we are hearing now. We don’t have plans on a particular release to include it in yet but we do plan to implement them. You can vote on features here; http://ef.mswish.net. We use that site (along with other sources) when it comes to prioritizing features.
Anonymous
February 13, 2012
Why does Update-Database require totally unrelated projects in the same solution to build before it can apply changes? Cheers, Lari
Anonymous
February 14, 2012
Hi, I am having a problem getting my existing poco classes back into sync with my existing db tables... There aren't too many differences... A few new properties... A new class or 2... It should only result in a the addition of those properties and a couple classes into the db... however it wants to add ALL tables like there is nothing there ?? What should I look for in my code to determine why its acting as if the db does not exist ? Add-Migration mig-1 -ConnectionStringName MeyerREContext -Force Add-Migration on db with very little differences to the classes results in a create table for all tables ?What do I need to do to have ONLY the differences between the classes and the database reflected in the generated script ? How Can I debug what is actually happening here ? It acts like there are NO tables at all in the db, but it is targeting the correct connection string when I use the verbose parameter ? Thanks Greg
Anonymous
February 14, 2012
@Lari Tuomisto – Update-Database currently just builds the solution. Your question is good feedback, I’ve added an item to our backlog to look at just building the required project(s). @Greg Foote – Was your database created using Code First Migrations, or is it an existing database? Migrations stores some metadata about your model in the database as it creates and changes it over time. If your targeting a database that was created outside of Migrations this metadata isn’t present.
Anonymous
February 14, 2012
@Rowan Miller It was created with Code First, but the migrations got out of sync and changes were made to keep the project going manually... There were some errors with the update datbase scripts generated by code first that resulted in errors when the scripts were run against the db... Alot things that require targeting constraint names seem to fail often... So what happended is that We had to keep going to keep the project moving... so we made the changes manually.. Now I want to get them back in sync...
Anonymous
February 14, 2012
Is it intended that Add-Migration generates code based on how the model differs from the database in the time of calling Add-Migration? Wouldn't it make more sense to generate the code based on what is the difference of the database between the previous migration (if such a thing is available, and I assume there always is, after calling Enable-Migrations) and the migration that is being added? The developer is almost certainly going to always have the latest database when they would like to generate the migration, now though, they have to revert their database to the previous migration, then call Add-Migration and then Update-Database again - which is fine, but it should also be made blatantly clear in the documentation that this is how it works. Just giving feedback, not sure this is the right place for it :) Thanks, Lari
Anonymous
February 15, 2012
@Greg Foote – OK, that makes sense. You should use Add-Migration and then just comment out or delete the code for the changes you have already applied to the database. Because migrations will look at changes between the last migration and the current model (rather than differences with the database) it won’t keep generating these changes in future Add-Migration calls. I would recommend commenting out the code and then un-commenting it once you’ve applied the migration so that if another developer runs the migrations to create a new database they will get the changes that you have hand coded to the database. @Lari Tuomisto - Add-Migration looks at the changes to the model since you last called Add-Migration (rather than the differences between the model and the current database). You shouldn't need to revert to the previous migration in order to generate the next one. If you have a scenario where you need to do this can you start up a thread in the forum and we'll work out what the issue is; social.msdn.microsoft.com/.../threads
Anonymous
February 15, 2012
Hi, I had an issue when trying to add-migration, turns out it doesn't like the configsource in a web.config, once I had removed that worked first time. This may be by design, but thought I would mention it here in case anybody had the same issue, may this will be fixed in a later version.
Anonymous
February 15, 2012
Hello, I have a problem with TPC, When i do my migration, it's removing field from my abstract class
Anonymous
February 16, 2012
Can anyone offer any guidance on enabling migrations on an existing database (pre 4.3)?
Anonymous
February 16, 2012
@Duncan Faulkner - A few folks have raised this issue, we have checked in a fix that will become available shortly in 4.3.1 patch release. @JasonBarrera – If the database was created by Code First then you can just use the Update-Database command to setup the database for migrations (your model will need to be in the same state it was when the database was created… the command will give you an error if it’s not). If the database wasn’t created with migrations then we are looking adding a command in the 4.3.1 patch release to enable migrations. In the meantime Julie Lerman has posted about a good approach to get you going; thedatafarm.com/.../using-ef-migrations-with-an-existing-database.
Anonymous
February 16, 2012
@Benjamin Hugot - There is a TPC related bug in 4.3 that will be fixed in the 4.3.1 patch, coming out shortly. Feel free to start up a thread on the forum so we can confirm this is the same issue; social.msdn.microsoft.com/.../threads
Anonymous
February 16, 2012
To get this walkthrough to work with Sql Server Compact:

Run 'Install-Package SqlServerCompact'
Add the below lines to your config file before 'Enable-Migrations' <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework"> <parameters> <parameter value="System.Data.SqlServerCe.4.0" /> </parameters> </defaultConnectionFactory> </entityFramework>

Anonymous
February 16, 2012
Is it possible to customize the generated SQL by Update-Database -Script -SourceMigration:$InitialDatabase with a T4 template ?
Anonymous
February 19, 2012
This borrows heavily from Migrator.NET (code.google.com/.../migratordotnet) - was this intentional?
Anonymous
February 20, 2012
@Paul Marshall, This general pattern has been around for a while and was actually popularized by Rails. Customer feedback (and our own experience) told us that we should not try and reinvent the wheel here, but rather stick to the well-known, proven approach. Our goal was to leverage this approach to create a sweet, integrated experience for the EF/Code First user. This is why we have things like scaffolding, automatic migrations and nice VS integration etc. Cheers, Andrew.
Anonymous
February 22, 2012
Just wondering how you refactor tables, as this looks like you don't have the ability to move 1/2 of the data to a new table, and then create a join between the table(s) and drop the duplicate records, which we have had to do.
Anonymous
February 23, 2012
Ok I'm getting: >CREATE DATABASE permission denied in database 'master'. This is immediately after step #4. I tried reworking your examples for SQL CE, no luck. When I tried supplying a connection string into the DbContext then I get "dbo.Blogs" not found? Would really appreciate some help here.
Anonymous
February 23, 2012
@gideonisrael: This happens typically when the user account you are using does not in fact have permissions to create databases in that particular instance of SQL Server. If this is the case, there is no much Code First or Migrations can do. A possible solution is to use SQL Server Management Studio or Visual Studio to assign the user login the dbcreator role in SQL Server security. You should do this while you "run as administrator" or with the user account originally employed to install SQL Server. In order to use SQL CE with Migrations you should specify the default connection factory as indicated in this blog post: blogs.msdn.com/.../ef-4-3-configuration-file-settings.aspx Hope this helps, Diego
Anonymous
February 24, 2012
Is there any way to use several Code First Data Contexts and several (different) Migrations in one database? We have one database shared by several applications, objects are separated by schema. So I'd like to be able to store and apply migrations for one schema independently from another one.
Anonymous
February 24, 2012
(or if we look other way around) Will (should) the be any issues if I have two applications, each one with its own migrations, looking at same database (and thus - same __MigrationHistory)?
Anonymous
February 28, 2012
In Code First Migrations beta I was able to specify a -TargetDatabase flag for Update-Database in order to specify which database I wanted the migration to apply to. In EF 4.3 Migrations however I get an error that the parameter is unknown. Has the -TargetDatabase flag been renamed, or how can I specify which database the migration should apply to? See my complete question on StackOverflow: stackoverflow.com/.../678801
Anonymous
February 28, 2012
Found the answer to my previous comment. Instead of using the -TargetDatabase flag, you can specify the -ConnectionStringName flag to point the migration towards another database, specified in the config-file.
Anonymous
February 29, 2012
The comment has been removed
Anonymous
February 29, 2012
I can not use this feature. After executing the command "Enable-Migrations" class configuration is created successfully, but no class "InitialCreate" is created. Also, use the method "protected override void Seed" to load some initial data, this method is never executed. Any tips?
Anonymous
March 01, 2012
@Riderman InitialCreate is only created when the database was originally created using a DbContext initializer. The Seed method will be called when you migrate to the latest version via Update-Database. Cheers, Andrew.
Anonymous
March 01, 2012
The comment has been removed
Anonymous
March 07, 2012
Let's say i have 100's of databases that use the same structure. How could i dynamicly update those databases using Migrations ? I'm already using Entity Framework dynamicly (overriding my Database.Connection.ConnectionString) by setting my connectionstring based on the current URL (that get's fetched from one central database).
Anonymous
March 07, 2012
@NicoJuicy, See this post: romiller.com/.../running-scripting-migrations-from-code about how to use the runtime APIs. Cheers, Andrew.
Anonymous
March 11, 2012
Heya, It would appear there is a problem with how the DBContext is handled in the Seed-method. Having more than one context.SaveChanges() inside the seed causes some erratic behaviour with how the context gets disposed after the save. There are cases, where it would be ideal to be able to add something to the database, before something else can be added For example this code: AddSystemRoles(context); context.SaveChanges(); AddSystemUsers(context); context.SaveChanges(); Causes Update-Database to fail on the first try, and when you do Update-Database again, it will just randomly work. It appears, some magic is being done with the disposing of the context, which causes this behaviour. Not sure if it's a bug or just me using it wrong. Cheers, Lari Tuomisto
Anonymous
March 25, 2012
Managing changes to production databases... I checked out Julie Lerman's Pluralsight videoes on EF migrations. Very good. One area that still looks like it needs some additional work though is the generation and management of scripts for updating production databases. The ability to generate scripts specifying source/target migrations looks useful but it would be nice if these were written in such a way as to be replayable and/or reversible. e.g. from the video, it doesn't appear that the script checks to see if a migration has already been applied before applying it. Nor does it look like there is the option to rollback a script (the down feature). Has anyone come up with an approach to using Sql Server Data Tools to manage the EF migration scripts on a production database? Thanks Martin
Anonymous
March 27, 2012
The comment has been removed
Anonymous
April 05, 2012
The comment has been removed
Anonymous
April 05, 2012
ok, seems that I found how to use MyDbContextFactory. As your previous blog post said, "If your context does not expose a default constructor then an implementation if IDbContextFactory should be included in the same assembly as your derived context type." May I know why must I to put MyDbContextFactory into the same assembly as MyDbContext? I expected that there will be a property in DbMigrationsConfiguration which allows me to set an IDbContextFactory object. Here is MyDbContextFactory public class MyDbContextFactory : IDbContextFactory<MyDbContext> { public MyDbContext Create() { return ServiceLocator.Current.GetInstance<MyDbContext>(); } } When I ran Add-Migration, a NullReferenceException occurred as ServiceLocator was not initlized yet.
Anonymous
April 05, 2012
@jiaxingseng - The context factory is used by more than just migrations, it's designed to be a general purpose way for any tooling to work out how to construct your context. For example, Beta 2 of the EF Power Tools (coming out shortly) also use this mechanism. The right click -> publish functionality in ASP.NET projects use it as well.
Anonymous
April 05, 2012
@jiaxingseng Can you bootstrap your IoC container from your context factory? Alternatively, can you avoid using the container at design time and just manually create your context? Cheers, Andrew.
Anonymous
April 14, 2012
Thank to ADO.NET Develpment Team for so great technology as DbMigration is! I encountered with some interest issue, can you provide any comment? social.msdn.microsoft.com/.../0988155e-3162-41f6-8aa2-fe693451576f
Anonymous
April 16, 2012
@Deren' Andriy - I've replied to the forum thread.
Anonymous
April 16, 2012
Hi, Can you show to Downgrade multiple columns in multiple tables. Thanks Bhanu Prakash
Anonymous
April 17, 2012
@Bhanu Prakash - I'm not sure exactly what you mean by 'Downgrade multiple columns in multiple tables' but if you make changes to multiple columns everything just works the same as shown in this walkthrough. Migrations will scaffold code for all the changes in a single file and you can upgrade/downgrade in the same way.
Anonymous
April 18, 2012
Hi, I'm running in a weird problem: basically I'm stuck at point 5. DB is created, but tables are not, both with SQL Server Express 2005 and SQL Compact. I wrote a detailed explanation here: stackoverflow.com/.../entity-framework-4-3-1-code-first-database-created-but-tables-are-not
Anonymous
April 19, 2012
@Simone Chiaretta - Someone from our team will follow up on your Stack Overflow thread.
Anonymous
April 22, 2012
@Rowan Miller, tnx for reply
Anonymous
April 22, 2012
Hi. If I use MigrateDatabaseToLatestVersion initializer, s there any way to get SQL script which is used to update database?
Anonymous
April 22, 2012
@Rowan: contacted the team via contact form, but using SQL Profiler I found out that the string used to represent the migration date is not in the right format, at least in my locale. I executed the same statements manually via query analyzer and still failed. When changing the format of the datatime string from 2012-04-23T14.16.59.038Z to 2012-04-23T14:16:59.038Z the command went through, so I guess somehow EF is sending the datatime in format that is not compatible with my locale. Any idea how to fix this?
Anonymous
April 23, 2012
@John Smith - You would need to derive from MigrateDatabaseToLatestVersion and insert logic to write out the script. There is a section about getting scripts from code in this post: romiller.com/.../running-scripting-migrations-from-code
Anonymous
April 23, 2012
@Simone Chiaretta - One of our team members is working on the issue at the moment.
Anonymous
April 26, 2012
@Rowan: any news/update/workaround on the problem?
Anonymous
April 30, 2012
@Simone - Yes, sorry not to get back to you. It is a bug in our code base related to non-English locales...it will be fixed in the EF5 RTM release, which will be out shortly.
Anonymous
May 28, 2012
After Enabling Migrations in ASP.NET MVC4 (visual studio 11) , model classes are not showing up in adding new view or controller dialog . but migrations and project running normal. but after deleting configuration class file in migration folder , it showing up.
Anonymous
May 29, 2012
@Vijayant Katyal - Make sure you build the project before scaffolding a controller. If you are still seeing the issue would you be able to send me a project that reproduces is? If so, you can use the 'Email Blog Author' link at the top right of the page and I'll reply with an email to send the project to.
Anonymous
May 30, 2012
Rowan, I'm having difficulty getting Entity Framework to recognize my previous migrations if I change the namespace that the Configuration class lives under. I put full steps to reproduce on this stack overflow question, but I have a feeling that you may be the only person who can answer this: stackoverflow.com/.../is-it-possible-to-get-entity-framework-to-recognize-previous-migrations-if-you-c Note that I want to be able to actually change the namespace for all migrations, and not just use the MigrationsNamespace property of the DbMigrationsConfiguration class (which doesn't let me change the namespace).
Anonymous
May 30, 2012
Ignore my last post. It turned out I didn't really change all of the namespaces when I did the refactor (I forgot the designer files, so entity framework thought my migrations didn't exist).
Anonymous
June 01, 2012
The comment has been removed
Anonymous
June 07, 2012
The comment has been removed
Anonymous
June 08, 2012
@Joe Waldschmidt - I've replied to your Stack Overflow question.
Anonymous
June 14, 2012
The comment has been removed
Anonymous
June 14, 2012
The comment has been removed
Anonymous
June 14, 2012
Okay thanks for confirming
Anonymous
June 14, 2012
@Rowan - There appears to be a bug in the Add-Migration code. If you have a field like public string Genre { get; set; } and you change both the name using the ColumnAttribute and the data type, such as [Column("GenreId")] public GenreEnum Genre { get; set; } it will generate the name change as a RenameColumn statement but not the data type change. Does the same if you change it to int. It should add an AlterColumn statement as well.
Anonymous
June 20, 2012
@John Perceval - Thank you for reporting this, it is a bug and I've added it to our backlog to fix. It won't be fixed in EF5 so for the moment the workaround would be to make the changes in separate steps, or manually add the AlterColumn call into the migration that is scaffolded.
Anonymous
June 24, 2012
Brilliant, i am very new to Entity Framework, but after reading this i feel like i have been doing migrations since my first breath or air. Excellent work.
Anonymous
June 25, 2012
All this stuff is pretty cool, but please spend some time to improve the documentation. There used to be a time where the documentation explains all features so it easy to discover and work out what features are useful. MSDN page msdn.microsoft.com/.../hh770484(VS.103).aspx just links back to this post. For example, where is the easy to find/reference documentation of the PowerShell scripts? Why is it that now I have to spend hours to search and read through tons of comments in a blog post to find the information? It feels like I'm using an OpenSource library, without the source code to work out what to do. And what if I'm not following the blog and suddenly needed to use this framework, I then have to determine which blog contains the official information, then search and read through all historic posts to find the information. Please Microsoft, please invest sometime in producing proper documentation.
Anonymous
June 27, 2012
@Ed - We are working on a bunch of documentation at the moment that will be ready alongside the EF5 RTM (which will be when Visual Studio 2012 reaches RTM).
Anonymous
July 12, 2012
The comment has been removed
Anonymous
July 13, 2012
@RogerH - You should be fine to edit the code to append the correct table prefix, we definitely consider the migrations to be 'scaffolded code' that you are free to edit. One thing to bear in mind is that you should always use the same prefix when you are generating new migrations, otherwise migrations will think you have renamed all your tables.
Anonymous
July 19, 2012
When you add the Class Post, you shall tell us that it's necessary to add a property like: public DbSet<Blog> Blogs { get; set; } to the BlogContext!
Anonymous
July 19, 2012
@Evan Hua - It's actually not necessary in this case because the Blog class references the Post class, so Code First will find it and include it in the model automatically.
Anonymous
July 20, 2012
When playing around with the test database, I suddenly was not able to roll all the way down to "AddBlogUrl". I executed Update-Database -TargetMigration:"AddBlogUrl" , but it would simply not run the Down() method in AddPostClass. I then noticed that "AddBlogUrl" and "AddPostClass" had the same timestamp in the __MigrationHistory. It seems that if I run many migrations at once, they might end up getting the same timestamp. I then ran the following command a few times: Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:"AddPostAbstract" And finally ended up with these __MigrationHistory inserts: INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201207211248025_InitialCreate', '2012-07-21T14:31:11.114Z', 0x[...], '4.3.1') INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201207211308260_AddBlogUrl', '2012-07-21T14:31:11.145Z', 0x[...], '4.3.1') INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201207211317132_AddPostClass', '2012-07-21T14:31:11.145Z', 0x[...], '4.3.1') INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201207211354182_AddPostAbstract', '2012-07-21T14:31:11.160Z', 0x[...], '4.3.1') Manually fixing the timestamp to a unique date for "AddBlogUrl" and "AddPostClass" fixed the problem. I'm assuming this is a bug?
Anonymous
July 23, 2012
@a_Quix - Yes, that sounds like a bug where we are ordering off the CreateOn column. In EF5 we actually removed the CreatedOn column and just order based on the migration name (which includes a timestamp for when it was created). The RC of EF5 is currently available and has a go-live license.
Anonymous
August 07, 2012
I like the migration feature very much. I'm not RoR developer but as far as I understand this is the source of this feature. In RoR world there are deployment tools like capistrano which take care of running the migration scripts during deployment. How does the migration feature of EF fit into the bigger picture of deploying a web site to a server. I hoped that MsDeploy (now Web Deploy) will also be able to run the "Update-Database" command as part of the deployment. I'm using shared hosting (GoDaddy, DiscountASP) to host my application and I want my deployment to be streamline such that I will not have to connect manually to modify the database. I've resorted to use IDbInitialization which kick off the migration process but that is not optimal because it happen every time the app-pool is recycled. Can you help me understand when the migration should take place in the test-dev-deply-test-dev cycle? Thank you, Ido
Anonymous
August 08, 2012
@Ido Ran - The easiest way to do this (as you discovered) is to use the MigrateDatabaseToLatestVersion database initializer. We did some performance profiling to make sure this fails fast if the database is already up-to-date, meaning there is minimal overhead when the app pool is recycled.
Anonymous
August 09, 2012
my concrete DBContext as follows namespace HXB.Model { public partial class CompanyEntities : DbContext { public CompanyEntities() : base("name=CompanyEntities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public DbSet<Channel> Channels { get; set; } public DbSet<Introduction> Introductions { get; set; } public DbSet<LoginLog> LoginLogs { get; set; } public DbSet<News> News { get; set; } public DbSet<NewsClass> NewsClasses { get; set; } public DbSet<Partner> Partners { get; set; } public DbSet<Permission> Permissions { get; set; } public DbSet<Product> Products { get; set; } public DbSet<ProductClass> ProductClasses { get; set; } public DbSet<Project> Projects { get; set; } public DbSet<ProjectClass> ProjectClasses { get; set; } public DbSet<Role> Roles { get; set; } public DbSet<RolePermission> RolePermissions { get; set; } public DbSet<RoleUser> RoleUsers { get; set; } public DbSet<User> Users { get; set; } } } when I run the command : Enable-Migrations it says No classes deriving from DbContext found in the current project. Edit the generated Configuration class to specify the context to enable migrations for. very odd....
Anonymous
August 12, 2012
@rowan thanks for the answer. The major problem I have is that inside the migration process you call for EnsureDatabaseExist but for some reaso. I can't understand on my GoDaddy hosting this always return false and so always try to create new database and so always fail. I've move to discountASP hosting there everything works but it will be nice to both fix the bug and allow for overriding of EnsureDatabaseExist method. Ido
Anonymous
August 15, 2012
@Ido - Migrations calls EnsureDatabaseExists so that if you trigger the migrations process against a database that hasn't been created yet it will create the database for you (if it has permissions to do so) and then migrate it.
Anonymous
August 15, 2012
@Hong XuBing - Do you have more than one project on your solution? If so you will need to select the project that contains your context in the drop down in Package Manager Console.
Anonymous
November 19, 2012
Hi, Rowan! Is there any hope of creating migrations manually, without the powershell command? In particular, I'm interested in using migrations for my dynamically assembled model. I have more details here: stackoverflow.com/.../code-first-migrations-for-dynamically-assembled-model Thank you.
Anonymous
November 20, 2012
The comment has been removed
Anonymous
November 22, 2012
The comment has been removed
Anonymous
November 28, 2012
@Fyodor Soikin - I'll provide more details on StackOverflow, but for the benefit of anyone reading these comments you can generate migrations using the MigrationsScaffolder class - msdn.microsoft.com/.../system.data.entity.migrations.design.migrationscaffolder(v=vs.103).aspx
Anonymous
March 27, 2013
The EntityFramework package is not installed on project 'IPatschool.Model'.
Anonymous
March 28, 2013
@Aarti - I'm assuming your Model project just contains the types and not you context. In 'Package Manager Console' there is a Default Project dropdown - it's best to select your start up project there and migrations will locate your project that contains your context etc.
Anonymous
April 21, 2013
The comment has been removed
Anonymous
April 22, 2013
Hi Hoi, Migrations currently don't work with Web Sites (you can use them with Web Applications though). The error that you get is very confusing and we have a work item to fix it - entityframework.codeplex.com/.../568. ~Rowan
Anonymous
April 29, 2014
This is too much text. Can't you replace some of it with images? Code-first is supposed to make things easier/faster, but I really don't see this happening... :|
Anonymous
April 29, 2014
To sum up: 1)Go to package-manager 2)Enable-Migrations (might ask for more details) 3)Add-Migration (might ask for more details) 4)Update-Database End of story.
Anonymous
May 07, 2014
@Me - Good point, we'll look at adding a TL;DR section to the top of the migrations documentation next time we work on it. BTW this post is obsolete now and the current docs are here - http://msdn.com/data/jj591621.
Anonymous
January 29, 2015
Nice job. Question: with this statement " Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");", are you sure that "Abstract" is getting the contents of "LEFT(Content, 100)" based on the key PostId?
Anonymous
January 29, 2015
@McAuley - Yes, this statement is updating all rows where the Abstract property is null.
Anonymous
September 24, 2015
Liked this very much. Is there a way to script out the seed method as well like this ? In my organization only the DBA has the exclusive rights to do DDLs and one-time SQLs. In that case, if I chose Code First Paradigm and cannot provide these SQLs that will be a challenge.
Anonymous
September 24, 2015
In addition to my previous question about generating the SQL for Seed Method, there are no "USE <Database>" Statements in the SQL Generated by the Update-Database -Script powershell command. I think we need to take an extra step to add this. Is that correct ? Or am I missing something :) Thank you for this wonderful tutorial which is very short and sweet

Freigeben über