2

In EF 6.1 you can run the command:

update-database -script

This generates the full SQL script for one or many migrations. The problem we found is that it does not generate the required "GO" statements for sql server. This is causing the SQL script to fail when it would otherwise succeed if run directly without the "-script" parameter.

Has anyone else run into this?

jhilden
  • 12,207
  • 5
  • 53
  • 76
  • 1
    `GO` is not a SQL statement - it's a **delimiter** that the SQL Server **Management Studio** uses - but it's a tool-specific feature, really - not a SQL standard feature – marc_s Aug 06 '14 at 14:19
  • @marc_s, I agree, but I'm going to take that sql script and run it in mgmt studio (as I assume most everyone else would as well). Have you come up with a better solution? – jhilden Aug 06 '14 at 15:25
  • This guy appears to have a possible answer but there is no example code. http://stackoverflow.com/questions/11548959/entity-framework-migrations-including-go-statement-only-in-script-output/14869986#14869986 – jhilden Aug 06 '14 at 19:38
  • you could always post a comment asking if that code is available for download somewhere... – marc_s Aug 06 '14 at 19:42

2 Answers2

5

You can override the script generation behavior by creating a wrapper class around the SqlServerMigrationSqlGenerator class. This class contains an overloaded Generate() method which takes in arguments that represent the type of script block it's generating. You can override these methods to add "GO" statements before starting new script blocks.

public class ProdMigrationScriptBuilder : SqlServerMigrationSqlGenerator
{
    protected override void Generate(HistoryOperation insertHistoryOperation)
    {
        Statement("GO");
        base.Generate(insertHistoryOperation);
        Statement("GO");
    }

    protected override void Generate(CreateProcedureOperation createProcedureOperation)
    {
        Statement("GO");
        base.Generate(createProcedureOperation);
    }

    protected override void Generate(AlterProcedureOperation alterProcedureOperation)
    {
        Statement("GO");
        base.Generate(alterProcedureOperation);
    }

    protected override void Generate(SqlOperation sqlOperation)
    {
        Statement("GO");
        base.Generate(sqlOperation);
    }
}

You will also need to set this class as the Sql Generator in your Configuration class constructor.

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;

       // Add back this line when creating script files for production migration...
       SetSqlGenerator("System.Data.SqlClient", new ProdMigrationScriptBuilder());

    }

One gotcha to this approach is that while it works great for creating re-usable script files for SQL Server Enterprise Manager, the GO statements do not work when executing migrations locally. You can comment out the SetSqlGenerator line when working locally, then simply add it back when you are ready to create your deployment scripts.

LarzStarz
  • 96
  • 3
1

If you are trying to alter a 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'))

Reference:

https://stackoverflow.com/a/20352867/150342

Community
  • 1
  • 1
Colin
  • 22,328
  • 17
  • 103
  • 197