How can I override SQL scripts generated by MigratorScriptingDecorator

Question

Using Entity Framework 4.3.1 Code first, and Data Migrations.

I have written a utility to automatically generate the Migration scripts for a target database, using the MigratorScriptingDecorator.

However, sometimes when re-generating the target database from scratch, the generated script is invalid, in that it declares a variable with the same name twice.

The variable name is @var0.

This appears to happen when there are multiple migrations being applied, and when at least two result in a default constraint being dropped.

The problem occurs both when generating the script form code, and when using the Package Manager console command:

Update-Database -Script

Here are the offending snippets form the generated script:

DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'SomeTableName')

and

DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'SomeOtherTableName')

I would like to be able to override the point where it generates the SQL for each migration, and then add a "GO" statement so that each migration is in a separate batch, which would solve the problem.

Anyone have any ideas how to do this, or if I'm barking up the wrong tree then maybe you could suggest a better approach?

Answer 1

So with extensive use of ILSpy and some pointers in the answer to this question I found a way.

Details below fo those interested.

Problem

The SqlServerMigrationSqlGenerator is the class ultimately responsible for creating the SQL statements that get executed against the target database or scripted out when using the -Script switch in the Package Manager console or when using the MigratorScriptingDecorator.

Workings

Examining the Genearate method in the SqlServerMigrationSqlGenerator which is responsible for a DROP COLUMN, it looks like this:

protected virtual void Generate(DropColumnOperation dropColumnOperation)
{
    RuntimeFailureMethods
        .Requires(dropColumnOperation != null, null, "dropColumnOperation != null");
    using (IndentedTextWriter indentedTextWriter = 
        SqlServerMigrationSqlGenerator.Writer())
    {
        string value = "@var" + this._variableCounter++;
        indentedTextWriter.Write("DECLARE ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" nvarchar(128)");
        indentedTextWriter.Write("SELECT ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" = name");
        indentedTextWriter.WriteLine("FROM sys.default_constraints");
        indentedTextWriter.Write("WHERE parent_object_id = object_id(N'");
        indentedTextWriter.Write(dropColumnOperation.Table);
        indentedTextWriter.WriteLine("')");
        indentedTextWriter.Write("AND col_name(parent_object_id, 
                                                       parent_column_id) = '");
        indentedTextWriter.Write(dropColumnOperation.Name);
        indentedTextWriter.WriteLine("';");
        indentedTextWriter.Write("IF ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(" IS NOT NULL");
        indentedTextWriter.Indent++;
        indentedTextWriter.Write("EXECUTE('ALTER TABLE ");
        indentedTextWriter.Write(this.Name(dropColumnOperation.Table));
        indentedTextWriter.Write(" DROP CONSTRAINT ' + ");
        indentedTextWriter.Write(value);
        indentedTextWriter.WriteLine(")");
        indentedTextWriter.Indent--;
        indentedTextWriter.Write("ALTER TABLE ");
        indentedTextWriter.Write(this.Name(dropColumnOperation.Table));
        indentedTextWriter.Write(" DROP COLUMN ");
        indentedTextWriter.Write(this.Quote(dropColumnOperation.Name));
        this.Statement(indentedTextWriter);
    }
}

You can see it keeps track of the variables names used, but this only appears to keep track within a batch, i.e. a single migration. So if a migratin contains more than one DROP COLUM the above works fine, but if there are two migrations which result in a DROP COLUMN being generated then the _variableCounter variable is reset.

No problems are experienced when not generating a script, as each statement is executed immediately against the database (I checked using SQL Profiler).

If you generate a SQL script and want to run it as-is though you have a problem.

Solution

I created a new BatchSqlServerMigrationSqlGenerator inheriting from SqlServerMigrationSqlGenerator as follows (note you need using System.Data.Entity.Migrations.Sql;):

public class BatchSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate
       (System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation)
    {
        base.Generate(dropColumnOperation);

        Statement("GO");
    }
}

Now to force the migrations to use your custom generator you have two options:

1. If you want it to be integrated into the Package Manager console, add the below line to your Configuration class:

      SetSqlGenerator("System.Data.SqlClient", 
                      new BatchSqlServerMigrationSqlGenerator());
   

2. If you're generating the script from code (like I was), add a similar line of code to where you have your Configuration assembly in code:

   migrationsConfiguration.SetSqlGenerator(DataProviderInvariantName, 
                      new BatchSqlServerMigrationSqlGenerator());