Adding 'GO' statements to Entity Framework migrations

Question

Adding 'GO' statements to Entity Framework migrations

So I have an application with a ton of migrations made by Entity framework. We want to get a script for all the migrations at once and using the -Script tag does work fine.

However...it does not add GO statements in the SQL giving us problems like Alter view should be the first statement in a batch file...

I have been searching around and manually adding Sql("GO"); help with this problem but only for the entire script. When I use the package console manager again it returns an exception.

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

Is there a way to add these GO tags only when using the -Script tag? If not, what is a good approach for this?

Note: we have also tried having multiple files but since we have so many migrations, this is near impossible to maintain every time.

sql

entity-framework

ef-migrations

asked on Stack Overflow Jun 14, 2013 by

Tikkes

5 Answers

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')")

answered on Stack Overflow Dec 3, 2013 by

Colin • edited Mar 14, 2019 by

Colin

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.

answered on Stack Overflow Jun 14, 2013 by

Skyp

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

answered on Stack Overflow Nov 14, 2016 by

Yepeekai

The easiest way is to add /**/ before the GO statement.

answered on Stack Overflow Dec 24, 2019 by

Ivan Stus

Just replace the current statement with a .Replace("GO", "");

answered on Stack Overflow Jan 23, 2015 by

Wouter Van Ranst

User contributions licensed under CC BY-SA 3.0