c# - 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?

Answer 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");