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:

You do not normally need to do that unless your migrations are messed up. EF does a schema compare from last migration to current migration. If this is SQL Server 2016 there is a new [Drop If Exists statement](https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/) that you could code into a Sql() command. — Steve Greene, Oct 25 '17 at 16:05
@SteveGreene it is used to manage migrations in a team environment. We use the same migration and rescaffold it when changes are made. The use case is to simplify the re-scaffold in development. Once the migration has reached production it doesn't change. The question focuses on custom sql migration generation, not manually adding migration operations like Sql(). There are of course other ways to manage migrations in a team environment, but these don't suit our needs: https://msdn.microsoft.com/en-us/library/dn481501(v=vs.113).aspx. — Hans Vonn, Oct 25 '17 at 16:30
At second article `protected override void Generate(MigrationOperation migrationOperation)` was used instead of method with same name, but with `DropColumnOperation` argument: `if (operation as DropColumnOperation)` — Slava Utesinov, Jan 15 '18 at 06:26
@SteveGreene You are right but it is also so easy to mess up his database or having wrong naming convention when starting from an existing database by using Code First From Database. EF is also far from being perfect. EF is not able to handle all cases of existing database. — Bastien Vandamme, Mar 23 '20 at 02:09

score 2 · Answer 1 · answered Aug 23 '20 at 06:40

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

    ...
}

How would you handle dependencies that needed to be dropped first? — Hans Vonn, Aug 25 '20 at 21:36

Entity Framework 6: Migration Drop column if exists

1 Answers1