Changing the type of a property with EF Code First [duplicate]

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

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

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