This post is now out of date.

Be sure to check out EF 4.3 Beta 1.

<transparency>I work for Microsoft</transparency> (just kidding… but I couldn’t leave the tag open)

We recently released a very early preview of Code First Migrations and it’s been getting some mixed feedback, the negative feedback falls into three buckets:

1. Stuff we have on the list but just haven’t done yet (i.e. Provider model, upgrade to specific named version, downgrade, outside of Visual Studio experience)
2. Things you want us to do differently (i.e. Get rid of those SQL scripts… I’m a developer not a DBA)
3. Functionality we did a bad job of explaining in the original posts (i.e. You don’t have to use the auto-magic behavior… you can have a script for every step if you want)

This post is going to walkthrough what it looks like to use migrations without the magic, a.k.a “Rails style migrations”. Code First Migrations also doesn’t lock you into one approach so you could always decide that you trust the magic in the future, or you can trust the magic for certain migrations.

One HUGE CAVEAT on this post is that all the scripts are going to be in SQL… one overwhelming bit of feedback we’ve heard is ‘get rid of the SQL… give me code’. You’re probably scratching you head asking ‘what exactly where those guys thinking when they used SQL scripts?’… we were thinking that folks would be more comfortable with the magic and that adding ‘custom scripts’ was going to be reserved for complex migration steps when you would likely need to drop to SQL anyway. Looks like we got that wrong (but that’s exactly why we ship CTPs). The plans we have around a provider model would make it very easy to swap in some other means of expressing the non-magical migrations… based on what we are hearing it looks we should be using code instead.

 

Getting a Model

• Create a new console application and install the EntityFramework.SqlMigrations package from ‘Package Manager Console’, this will also install the EntityFramework package:

PM> Install-Package EntityFramework.SqlMigrations

• Lets build a simple console app with a Code First model. Note that I’m also getting rid of other Code First magic by switching off database initializers and getting rid of the EdmMetadata table:

  using System;
  using System.Data.Entity;
  using System.Data.Entity.Infrastructure;
  
  namespace NoMagic
  {
      class Program
      {
          static void Main(string[] args)
          {
              // Don't do any database creation magic
              Database.SetInitializer<MyContext>(null);
  
              using (var db = new MyContext())
              {
                  foreach (var b in db.Books)
                  {
                      Console.WriteLine(b.Name);
                  }
              }
          }
      }
  
      public class MyContext : DbContext
      {
          public DbSet<Book> Books { get; set; }
  
          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
              // No EdmMetadata table please
              modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
          }
      }
  
      public class Book
      {
          public int BookId { get; set; }
          public string Name { get; set; }
      }
  }

 

First Migration

• We’re not using any magic, so we want a script that will handle database creation, we use the Add-CustomScript command for that:

PM> Add-CustomScript –Name:”InitialCreate”
Scaffolding custom script by comparing current model to database.
Added custom script: Migrations\20110730045135_InitialCreate

 

This adds a Migrations folder to our project with a sub-folder representing our script. The folder contains our script and also a Target.xml file… the Target.xml file is there to facilitate downgrade with automatic migrations… it’s always there in the CTP but in the next release we’ll only leave Target.xml around when you are actually using automatic migrations.

The script is scaffolded with what migrations would have done if you wanted it to run automatically… I won’t cover the SQL vs Code point again here, see the top. I could edit the script at this point if I don’t like what it is doing. The script is quite verbose too… I’m just showing the bit that actually creates our table (Beware: Don’t try and remove all that SET <blah, blah, blah> stuff from the script… did I mention this is a super early preview). There is also a create statement for the __ModelSnapshot table which is how the database knows what version it is at etc.

CREATE TABLE [dbo].[Books] (
    [BookId] INT            IDENTITY (1, 1) NOT NULL,
    [Name]   NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([BookId] ASC)
);

 

• Now we can use the Update-Database command to run the script

PM> Update-Database
1 pending custom scripts found.
Processing custom script [20110730045135_InitialCreate].
 - Executing custom script [20110730045135_InitialCreate].
  - Creating NoMagic.MyContext...
  - Creating [dbo].[Books]...
  - Update complete.
Ensuring database matches current model.
 - Database already matches model.

Update-Database : Cannot open database "NoMagic.MyContext" requested by the login. The login failed.
Login failed for user 'REDMOND\rowmil'.
At line:1 char:16
+ Update-Database <<<< 
    + CategoryInfo          : NotSpecified: (:) [Update-Database], SqlException
    + FullyQualifiedErrorId : System.Data.SqlClient.SqlException,System.Data.Entity.Migrations.Commands.MigrateCommand

 

Ok, so the error is an annoying bug… but everything actually worked ok. It errored out during the automatic upgrade process… which wasn’t going to do anything because we aren’t using the magic. You’ll get this error when you run the first migration against a database that doesn’t exist. Migrations will actually create the empty database for you… is that more magic that you should be able to switch off?

One thing that occurred to me while writing this post is that it would be great to have a way to prevent the automatic pipeline from ever kicking in just incase I accidently let it do something. Sounds like we need to have some settings defined in an xml config file your code.

That’s pretty much it, we just repeat that process indefinitely.

 

Second Migration

• Let’s rename the Book.Name property to Title:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
}

 

• We could just let migrations scaffold the script with a drop/add column and then manually edit the script… but let’s tell it to take the rename into account while scaffolding:

PM> Add-CustomScript -Name:"RenameBookName" -Renames:"Book.Name=>Book.Title"
Scaffolding custom script by comparing current model to database.
Added custom script: Migrations\20110730051326_RenameBookName

 

You’ll notice that migrations has added a Model.refactorlog file to your project… Model.refactorlog shouldn’t be there since we scaffolded a script rather than doing an automatic upgrade… another CTP thing that we’ll fix.

 

Looking at the scaffolded script we see that it contains a rename rather than a drop create:

EXECUTE sp_rename @objname = N'[dbo].[Books].[Name]', @newname = N'Title', @objtype = N'COLUMN';

 

• Now we can use Update-Database to run our script:

PM> Update-Database
1 pending custom scripts found.
Processing custom script [20110730051326_RenameBookName].
 - Executing custom script [20110730051326_RenameBookName].
  - Rename [dbo].[Books].[Name] to Title
  - Caution: Changing any part of an object name could break scripts and stored procedures.
  - Update complete.
Ensuring database matches current model.
 - Database already matches model.

 

Conclusion

Code First Migrations supports automatic upgrade, custom scripts or a combination of both. We use SQL for the custom scripts at the moment but you are telling us they need to be code based. This is an early CTP which means it is full of rough edges… but that also means we are early enough in the release cycle to change things