C# EF5 Code First - 使用迁移更改列类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14837168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EF5 Code First - Changing A Column Type With Migrations
提问by Nick
I am new to EF5 Code First and I'm tinkering with a proof-of-concept before embarking on a project at work.
我是 EF5 Code First 的新手，在开始工作项目之前，我正在修改概念验证。
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.
我使用一个小 MVC 应用程序添加了一些记录，我粘在顶部。
Now I want to change the Location column to an enum, something like:
现在我想将 Location 列更改为枚举，例如：
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
但是当我运行 update-database 时出现错误
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?
迁移中有没有办法在运行alter语句之前截断表？
I know I can open the database and manually do it, but is there a smarter way?
我知道我可以打开数据库并手动执行此操作，但是有更聪明的方法吗？
采纳答案by JustAnotherUserYouMayKnow
The smartest way is probably to not alter types. If you need to do this, I'd suggest you to do the following steps:
最聪明的方法可能是不改变类型。如果您需要这样做，我建议您执行以下步骤：
- Add a new column with your new type
- Use
Sql()to take over the data from the original column using an update statement - Remove the old column
- Rename the new column
- 添加具有新类型的新列
- 用于
Sql()使用更新语句从原始列中接管数据 - 删除旧列
- 重命名新列
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).
这一切都可以在同一次迁移中完成，将创建正确的 SQL 脚本。如果您希望丢弃数据，可以跳过第 2 步。如果你想接管它，添加适当的语句（也可以包含一个 switch 语句）。
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");
回答by Seven
Based on @JustAnotherUserYouMayKnow's answer, but easier.
基于@JustAnotherUserYouMayKnow 的回答，但更容易。
Try firstly execute Sql()command and then AlterColumn():
尝试先执行Sql()命令，然后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));
回答by Ben
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.
我知道这并不直接适用于该问题，但可能对某人有所帮助。在我的问题中，我不小心将年份字段设为日期时间，我试图弄清楚如何删除所有数据，然后将数据类型切换为 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.
在进行添加迁移时，EF 只想更新列。我不得不删除他们想做的事情并添加我自己的代码。我基本上只是删除了该列并添加了一个新列。这对我有用。
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);
}
