Scott Hanselman

Hot on the heels of my RFC blog post on product versioning, the Entity Framework team has released Entity Framework 4.1 Code First Migrations: August 2011 CTP. Cool. And it's July, too!

Or my preferred product name, "Migrating Magic Unicorns 0.5." It's probably best to think of this as 0.5 Alpha Migrations for EF but that's my guess at a name and not nearly as descriptive.

I showed early daily builds of EF Migrations at a few conferences recently, and encouraged folks to comment on the ADO.NET team blog. Now they've released bits for us to play with. This initial CTP is available via NuGet as the EntityFramework.SqlMigrations package.

Here's the general idea. Be aware that this is NOT specific to the Web. You can do this in a Console App or whatever. I just like Hello World Web applications.

Short Walkthrough

Make a new ASP.NET MVC app. Go to Manage NuGet Packages from References and search for EntityFramework.SqlMigrations. Note the dependency it has. It'll upgrade your project's EntityFramework package as well.

Now, make a new class with a simple model:

namespace MvcApplication15.Models
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
    }
    
    public class DemoContext : DbContext
    {
        public DbSet<Person> People { get; set; }
    }
}

Scaffold out a quick Person Controller...

Visit it at /Person and make a few People.

The database that was created looks like this. There's a People table and the First column that is an nvarchar.

Now, let's add an Email field. I'll update the Person class to include Email:

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

Then, I'll compile and from the console inside Visual Studio, I'll do this:

PM> update-database
No pending custom scripts found.
Ensuring database matches current model.
 - Performing automatic upgrade of database.
  - Starting rebuilding table [dbo].[EdmMetadata]...
  - Caution: Changing any part of an object name could break scripts and stored procedures.
  - Starting rebuilding table [dbo].[People]...
  - Caution: Changing any part of an object name could break scripts and stored procedures.
  - Update complete.

I typed update-database, that's all. This is an automatic migration. See how the system compare the .NET type and the database and did what needed to be done:

Now, let's rename Email to EmailAddress. If I change the Person...

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

And type update-database...

PM> update-database
No pending custom scripts found.
Ensuring database matches current model.
 - Performing automatic upgrade of database.
Update-Database :   - .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
At line:1 char:16
+ Update-Database <<<< 
    + CategoryInfo          : NotSpecified: (:) [Update-Database], Exception
    + FullyQualifiedErrorId : UnhandledException,System.Data.Entity.Migrations.Commands.MigrateCommand

I'm told that data loss may occur. It can't tell that I want to rename that column or not. It doesn't know what it was before and what it wants to be. Maybe I want to drop Email and add EmailAddress? Who knows. Let me be explicit and give Migrations more context.

PM> Update-Database -Renames:"Person.Email=>Person.EmailAddress"
No pending custom scripts found.
Ensuring database matches current model.
 - Performing automatic upgrade of database.
  - The following operation was generated from a refactoring log file d5598498-a656-4ccd-1e93-bea562ab6e31
  - Rename [dbo].[People].[Email] to EmailAddress
  - Caution: Changing any part of an object name could break scripts and stored procedures.
  - Update complete.

I'm not sure if I like that Renames: syntax. I'm sure the team would be interested in your opinion. But that works, as I can see in the database.

What Changes Can Migrations Detect Automatically?

From the ADO.NET blog:

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

Moving to Staging/Production/etc

Once dev is correct, when you want to move to production, you would generate a script for your other database by doing a diff between what that DB looks like and what the code looks like.

For example, -script generates a script I can run myself with osql.exe or whatever.

update-database -Script -ConnectionString "SERVER=.\SQLEXPRESS;Database=PersonProd;Trusted_Connection=true;"

Which spits out something like (but more complex than) this:

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

Call for Feedback

The EF Team has also this specific call for feedback when it comes to Custom Scripts:

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.

Leave your thoughts in my comments, or theirs and I'll make sure the right people get it.

Enjoy!

P.S. Note the EntityFramework.SqlMigrations NuGet package's exposed version number. It's 0.5.10727.0. ;)

Limitations

This is Alpha, so read the Limitations section. They are putting out rough things like this because they know we want to see bits earlier, but the trade off is limitations. Here's a few. Read the list for the rest.

• There is no provider model, this release only targets SQL Server, including SQL Azure. SQL Compact and other providers are not supported. We are currently working through what the provider model should look like for migrations.
  • Question: What do you think? How important is this?
• Migrations currently needs to run in full trust. This isn’t an issue when working inside of Visual Studio but if consuming the migrations assembly from custom code you may want to run in medium trust. We are looking at ways to support this in a later release.
• This release is only available via NuGet. As we support more scenarios such as team build and an ‘outside of Visual Studio’ command line experience we will also support more installation options.
  • Scott: Limitation? That's lovely! ;)
• Downgrade is currently not supported. When generating custom scripts you will notice that the script is named ‘Up.sql’ but there is no corresponding ‘Down.sql’. We are planning to add downgrade functionality prior to RTM but it is not available in this release.

I'm glad we're getting migrations. It's been a missing LEGO piece for a while.

Related Links

• Code First Migrations: August 2011 CTP Released
  • Please be sure to read the ‘Issues & Limitations’ section of the announcement post before using migrations. It's a 0.5 version. That's a Fuzzy Alpha, friends.
• Code First Migrations: Walkthrough of August 2011 CTP
• Comment in the Entity Framework Pre-Release Forum. They are listening intently.

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

About   Newsletter

Hosting By