EF5 Code First - Changing A Column Type With Migrations

Question

I am new to EF5 Code First and I'm tinkering with a proof-of-concept before embarking on a project at work.

I have initially created a model that looked something like

public class Person {
  public int Id { get; set; }
  public string FirstName { get; set;}
  public string Surname {get;set;}
  public string Location {get;set;}
}

And I added a few records using a little MVC application I stuck on the top.

Now I want to change the Location column to an enum, something like:

public class Person {
  public int Id { get; set; }
  public string FirstName { get; set;}
  public string Surname {get;set;}
  public Locations Location {get;set;}
}

public enum Locations {
  London = 1,
  Edinburgh = 2,
  Cardiff = 3
}

When I add the new migration I get:

AlterColumn("dbo.People", "Location", c => c.Int(nullable: false));

but when I run update-database I get an error

Conversion failed when converting the nvarchar value 'London' to data type int.

Is there a way in the migration to truncate the table before it runs the alter statement?

I know I can open the database and manually do it, but is there a smarter way?

Solution 1

The smartest way is probably to not alter types. If you need to do this, I'd suggest you to do the following steps:

1. Add a new column with your new type
2. Use Sql() to take over the data from the original column using an update statement
3. Remove the old column
4. Rename the new column

This can all be done in the same migration, the correct SQL script will be created. You can skip step 2 if you want your data to be discarded. If you want to take it over, add the appropriate statement (can also contain a switch statement).

Unfortunately Code First Migrations do not provide easier ways to accomplish this.

Here is the example code:

AddColumn("dbo.People", "LocationTmp", c => c.Int(nullable: false));
Sql(@"
    UPDATE dbp.People
    SET LocationTmp =
        CASE Location
            WHEN 'London' THEN 1
            WHEN 'Edinburgh' THEN 2
            WHEN 'Cardiff' THEN 3
            ELSE 0
        END
    ");
DropColumn("dbo.People", "Location");
RenameColumn("dbo.People", "LocationTmp", "Location");

Solution 2

Based on @JustAnotherUserYouMayKnow's answer, but easier.

Try firstly execute Sql() command and then AlterColumn():

Sql(@"
    UPDATE dbo.People
    SET Location =
        CASE Location
            WHEN 'London' THEN 1
            WHEN 'Edinburgh' THEN 2
            WHEN 'Cardiff' THEN 3
            ELSE 0
        END
    ");
AlterColumn("dbo.People", "Location", c => c.Int(nullable: false));

Solution 3

I know this doesn't apply directly to the question but could be helpful to someone. In my problem, I accidentally made a year field a datetime and I was trying to figure out how to delete all the data and then switch the data type to an int.

When doing an add-migration, EF wanted to just update the column. I had to delete what they wanted to do and add my own code. I basically just dropped the column and added a new column. Here is what worked for me.

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "TestingPeriodYear",
            table: "ControlActivityIssue");

        migrationBuilder.AddColumn<int>(
            name: "TestingPeriodYear",
            table: "ControlActivityIssue",
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "TestingPeriodYear",
            table: "ControlActivityIssue");

        migrationBuilder.AddColumn<DateTime>(
            name: "TestingPeriodYear",
            table: "ControlActivityIssue",
            nullable: true);
    }

Comments

[pipedreambomb]

I'd suggest for developers in similar positions that they see if the conversion is handled automatically by Entity Framework before following some of the answers on this page. For example, I found it handles the conversion between a string and a decimal and back again with no help from me - the migration that Add-Migration generated works fine. Obviously, try it out on a local/test database before applying it to production!

[Marcel]

I like it more than the marked answer: It doesn't need a temporary column.

[InteXX]

A quick note: using a temporary column is the only way I've found to do this when migrating Down(). The schema updates appear to be running inside a transaction that also contains the Sql() command, as a column update from the Integer values back to String fail with the same error message—this time on Sql() instead of AlterColumn(). This even though we run Sql() AFTER AlterColumn().

[MathieuAuclair]

Can't do that on a system with existing data