pekspro
commented
3 years ago This sounds a bit suspicious. I have done similar things before, and I have not had any issue like that.
I recommend you create a simple project as possible that replicates this issue. Also try with the dotnet ef commands:
https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli
https://docs.microsoft.com/en-us/ef/core/cli/dotnet#dotnet-ef-dbcontext-script
And then publish to Github and report this to https://github.com/dotnet/efcore. If this is bug it is better to fix it in that project. This project is just a wrapper for dotnet ef so there is not much I could do about it :-)
KbKev78
Fuller details on Stack Overflow, where I posted my problem and my workaround.
tl;dr - When the migration uses a raw sql command in a migrationBuilder.SQL() instruction (in my case, that's how I do data transforms), the EF-Migrations-Script-Generator-Task converts that to a real SQL string. Normally that's fine, but if a later update has modified the schema of the database, the whole deployment script will fail to run because that raw SQL doesn't validate ("Invalid Column" errors).
My workaround was to go back through my transform instructions and wrap the SQL command string in a sql EXEC() command. This means the EXEC command is the raw SQL, and the payload instruction remains a string literal and is not subject to parsing unless the migration is being applied.
Suggestion: Wrap all SQL strings found in migrationBuilder.Sql() instructions in sql EXEC() instructions (and escape every literal inside the SQL string - single quote becomes 2x single quotes).
As far as I'm aware it would be that simple. I'm not aware of any SQL instruction that can't be escaped and wrapped that way, but other folks might be able to offer further advice on that.