EF5 Migrations - Duplicate/Re-defined variable bug when dropping constraints | Issue with usage of SQL GO command

Question

Background:

We have a project that uses ef-migrations containing multiple (read ~60) migrations created over a long period of development. Naturally, some of these migrations also involve:

dropping constraints^1,2
creating triggers

All is unicorns and rainbows when we run

Update-Database

because each migration runs as a separate batch. But when creating SQL Scripts for these migrations using

Update-Database -Script

we encountered a few issues as described below:

Problem 1:

When dropping multiple constraints across multiple migration files, the script generated by EF tends to re-declare variables that it uses for dropping. This is because it ensures uniqueness of variable names within the same migration file, but on change of file, it resets the counter, thus overlapping the names.

Problem 2:

SQL enforces that CREATE TRIGGER is always the first statement in a batch. When the script is generated, EF is oblivious to the contents of Sql("CREATE TRIGGER ... "); and thus doesn't treat it any specially. Thus the statement may appear right in the middle of a script file, and error out.

Solution: (or so we thought!)

A common/common-sense solution to the two problems is to insert Begin/End the sql batch at the right places. Manually doing this would make me a very rich man, so that is not an efficient solution.

Instead, we used the technique provided by @DavidSette. Creating a new BatchSqlServerMigrationSqlGenerator inheriting from SqlServerMigrationSqlGenerator which effectively overrides dropColumnOperation and sqlOperation and then forcing a GO statement around the sensitive ones as such:

protected override void Generate (System.Data.Entity.Migrations.Model.DropColumnOperation dropColumnOperation)
{
    base.Generate(dropColumnOperation);
    Statement("GO");
}

Boo Boo:

This solution breaks running Update-Database without the -Script flag with the following error:

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

Which was added by our custom generator. Now I'm not certain why, but there should be a pretty good reason EF doesn't recognise GO!

More Info:

Full error:

Applying code-based migration: 201205181406363_AddTriggerForOverlap.
GO
System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'GO'.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
    at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
    at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
    at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
    at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
    at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
    at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
    at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
ClientConnectionId:ac53af4b-1f9b-4849-a0da-9eb33b836caf
Could not find stored procedure 'GO'.

So basically fixing the scripts breaks an essential command. Please help me decide which is the lesser of the two evils!

Amazing work on this post. I'm surprised it doesn't have more votes! — bwerks, Jun 07 '13 at 16:57

score 2 · Answer 1 · answered Mar 12 '13 at 16:59

I put a Sql("--<GO>"); at the end of each migration. This runs OK as an applied migration and when I script the SQL I just do a find and replace on "--<GO>" to "GO". A bit manual but works for me. You could put Sql("--<GO>"); around your create trigger statements.

score 1 · Answer 2 · answered Nov 27 '12 at 18:52

1

As per msdn

"GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor."

Since you are not using any of the above tools but SqlCommand class to execute your Sql statement Sql Server (and not EF - see the stack trace where the exception originated) is choking on it

answered Nov 27 '12 at 18:52

Pawel

31,342
4
73
104

Yes, I know, which is where the issue is. What is an alternate way to seal a batch? – bPratik Nov 27 '12 at 18:57
What if you did not add "GO" at all - I am not 100% sure but think you can have multiple commands in one SqlCommands... – Pawel Nov 27 '12 at 19:14
2

`GO` is needed because of `CREATE TRIGGER` and repeating variable names across batches. It is explained in detail in the question. – bPratik Nov 27 '12 at 19:22

score 1 · Answer 3 · edited May 23 '17 at 11:53

1

After I experienced the problem first hand..

We decided to create smaller migrations. If the change was big enough that it required a GO command, then the developer was attempting to change too much in a migration. Unfortunately, the only way to control what goes into a migration is to comment out the changes.

I also pondered why I wanted the script so badly. Its not like I didn't trust EF to perform the migration correctly, (provided I tested it first). Ideally I would never and (thankfully) have yet to have a reason to modify it. I've only looked at it a few times when I started using EF Code First.

I don't like this answer, but I don't think it serves a purpose other than debugging a migration for Entity Framework Code First.

edited May 23 '17 at 11:53

Community

1
1

answered Nov 27 '12 at 20:17

Stanley.Goldman

4,317
2
23
25

1

But surely having even smaller units of migrations would mean more `drop constraints` being spread across multiple files and more variable names getting repeated. Would it not make more sense to instead combine multiple migrations into much larger ones so that on script generation, you are faced with a small number of batches which you need to isolate? – bPratik Nov 28 '12 at 02:31
Also, what do you use to deploy db changes to production server if not migrations in it's full glory. I ask this coz of your statement 'I also pondered why I wanted the script so badly'. Are you seriously letting migrations lose on production db directly? – bPratik Nov 28 '12 at 02:34
The intent of making smaller migrations would be to have smaller scripts... executed individually so a GO command isn't required. It is not particularly ideal. – Stanley.Goldman Nov 28 '12 at 13:55
1

Because of all the problems of trying to do it via script. You don't really have much of a choice. I have backups and we do a trial run immediately before we run it on our production database. It took some time getting used to doing things this way, but I have yet to have a problem. If you really don't like it, don't use a framework whose intent is to manage the database structure for you. – Stanley.Goldman Nov 28 '12 at 13:59

score -1 · Answer 4 · answered Nov 11 '13 at 04:29

-1

Just delete all the repetitions of this line:

DECLARE @var0 nvarchar(128)

Only the first declaration is sufficient. Your script will work smoothly! :)

answered Nov 11 '13 at 04:29

Mosh

5,944
4
39
44