EF5代码优先 - 更改列类型进行数据迁移

问题描述

我是新来E​​F5代码首先，我着手在工作中的一个项目之前，证明了概念修修补补。

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

和我使用一个小的MVC应用程序，我被困在上面添加几个记录。

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.

有没有在迁移的方式来截断表运行ALTER语句之前？

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?

推荐答案

最聪明的方法可能是不会改变的类型。如果你需要做到这一点，我建议你做以下步骤：

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. 添加一个新列与新类型


2. 使用 SQL（）使用更新语句来接管原来的列中的数据


3. 删除旧的列


4. 重命名新列

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

这都可以在同一个完成迁移过程中，正确的SQL脚本将被创建。如果你希望自己的数据被丢弃你可以跳过步骤2。如果你要抢过来，添加适当的语句（也可以包含一个switch语句）。

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

不幸的Code First迁移不提供更简单的方式来做到这一点。

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

下面是示例代码：

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

这篇关于EF5代码优先 - 更改列类型进行数据迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！