Code First Migrations: Walkthrough of August 2011 CTP

  • Article

 

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

Please be sure to read the ‘Issues & Limitations’ section of the announcement post before using migrations.

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.

 

Building an Initial Model

  1. Create a new ‘Demo’ Console application
    .

  2. Add the EntityFramework NuGet package to the project

    • Tools –> Library Package Manager –> Package Manager Console

    • Run the ‘Install-Package EntityFramework’ command

      Note: If you have previously run the standalone installer for the original EF 4.1 RTM you will need to upgrade or remove the installation because migrations relies on EF 4.1 Update 1. This is required because the installer adds the EF 4.1 assembly to the Global Assembly Cache (GAC) causing the original RTM version to be used at runtime rather than Update 1.
      .

  3. Replace the contents of Program.cs with the following code:
    using System;
    using System.Data.Entity;
    using System.Linq;

    namespace Demo
    {
    class Program
    {
    static void Main(string[] args)
    {
    using (var db = new DemoContext())
    {
    if (!db.People.Any())
    {
    db.People.Add(new Person { Name = "John Doe" });
    db.People.Add(new Person { Name = "Jane Doe" });
    db.SaveChanges();
    }

    foreach (var person in db.People)
    {
    Console.WriteLine(person.Name);
    }
    }
    }
    }

    public class DemoContext : DbContext
    {
    public DbSet<Person> People { get; set; }
    }

    public class Person
    {
    public int PersonId { get; set; }
    public string Name { get; set; }
    }
    }
    . .

  4. Run the application

 

Automatic Migrations

  1. Make sure that you have run the application from the previous step
    (This will ensure that the database has been created with the initial schema)
    .

  2. Update the Person class to include an Email property:

     public class Person 
{ 
  public int PersonId { get; set; } 
  public string Name { get; set; } 
  public string Email { get; set; } 
}
.

  3. Run the application again

    • You will receive an exception informing you that the database no longer matches the model

      “The model backing the 'DemoContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.”
      .

  4. Fortunately we now have a better alternative to the two options proposed in the exception message. Let’s install Code First Migrations!

    • Tools –> Library Package Manager –> Package Manager Console
    • Run the ‘Install-Package EntityFramework.SqlMigrations’ command
      .

  5. Installing migrations has added a couple of commands to Package Manager Console. Let’s use the Update-Database command to bring our schema inline with our model.

    • Run the ‘Update-Database‘ command in Package Manager Console

      Migrations will now attempt to calculate the changes required to make the database match the model. In our case this is a very simple change, and there is no chance of data loss, so migrations will go ahead and apply the change. Later in this walkthrough we will look at taking control of more complicated changes as well as previewing the script that migrations will run.

 

What Changes Can Migrations Detect Automatically?

In this section we looked at adding a property, here is the full list of changes that migrations can take care of automatically:

  • Adding a property or class
    • Nullable columns will be assigned a value of null for any existing rows of data
    • Non-Nullable columns will be assigned the CLR default for the given data type for any existing rows of data
  • Renaming a property or class
    • See ‘Renaming Properties & Classes’ for the additional steps required here
  • Renaming an underlying column/table without renaming the property/class
    (Using data annotations or the fluent API)
    • Migrations can automatically detect these renames without additional input
  • Removing a property
    • See ‘Automatic Migrations with Data Loss’ section for more information

 

Renaming Properties & Classes

So far we have looked at changes that migrations can infer without any additional information, now let’s take a look at renaming properties and classes.

  1. Rename the Person.Email property to EmailAddress

     public class Person 
{ 
  public int PersonId { get; set; } 
  public string Name { get; set; } 
  public string EmailAddress { get; set; } 
}

    .

  2. Attempt to migrate using the ‘Update-Database’ command in Package Manager Console

    • You will receive an error warning about data loss. This is because migrations doesn’t know about the property rename and is attempting to drop the Email column and add a new EmailAddress column.

      ”Update-Database : - .Net SqlClient Data Provider: ……. Rows were detected. The schema update is terminating because data loss might occur.” .

  3. Let’s preview what migrations is trying to do by running ‘Update-Database –Script‘

    • This gives us a script showing us what migrations is trying to do. Inspecting this script confirms that migrations is trying to drop the Email column.
      ALTER TABLE [dbo].[People] DROP COLUMN [Email] ;
      .

  4. We can inform migrations of the rename by running:
    ‘Update-Database -Renames:"Person.Email=>Person.EmailAddress"’
    .

    • Migrations will go ahead and migrate the database, this time the Email column is renamed to EmailAddress
    • You’ll also notice that a ‘Migrations’ folder is added to your project with a single Model.refactorlog file in it. This file ensures that the same rename will be applied when migrations is run against a different database or on another developers machine. The ‘Migrations’ folder will get some more use later in this walkthrough.

 

The renames parameter is a comma separated list of renames and can include class and property renames. Class renames use the same format as property renames i.e. –Renames:”Person=>Customer”.

 

Custom Scripts

Up until now we’ve let migrations take care of working out what SQL to execute. Now let’s take a look at how we can take control when we need to do something more complex.

Call for Feedback: From what we are seeing in our own internal use we don’t anticipate that custom scripts will be required very often. However, our efforts are somewhat sheltered from the ‘real world’ so we would love feedback on situations where you need to use custom scripts. In particular we are interested if there are significant scenarios where a code based alternative to writing raw SQL would be beneficial.

  1. Remove the Person.Name property and add in FirstName and LastName properties

     public class Person 
{ 
  public int PersonId { get; set; } 
  public string EmailAddress { get; set; } 
  public string FirstName { get; set; } 
  public string LastName { get; set; } 
}

  2. You’ll also need to update the code in the main method to deal with this model change:
    static void Main(string[] args)
    {
    using (var db = new DemoContext())
    {
    foreach (var person in db.People)
    {
    Console.WriteLine("{0}, {1}", person.LastName, person.FirstName);
    }
    }
    }

  3. If we try and upgrade we will get an error warning about data loss because migrations will try and drop the Name column. What we really want to do is take care of populating the new columns with data from the old column before dropping it.
    .

  4. Ask migrations to scaffold it’s best guess at the changes to a new custom script by running:
    ‘Add-CustomScript –n:”SplitPersonName”’
    .

  5. You’ll now notice a new sub-folder appear in the ‘Migrations’ folder in your project. The name of this folder contains a timestamp to control ordering and the human readable name that you supplied. This new folder also contains a few files:

    • Source.xml – This file captures the state that the database should be in before this custom script is run. This allows migrations to replicate the changes we have made to the schema in the previous sections of this walkthrough before running our custom script.
    • Up.sql – This is the script that will be run. Migrations has given you a starting point by populating it with the SQL it was trying to run.
    • Target.xml – This is the state that the database should be in after the script has run (i.e. the current state of the model). This file will come into play more once we support downgrade as well as upgrade.
      .

  6. Let’s go ahead and change the script to add the new columns, migrate data and then drop the old column:

    • You’ll notice the script contains a lot of SET statements, this is just a limitation of this first preview and we are working to remove them for future releases.
      SET ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT, ANSI_NULLS OFF

       ;

      GO PRINT N'Adding new name columns...'

       ;

      GO ALTER TABLE [dbo].[People] ADD [FirstName] NVARCHAR (MAX) NULL, [LastName] NVARCHAR (MAX

       ) NULL;

      GO PRINT N'Migrating Data...'

       ;

      GO UPDATE [dbo].[People] SET [FirstName] = LEFT([Name], CHARINDEX(' ', [Name]) - 1), [LastName] = RIGHT([Name], LEN([Name]) - CHARINDEX(' ', [Name]

       ))

      GO PRINT N'Removing old name column...'

       ;

      GO ALTER TABLE [dbo].[People] DROP COLUMN [Name]

       ; GO

  7. Run the ‘Update-Database’ command to bring the database up to date

 

With our custom script complete we can go back to using the automatic upgrade functionality, until we find the need to take control again. Migrations allows you to swap between automatic upgrade and custom scripts as needed. The Source.xml file associated with each custom script allows migrations to reproduce the same migration steps we have performed against other databases.

 

Automatic Migrations with Data Loss

So far we have only made changes that avoid data loss but let’s take a look at how we can let an automatic migration execute even when it detects that data loss will occur.

  1. Remove the EmailAddress property from Person

     public class Person 
{ 
  public int PersonId { get; set; } 
  public string FirstName { get; set; } 
  public string LastName { get; set; } 
}

  2. If we try and upgrade we will get an error warning about data loss but we can run
    ‘Update-Database –Force’ to let migrations know that we are ok with data loss occurring

 

Summary

In this walkthrough we saw an overview of the functionality included in the first preview of Code First Migrations. We saw automatic migrations, including property and class renames as well as migrating with data loss. We also saw how to use custom scripts to take control of parts of the migration process. 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