Using multiple SQL statements in EF migration - how to use GO to run in SSMS?

ef-migrations entity-framework-6 sql-server

Question

I am renaming some columns using EF6 migrations, and need to update several functions, views and stored procedures which use the columns. I want to add these as separate Sql() calls within different private methods so the overall migration is easier to read.

When I do this, I can run the migration using update-database without any problems, but if I generate scripts (using update-database -script) the script doesn't run fully because 'ALTER FUNCTION' must be the first statement in a query batch..

I have tried putting a GO at the end of each Sql() statement, but when EF generates the script it removes the GO. If I try with two of them (see below), EF gives the error The argument 'sql' cannot be null, empty or contain only white space.

ALTER FUNCTION myFunc
...
GO
GO

I want to be able to run the migration through EF, or by generating a script, and for both to work without needing to change any configuration or manually modify the script.

1
0
4/26/2019 4:45:54 PM

Accepted Answer

I found an odd fix to this problem:

GO
--any arbitrary comment here
GO

My guess is that EF will remove GO from the end of a Sql() call:

Sql(@"
--actual SQL here
GO");

Including the arbitrary comment seems to mean that only the final GO is removed, but the initial one is kept. This works for me regardless of whether I run the migration through EF or generate a script to run in SSMS.

1
4/26/2019 4:46:07 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow