sql server - Entity Framework - how to create sql script with "GO" statements

Question

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?

Answer 1

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.