In previous posts I created a database using EF code-first and I enabled migrations.

Now lets try some advanced topics:

• Downgrades & migrations to specific  versions
• Customizing Migrations
• Execute custom SQL with the migration
• Get migration generated SQL
• Auto migrate when the application start and additional actions

Downgrades & migrations to specific  versions

To migrate to an specific version run in command in Package Manager Console.

Update-Database –TargetMigration: MigrationName

Customizing Migrations

Let’s add a couple of new properties to our Model and add a migration to generate the default migration. We will add some information to our recipes:

• A name: the name shall be unique for each recipe.
• A complexity index: each recipe shall have a complexity index from 1 to 3, the default complexity level shall be 2.

This is our new Recipe class with a name and complexity index:

public class Recipe
{
    // Primary Key
    public int RecipeId { get; set; }
 
    // EF needs the virtual attribute to enable Lazy loading 
    public virtual List<Food> Ingredients { get; set; }
 
    // Recipe description
    public string Description { get; set; }
 
    // Recipe name
    [Index(IsUnique = true)]
    [MaxLength(200)] 
    public string Name { get; set; }
 
    // Recipe complexity
    [Range(1, 3)]
    [DefaultValue(2)]
    public int Complexity { get; set; }
}

Add the default migration:

PM> Add-Migration Recipe_NameAndComplexity
Scaffolding migration ‘Recipe_NameAndComplexity’.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration Recipe_NameAndComplexity’ again.

public partial class NameAndComplexity : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Recipes", "Name", c => c.String());
        AddColumn("dbo.Recipes", "Complexity", c => c.Int(nullable: false));
        CreateIndex("dbo.Recipes", "Name", unique: true);
    }
 
    public override void Down()
    {
        DropIndex("dbo.Recipes", new[] { "Name" });
        DropColumn("dbo.Recipes", "Complexity");
        DropColumn("dbo.Recipes", "Name");
    }
}

The migration class was added correctly, it will create the two new columns.

We want a unique recipe name and the generated code for the name is correct because we added the annotation “[Index(IsUnique = true)]”.

The complexity however has not a default value of 2. This requirement can be fulfilled editing the migration class:

AddColumn("dbo.Recipes", "Complexity", c => c.Int(nullable: false, defaultValue: 2));

I have already a recipe in the database, after executing the migration I would like to have this recipe with the default value…

If I try to run Update-Database the database I have this error:

Column ‘Name’ in table ‘dbo.Recipes’ is of a type that is invalid for use as a key column in an index.

After investigating the problem I found something… I wanted to create an Index from a string without MaxLength and an Index can have a size up to 900 bytes…  I will add a MaxLength to my Recipe Name column:

AddColumn("dbo.Recipes", "Name", c => c.String(maxLength:200));

Let’s try to run Update-Database again:

PM> Update-Database
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201407271554362_NameAndComplexity].
Applying explicit migration: 201407271554362_NameAndComplexity.
Running Seed method.

Looks better, my database was updated. My Recipes table has two new columns a Name and a complexity that by default has a value of 2:

I already checked that the database has a unique index using the recipe name, but let’s try to do something wrong from code. I will try to create 2 recipes with the same name:

As expected an exception occurs and the Message is clear, Recipe name must be unique:

Cannot insert duplicate key row in object ‘dbo.Recipes’ with unique index ‘IX_Name’.

Execute custom SQL

At some point may be necessary to execute custom SQL code after a migration. E.g. to move data, generate data, etc.

This is really easy, just call Sql(query) within the Up or Down methods, where query is a string with the query to run.

Let’s try it out adding a default name to our recipes with name = NULL. To keep it simple, set a name equal to “Recipe” + <RecipeID>.

This is our new migration with the query to run:

public partial class CustomQuery : DbMigration
{
    public override void Up()
    {
        const string createDefaultRecipeNameQuery = "UPDATE dbo.Recipes SET Name = CONCAT('Recipe_', RecipeId) WHERE Name IS NULL";
        Sql(createDefaultRecipeNameQuery);
    }
 
    public override void Down()
    {
    }
}

The result after updating the database:

Get migration generated SQL

At some point may be necessary to execute the migration in a different machine or maybe even in production… For this it is necessary to get the generated SQL.

Update-Database -Script -SourceMigration: $InitialDatabase -TargetMigration: MigrationName

By default:

• SourceMigration: the current status.
• TargetMigration: the last migration.

Let’s inspect the generated SQL if we add a new column, for example a preparation time for our recipes:

public TimeSpan PreparationTime { get; set; }

namespace CodeFirstAzureTest.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class PreparationTime : DbMigration
    {
        public override void Up()
        {
            AddColumn("dbo.Recipes", "PreparationTime", c => c.Time(nullable: false, precision: 7));
        }
        
        public override void Down()
        {
            DropColumn("dbo.Recipes", "PreparationTime");
        }
    }
}

Now instead of just updating the database I will generate the SQL: Run Update-Database –Script.

Perfect! Visual Studio generates and opens the generated SQL.

Let’s try to generate the SQL to create the current version of the database from scratch.

Update-Database -Script -SourceMigration: $InitialDatabase

This time the generated SQL is bigger of course… but this also works out of the box!

Auto upgrade when the application start

If we want to execute an update-database before each application run we have to add this line before any DbContext is used:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<FoodContext, Configuration>());

The method SetInitializer has a IDatabaseInitializer parameter with the Type of the related DbContext and the Configuration file. (The file created when Migrations are enabled)

These are the predefined Initializers:

References:

• http://msdn.microsoft.com/en-us/data/jj591621