C# 用于更改列数据类型的 EF 迁移
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17894906/
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
EF migration for changing data type of columns
提问by Pooya Yazdani
I have a Model in my project as below:
我的项目中有一个模型，如下所示：
public class Model
{
public int Id { get; set; }
public long FromNo { get; set; }
public long ToNo { get; set; }
public string Content { get; set; }
public long TicketNo { get; set; }
}
The migration is as below
迁移如下
public override void Down()
{
AlterColumn("dbo.Received", "FromNo", c => c.Long(nullable: false));
AlterColumn("dbo.Received", "ToNo", c => c.Long(nullable: false));
AlterColumn("dbo.Received", "TicketNo", c => c.Long(nullable: false));
}
public override void Up()
{
AlterColumn("dbo.Received", "FromNo", c => c.String());
AlterColumn("dbo.Received", "ToNo", c => c.String());
AlterColumn("dbo.Received", "TicketNo", c => c.String());
}
when I use Update-Database the error below is raised:
当我使用 Update-Database 时，会引发以下错误：
The object 'DF__Receiv__FromN__25869641' is dependent on column 'FromNo'. ALTER TABLE ALTER COLUMN FromNo failed because one or more objects access this column.
对象“DF__Receiv__FromN__25869641”依赖于“FromNo”列。ALTER TABLE ALTER COLUMN FromNo 失败，因为一个或多个对象访问此列。
This tables has no foreign key or what else so what is the problem?
这个表没有外键或其他什么所以有什么问题？
采纳答案by James Hull
You have a default constraint on your column. You need to first drop the constraint, then alter your column.
您的列有默认约束。您需要首先删除约束，然后更改您的列。
public override void Up()
{
Sql("ALTER TABLE dbo.Received DROP CONSTRAINT DF_Receiv_FromN__25869641");
AlterColumn("dbo.Received", "FromNo", c => c.String());
AlterColumn("dbo.Received", "ToNo", c => c.String());
AlterColumn("dbo.Received", "TicketNo", c => c.String());
}
You will probably have to drop the default constraints on your other columns as well.
您可能还必须删除其他列的默认约束。
I've just seen Andrey's comment (I know - very late) and he is correct. So a more robust approach would be to use something like:
我刚刚看到安德烈的评论（我知道 - 很晚了），他是正确的。因此，更强大的方法是使用以下内容：
DECLARE @con nvarchar(128)
SELECT @con = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('dbo.Received')
AND col_name(parent_object_id, parent_column_id) = 'FromNo';
IF @con IS NOT NULL
EXECUTE('ALTER TABLE [dbo].[Received] DROP CONSTRAINT ' + @con)
I know this probably doesn't help the OP but hopefully it helps anyone else that comes across this issue.
我知道这可能对 OP 没有帮助，但希望它可以帮助遇到此问题的任何其他人。
回答by DTTerastar
static internal class MigrationExtensions
{
public static void DeleteDefaultConstraint(this IDbMigration migration, string tableName, string colName, bool suppressTransaction = false)
{
var sql = new SqlOperation(
string.Format(@"DECLARE @SQL varchar(1000)
SET @SQL='ALTER TABLE {0} DROP CONSTRAINT ['+(SELECT name
FROM sys.default_constraints
WHERE parent_object_id = object_id('{0}')
AND col_name(parent_object_id, parent_column_id) = '{1}')+']';
PRINT @SQL;
EXEC(@SQL);", tableName, colName)
)
{
SuppressTransaction = suppressTransaction
};
migration.AddOperation(sql);
}
}
public override void Up()
{
this.DeleteDefaultConstraint("dbo.Received", "FromNo");
AlterColumn("dbo.Received", "FromNo", c => c.String());
this.DeleteDefaultConstraint("dbo.Received", "ToNo");
AlterColumn("dbo.Received", "ToNo", c => c.String());
this.DeleteDefaultConstraint("dbo.Received", "TicketNo");
AlterColumn("dbo.Received", "TicketNo", c => c.String());
}
回答by Martin
This is an example for changing an existing column to 'not null' that already has a foreign key constraint.
The column's name is "FKColumnName" in table "SubTable" and it is referencing the "Id" column in table "MainTable".
这是将现有列更改为已具有外键约束的“非空”的示例。该列的名称是表“SubTable”中的“FKColumnName”，它引用表“MainTable”中的“Id”列。
Up script:
向上脚本：
After the column is made 'not nullable' the index and the foreign key first dropped and then re-created.
在列被设置为“不可为空”后，索引和外键首先删除，然后重新创建。
Down script:
下脚本：
Here the steps are identical except that the column is made nullable again.
这里的步骤是相同的，只是该列再次变为可空。
public partial class NameOfMigration : DbMigration
{
public override void Up()
{
DropForeignKey("dbo.SubTable", "FKColumnName", "dbo.MainTable");
DropIndex("dbo.SubTable", new[] { "FKColumnName" });
AlterColumn("dbo.SubTable", "FKColumnName", c => c.Int(nullable: false));
CreateIndex("dbo.SubTable", "FKColumnName");
AddForeignKey("dbo.SubTable", "FKColumnName", "dbo.MainTable", "Id");
}
public override void Down()
{
DropForeignKey("dbo.SubTable", "FKColumnName", "dbo.MainTable");
DropIndex("dbo.SubTable", new[] { "FKColumnName" });
AlterColumn("dbo.SubTable", "FKColumnName", c => c.Int(nullable: true));
CreateIndex("dbo.SubTable", "FKColumnName");
AddForeignKey("dbo.SubTable", "FKColumnName", "dbo.MainTable", "Id");
}
}
回答by Mostafa Basha
If you're using EF:
如果您使用的是 EF：
- Delete the migration folder and the database
enable-migrationsadd-migration initialupdate-database
- 删除迁移文件夹和数据库
enable-migrationsadd-migration initialupdate-database
Although, this solution would remove all current items in the database. If this is not your intention, I would suggest one of the other answers.
虽然，此解决方案将删除数据库中的所有当前项目。如果这不是您的意图，我会建议其他答案之一。
回答by Elyas Dolatabadi
The better way is to solve the problem for ever.
更好的方法是永远解决问题。
You can implement a custom sql generator class derived from SqlServerMigrationSqlGenerator from System.Data.Entity.SqlServer namespace:
您可以实现从 System.Data.Entity.SqlServer 命名空间的 SqlServerMigrationSqlGenerator 派生的自定义 sql 生成器类：
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.SqlServer;
namespace System.Data.Entity.Migrations.Sql{
internal class FixedSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator {
protected override void Generate(AlterColumnOperation alterColumnOperation){
ColumnModel column = alterColumnOperation.Column;
var sql = String.Format(@"DECLARE @ConstraintName varchar(1000);
DECLARE @sql varchar(1000);
SELECT @ConstraintName = name FROM sys.default_constraints
WHERE parent_object_id = object_id('{0}')
AND col_name(parent_object_id, parent_column_id) = '{1}';
IF(@ConstraintName is NOT Null)
BEGIN
set @sql='ALTER TABLE {0} DROP CONSTRAINT [' + @ConstraintName+ ']';
exec(@sql);
END", alterColumnOperation.Table, column.Name);
this.Statement(sql);
base.Generate(alterColumnOperation);
return;
}
protected override void Generate(DropColumnOperation dropColumnOperation){
var sql = String.Format(@"DECLARE @SQL varchar(1000)
SET @SQL='ALTER TABLE {0} DROP CONSTRAINT [' + (SELECT name
FROM sys.default_constraints
WHERE parent_object_id = object_id('{0}')
AND col_name(parent_object_id, parent_column_id) = '{1}') + ']';
PRINT @SQL;
EXEC(@SQL); ", dropColumnOperation.Table, dropColumnOperation.Name);
this.Statement(sql);
base.Generate(dropColumnOperation);
}
}
}
and Set this configuration:
并设置此配置：
internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
SetSqlGenerator("System.Data.SqlClient", new FixedSqlServerMigrationSqlGenerator ());
}
...
}
回答by tomRedox
I was having this issue with a default value of zero constraint on an integer column.
我在整数列上的默认值为零约束时遇到了这个问题。
In my case I solved it by switching from Entity Framework 6.1.x to EF 6.2.0.
就我而言，我通过从 Entity Framework 6.1.x 切换到 EF 6.2.0 解决了这个问题。
There's a known bug in EF prior to 6.2 that means that EF sometimes doesn't deal with these types of constraints automatically when altering columns. That bug is described on the official EF github repo here, Bricelam describes the issue as:
在 6.2 之前的 EF 中存在一个已知错误，这意味着 EF 有时在更改列时不会自动处理这些类型的约束。该错误在此处的官方 EF github repo 中有所描述，Bricelam 将问题描述为：
When adding NOT NULL columns, we synthesize a default value for any existing rows. It looks like our logic to drop default constraints before ALTER COLUMN doesn't take this into account.
添加 NOT NULL 列时，我们为任何现有行合成一个默认值。看起来我们在 ALTER COLUMN 之前删除默认约束的逻辑没有考虑到这一点。
The commit for the fix for that issue can be found here.
可以在此处找到该问题修复程序的提交。
