Entity Framework Migrations: Including Go statement only in -Script output

Question

As part of planning an Entity Framework migration, in order to debug data movement, I would often use the -Script parameter to generate the script.

I could then take this script to Query Analyzer and wrap it in a transaction in order to test it manually.

I came across a situation where we needed a Go statement to execute the script properly. The following code was added to the migration in order to output a Go in the proper place.

Sql("GO");

This adds a GO statement in the proper position when -Script is used. But when -Script isn't used. I get the exception...

System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'GO'.

Is there a safe way to add a Go command to the script?

Answer 1

I hit the exact same situation recently. My EF code migrations often introduce a new table or column, and then I also put data migrations using Sql(...) into those migrations that sometimes want to reference the new table/column. As you pointed out, when run as an EF code migration, each statement appears to be issued as a discrete batch to the DB, and hence there aren't any issues. However, to satisfy the production deployment constraints, we turn a set of Code migrations from a sprint into a single script (using -Script) to present a single aggregate SQL script migration for the deployment team. This script file sometimes fails, as you pointed out, due to it attempting to process a single T SQL batch from a single code migration where later statements attempt to refer to structure that was only defined earlier in the batch.

I don't particularly like either of the two approaches I've taken for now to mitigate this, but here they are:

a. If I happen to be thinking about it at the time, I split the code migration into two migrations, so that when they are scripted, they are in two (or more) separate batches. I don't like this because there is no feedback during the development of the Code Migration that this is necessary, and hence it seems error prone.

b. When I generate the aggregate scripts, I run them against a scratch DB to prove them out, and I end up manually injecting "GO" statements where necessary in that script. This is an annoying process to have to go back and do, and results in -Script output that isn't a 100% reflection of the Code Migrations.

I haven't spent much time digging into the source code of EF Code Migrations yet to see if I can understand why it interprets "GO" as a stored proc, and whether there is anything in the source code that would point to a way to provide a directive that would avoid that.

Answer 2

internal sealed class Configuration : DbMigrationsConfiguration<Context>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        const string providerInvariantName = "System.Data.SqlClient";
        SetSqlGenerator(providerInvariantName, new BatchingMigrationSqlGenerator(GetSqlGenerator(providerInvariantName)));
    }

    protected override void Seed(Context context)
    {
    }

}

internal class BatchingMigrationSqlGenerator : MigrationSqlGenerator
{
    private readonly MigrationSqlGenerator migrationSqlGenerator;

    public BatchingMigrationSqlGenerator(MigrationSqlGenerator migrationSqlGenerator)
    {
        this.migrationSqlGenerator = migrationSqlGenerator;
    }

    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        var migrationStatements = migrationSqlGenerator.Generate(migrationOperations, providerManifestToken).ToArray();
        foreach (var migrationStatement in migrationStatements)
        {
            migrationStatement.BatchTerminator = "GO";
        }
        return migrationStatements;
    }
}

Answer 3

I ended up using two different Configuration classes when I run the migrations with and without the -Script parameter. In one of my Configuration classes I wrap its MigrationSqlGenerator in a custom implementation, which adds the GO statements.

Answer 4

I've used:

public class MigrationScriptBuilder : SqlServerMigrationSqlGenerator
{
#if !DEBUG
    protected override void Generate(System.Data.Entity.Migrations.Model.SqlOperation sqlOperation)
    {
        Statement("GO");

        base.Generate(sqlOperation);

        Statement("GO");
    }
#endif
}

So when it's debug it does not crash. And I script from release mode.

Answer 5

This is working for me:

public class MigrationScriptBuilder : SqlServerMigrationSqlGenerator
{
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        var statements = base.Generate(migrationOperations, providerManifestToken);

        statements = statements.SelectMany(s => new[] {
            s,
            new MigrationStatement
            {
                Sql = "GO"
            }
        }).ToList();

        return statements;
    }
}

Which (as seen in other answers) can be used (migration process flow) with this kind of method in the DbContext Configuration:

    public Configuration()
    {
        SetSqlGenerator("System.Data.SqlClient", new MigrationScriptBuilder());
    }