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?

Why do you need GO? It is not a SQL statement - it is command for SQL tools. — Ladislav Mrnka
– Ladislav Mrnka, Commented Jul 18, 2012 at 20:30
@LadislavMrnka - I've defined a perfectly plausible use-case (and it's associated problems) in this question: stackoverflow.com/q/13589986/476786 — bPratik
– bPratik, Commented Nov 27, 2012 at 17:54

Peter Jaffe · Accepted Answer · 2012-09-19 12:51:17Z

15

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.

answered Sep 19, 2012 at 12:51

Peter Jaffe

3113 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stanley.Goldman Over a year ago

I'm glad to know at least one other person is having this problem. In the end I ended up getting more comfortable having EF run the migrations and that is my solution to the problem.

jkoreska Over a year ago

+1 for suggestion a. It felt dirty editing the script (required for deploy to Prod) so I went back and was able to split up my changes across several migrations. Thanks!

bwerks Over a year ago

I am both comforted and disappointed by this answer. I'm comforted to know it's not just me, and I'm disappointed that the problem I'm seeing is a real one.

BenTaylor · Accepted Answer · 2015-07-02 10:10:06Z

9

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

answered Jul 2, 2015 at 10:10

BenTaylor

5014 silver badges15 bronze badges

4 Comments

Stanley.Goldman Over a year ago

I'm no longer in a position to test this, but this looks like an awesome solution to me. I'm accepting the answer.

regisbsb Over a year ago

@BenTaylor may I suggest the usage of the base class? public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken) { foreach (var migrationStatement in base.Generate(migrationOperations, providerManifestToken)) { migrationStatement.BatchTerminator = "GO"; yield return migrationStatement; } }

regisbsb Over a year ago

Sorry of the upvote. It does not work with stored procedures

yurish Over a year ago

This method does not work. Even so the generated script has statements separated into batches it fails because @CurrentMigration variable declared at the beginning of the script now spans several batches.

Jesper Kristensen · Accepted Answer · 2013-02-14 07:41:56Z

2

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.

answered Feb 14, 2013 at 7:41

Jesper Kristensen

3004 silver badges9 bronze badges

1 Comment

D.Kempkes Over a year ago

Have you perphaps got an example of this?

regisbsb · Accepted Answer · 2015-11-20 12:35:40Z

0

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.

answered Nov 20, 2015 at 12:35

regisbsb

3,8143 gold badges39 silver badges42 bronze badges

Comments

Serge Intern · Accepted Answer · 2017-08-31 06:10:21Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related