实体框架6:“迁移删除"列(如果存在)

实体框架6:“迁移删除

问题描述：

在迁移操作中删除列时，如何在尝试删除列之前先生成SQL以检查该列的存在?

During a migration operation to drop a column, how can one generate SQL to check for the column's existence first before attempting to drop it?

对于放置列操作，实体框架当前会生成这样的sql来删除列:

For a drop column operation Entity framework currently generates sql like this to drop a column:

// Migration Operation:
DropColumn("dbo.Table", "Column");

// TSQL generated:
// Dependency management logic ...
ALTER TABLE [dbo].[Table] DROP COLUMN [Column]

如何更改SQL以首先检查该列是否存在:

How can one alter the SQL to check for the column's existence first:

// 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 块中.请参见下面的示例:

I know that one can customize migration SQL by inheriting from SqlServerMigrationSqlGenerator. My attempts to do so failed to wrap the default drop column logic in an IF block. See example below:

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

来源:

实体框架6:如何覆盖SQL生成器?
Romiller首先定制代码迁移
EF 6 SqlServerMigrationSqlGenerator删除列逻辑

答

如果您已经正确配置了 CustomSqlServerMigrationSqlGenerator ，那么在执行 Update-Database 的过程中，您应该会经历此错误消息:

If you have correctly configured your CustomSqlServerMigrationSqlGenerator then during execution of an Update-Database you should have experienced this error message:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'BEGIN'.

问题在于您已经构造并执行了3个部分语句，默认情况下对 Statement()的调用将作为单个批处理操作执行，并且直到实际的 DropColumn 语句和 End .

The problem is that you have constructed and executed 3 partial statements, a call to Statement() by default executes as a single batch operation, and your batch isn't valid syntax until the actual DropColumn statement and End are included in the statement.

由于基本实现不允许我们通过文本编写器(我们需要的方法标记为 protected )，因此我们被迫要做的是完全忽略基本实现:>

As the base implementation does not let us pass through the text writer (the method we need for that is marked protected) what we are forced to do instead is ignore the base implementation altogether:

/// <summary>
/// Drop column only if it exists.
/// </summary>
/// <remarks>This helps when we're stuffed up a previous migration or someone has already executed the drop in the DB direct.</remarks>
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");

        // Base Implementation, increase the indentation
        writer.Indent++;
        writer.WriteLine("ALTER TABLE {0}", Quote(dropColumnOperation.Table));
        writer.WriteLine("DROP COLUMN {0}", Quote(dropColumnOperation.Name));
        writer.Indent--;

        writer.WriteLine("END");

        Statement(writer);
    }
}

如果没有看到当前错误，则可能是 CustomSqlServerMigrationSqlGenerator 未正确注册，请确保在Configuration中Configuration类的构造函数中已对其进行 set 设置.cs例如:

If you are not seeing your current error, perhaps the CustomSqlServerMigrationSqlGenerator was not registered correctly, make sure you have set it in the constructor of the Configuration class in Configuration.cs for example:

public Configuration()
{
    AutomaticMigrationsEnabled = false;
    AutomaticMigrationDataLossAllowed = false;
    // Register the Customized SQL Generator to use
    this.SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());
}

如果您需要调试此过程，请遵循与本文类似的本文中的建议，您可以在生成器类的构造函数中放置一个断点:

If you need to debug this process, follow the advice in this post that is a similar scenario to this one, you can put a break point in the constructor of your generator class:

public class CustomSqlServerMigrationSqlGenerator: SqlServerMigrationSqlGenerator
{
    public CustomSqlServerMigrationSqlGenerator()
        : base()
    {
        if (!System.Diagnostics.Debugger.IsAttached)
            System.Diagnostics.Debugger.Launch();
    }

    ...
}

实体框架6:“迁移删除"列(如果存在)

相关推荐