Manually extending MVC Entity Framework migrations

I recently applied an entity framework migration that failed as the data in the source table would get truncated. In this case, we need to include some migration scripts to change the existing data. To do that, you need to manually extend the migration.

Background on Migrations

Migrations allow you to upgrade and downgrade the schema of a database automatically during execution of an application. To enable migrations, you open the Package Manager Console for your MVC project and run the following command:

Enable-Migrations

This will install a table called _MigrationHistory and this table will contain each migration step including the schema that the database had at the specific time. It will also generate a folder named Migrations with the initial configuration.

If you change the DbContext class by, for example, adding columns or new tables, then you need to create a new migration. This can automatically be applied by running the following command in the Package Manager Console:

Add-Migration NameOfMigration

The “NameOfMigration” becomes the migration label and also part of the generated file name that is added to the Migrations folder of your project. If you open this table, you find an “Up” and a “Down” command for upgrading and downgrading the schema. This will for example generate the following:

public override void Down()
{
    this.DropTable("dbo.Links");
}

public override void Up()
{
    this.CreateTable(
        "dbo.Links", 
        c =>
        new
            {
                Id = c.Int(false, true), 
                Owner = c.String(maxLength: 255), 
                CommonIdentifier = c.Int(false), 
                Predefined = c.Boolean(false), 
                Icon = c.String(), 
                Description = c.String(maxLength: 1000), 
                Order = c.Int(false), 
                Url = c.String(maxLength: 1000), 
                Title = c.String(maxLength: 1000), 
                SortOrder = c.Int(false), 
                Category = c.String(maxLength: 1000)
            }).PrimaryKey(t => t.Id);
}

You can also roll back a migration from the database by using the following command:

Update-Database -TargetMigration:"MigrationName"

The migration name can easily be found by opening the table _MigrationHistory.

The issue with migrations and data truncation

The specific error I received was generated by changing the Title field from 1000 characters to 255. Therefore, some data would be truncated and I received the following exception:

String or binary data would be truncated. The statement has been terminated

This requires me to execute the DbMigration.Sql command to manipulate the data. The migration, plus the manual change, are now within the Up method as:

this.Sql("UPDATE Links SET Title = LEFT(Title, 255) WHERE LEN(Title) > 255");
this.AlterColumn("dbo.Links", "Title", c => c.String(maxLength: 255));

Now, the SQL statement is executed first and I can then safely apply my DbMigration.AlterColumn command without any exceptions.

 

Hope this helps!