CODEWITHSUNDEEP
c#ef-code-firstentity-framework-5entity-framework-migrations
Nick
Nick

Reputation: 4223

EF5 Code First - Changing A Column Type With Migrations

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?

Upvotes: 48

Views: 52879

Answers (4)

Ali Tayyip Aydin
Ali Tayyip Aydin

Reputation: 43

If you are using PostgreSQL:

In PostgreSQL, when there's a possibility of data loss, adding a USING clause is necessary to be able to delete data. Unfortunately, there isn't an automated feature in Entity Framework that handles this. When generating a migration, if you replace the alter part of the column in the example provided below, you can take a migration without encountering any issues during the update-database process.

migrationBuilder.Sql($@" ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING (column_name::integer); ");

Upvotes: 1

Ben
Ben

Reputation: 2020

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

Upvotes: 4

Seven
Seven

Reputation: 825

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

Upvotes: 28

JustAnotherUserYouMayKnow
JustAnotherUserYouMayKnow

Reputation: 2574

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

Upvotes: 74

Related Questions