Entity Framework 6: Migration Drop column if exists

Question

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?

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]

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

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

Sources:

• Entity Framework 6: How to override SQL generator?
• Romiller on customizing code first migrations
• EF 6 SqlServerMigrationSqlGenerator drop column logic

Answer 1

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'.

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.

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

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

    ...
}