Tikkes Tikkes - 20 days ago 6
SQL 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.

Answer

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.

Comments