18

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.

Improve this question

5 Answers 5

Reset to default
21

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This thing allows to fill tables and columns right after creation. Great!
Where is the EXEC function defined please? I am unable to find it.
@MathuSumMut It is standard Sql. I had omitted the quotation marks needed around the Sql string. I've edited the answer to correct the error
This should be the accepted answers. It is also stated here: github.com/dotnet/efcore/issues/12911#issuecomment-831447488
12

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.

Improve this answer

3 Comments

Works perfectly when using the -Script tag. I have to comment out the SetSqlGenerator("..... line however when I dont use it because otherwise I got the exception again. This however saves me a ton of time because it is just commenting out a line in stead of adding Sql("GO"); everywhere. Thanks!
I can't find InsertHistoryOperation. Which assembly is it in?
Ah I think it's renamed in EF 6 to HistoryOperation
10

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;
        }
    }
Improve this answer

3 Comments

This worked for me! I was looking for a way that works both with and without -script argument. Thanks!
This worked for me when EF6 was inexplicably removing a go statement from the end of my stored procedure creation script being inserted via SqlResource. The magic of EF is not something I'm fond of.
hm, interesting, this works perfectly when using update-database -script however in an Azure Deployment Group Task Generate migration SQL script instead of GO terminator EXECUTE('<GUIDSTRING>')-statements appear in the generated code
5

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

Improve this answer

Comments

-4

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.