EF迁移,用于更改列的数据类型
本文关键字:数据类型 迁移 用于 EF | 更新日期: 2023-09-27 18:25:19
我的项目中有一个模型,如下所示:
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; }
}
迁移如下
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());
}
当我使用更新数据库时,会出现以下错误:
对象"DF__Receiv_FromN__25869641"依赖于列"FromNo"。由于一个或多个原因,ALTER TABLE ALTER COLUMN FromNo失败对象访问此列。
这些表没有外键或者其他什么,那么问题出在哪里呢?
您的列上有一个默认约束。您需要先删除约束,然后更改列。
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());
}
您可能还必须删除其他列上的默认约束。
我刚刚看到安德烈的评论(我知道——很晚了),他是对的。因此,一种更稳健的方法是使用类似的东西:
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)
我知道这可能对OP没有帮助,但希望它能帮助其他遇到这个问题的人。
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());
}
更好的方法是永远解决问题。
您可以从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);
}
}
}
并设置此配置:
internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
SetSqlGenerator("System.Data.SqlClient", new FixedSqlServerMigrationSqlGenerator ());
}
...
}
这是将已有外键约束的列更改为"not null"的示例。该列的名称是表"SubTable"中的"FKColumnName",它引用的是表"MainTable"中"Id"列。
向上脚本:
将列设置为"不可为null"后,首先删除索引和外键,然后重新创建。
向下脚本:
这里的步骤是相同的,只是列可以再次为null。
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");
}
}
我遇到了这个问题,整数列的默认值为零约束。
在我的案例中,我通过从实体框架6.1.x切换到EF 6.2.0解决了这个问题。
在6.2之前的EF中有一个已知的错误,这意味着EF在更改列时有时不会自动处理这些类型的约束。该错误在官方EF github回购中有描述,Bricelam将该问题描述为:
当添加NOT NULL列时,我们为任何现有行。看起来我们的逻辑是放弃默认约束在ALTER COLUMN不考虑这一点之前。
可以在此处找到修复该问题的提交。
如果您使用的是EF:
- 删除迁移文件夹和数据库
enable-migrations
add-migration initial
update-database
不过,此解决方案将删除数据库中的所有当前项。如果这不是你的意图,我会建议其他答案之一。