在删除列的迁移操作期间,如何生成SQL以先检查该列是否存在,然后再尝试删除它?
对于删除列操作,Entity Framework当前会生成类似于以下SQL:
// Migration Operation:
DropColumn("dbo.Table", "Column");
// TSQL generated:
// Dependency management logic ...
ALTER TABLE [dbo].[Table] DROP COLUMN [Column]
如何修改SQL以先检查列是否存在:
// Migration Operation:
DropColumn("dbo.Table", "Column");
// TSQL desired:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = Object_id('dbo.Table') AND name = 'Column')
BEGIN
// Dependency management logic ...
ALTER TABLE [dbo].[Table] DROP COLUMN [Column]
END
我知道可以通过继承SqlServerMigrationSqlGenerator
来自定义迁移SQL。我的尝试未能将默认的删除列逻辑包装在一个IF
块中。请参见以下示例:
public class CustomSqlServerMigrationSqlGenerator: SqlServerMigrationSqlGenerator
{
/// <summary>
/// Drop column only if it exists.
/// </summary>
protected override void Generate(System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation)
{
using (var writer = Writer())
{
writer.WriteLine(
"IF EXISTS (SELECT * FROM sys.columns WHERE object_id = Object_id('{0}') AND name = '{1}')",
dropColumnOperation.Table,
dropColumnOperation.Name);
writer.WriteLine("BEGIN");
Statement(writer);
}
// Default drop column logic
base.Generate(dropColumnOperation);
using (var writer = Writer())
{
writer.WriteLine("END");
Statement(writer);
}
}
}
参考资料:
protected override void Generate(MigrationOperation migrationOperation)
方法,而不是具有相同名称但带有DropColumnOperation
参数的方法:if (operation as DropColumnOperation)
。 - Slava Utesinov