Adding 'GO' statements to Entity Framework migrations

If you are trying to alter your view using Sql("Alter View dbo.Foos As etc"), then you can avoid the should be the first statement in a batch file error without adding GO statements by putting the sql inside an EXEC command:

Sql("EXEC('Alter View dbo.Foos As etc')")

In order to change the SQL Generated by entity framework migrations you can create a new SqlServerMigrationSqlGenerator

We have done this to add a GO statement before and after the migration history:

public  class MigrationScriptBuilder: SqlServerMigrationSqlGenerator
{
    protected override void Generate(System.Data.Entity.Migrations.Model.InsertHistoryOperation insertHistoryOperation)
    {
        Statement("GO");

        base.Generate(insertHistoryOperation);

        Statement("GO");

    }
}

then add in the Configuration constructor (in the Migrations folder of the project where you DbContext is) so that it uses this new sql generator:

[...]
internal sealed class Configuration : DbMigrationsConfiguration<PMA.Dal.PmaContext>
{
    public Configuration()
    {
        SetSqlGenerator("System.Data.SqlClient", new MigrationScriptBuilder());
        AutomaticMigrationsEnabled = false;
    }
[...]

So now when you generate a script using the -Script tag, you can see that the insert into [__MigrationHistory] is surrounded by GO

Alternatively in your implementation of SqlServerMigrationSqlGenerator you can override any part of the script generation, the InsertHistoryOperation was suitable for us.

Turn out the concept exist deep in the SqlServerMigrationSqlGenerator as an optional argument for Statement(sql, batchTerminator). Here is something based on Skyp idea. It works both in -script mode or not. The GOs are for different operations than for Skyp only because our needs are a little different. You then need to register this class in the Configuration as per Skyp instructions.

    public class MigrationScriptBuilder : SqlServerMigrationSqlGenerator
    {
        private string Marker = Guid.NewGuid().ToString(); //To cheat on the check null or empty of the base generator

        protected override void Generate(AlterProcedureOperation alterProcedureOperation)
        {
            SqlGo();
            base.Generate(alterProcedureOperation);
            SqlGo();
        }
        protected override void Generate(CreateProcedureOperation createProcedureOperation)
        {
            SqlGo();
            base.Generate(createProcedureOperation);
            SqlGo();
        }
        protected override void Generate(SqlOperation sqlOperation)
        {
            SqlGo();
            base.Generate(sqlOperation);
        }

        private void SqlGo()
        {
            Statement(Marker, batchTerminator: "GO");
        }

        public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
        {
            var result = new List<MigrationStatement>();
            var statements = base.Generate(migrationOperations, providerManifestToken);

            bool pendingBatchTerminator = false;
            foreach (var item in statements)
            {
                if(item.Sql == Marker && item.BatchTerminator == "GO")
                {
                    pendingBatchTerminator = true;
                }
                else
                {
                    if(pendingBatchTerminator)
                    {
                        item.BatchTerminator = "GO";
                        pendingBatchTerminator = false;
                    }
                    result.Add(item);
                }
            }

            return result;
        }
    }