Efcore: Script-Migration -idempotent creates script with errors

[sdpollack]

I'm trying to create an idempotent migration script (as I've done previously). The resulting script won't load due to errors.

Steps to reproduce

1) At the VS Package Manager Console, issue command "Script-Migration -idempotent"
2) Connect to a database running an earlier migration.
3) run the script.

Loading the script in VS shows 3 errors. Running the script results in 5 errors.

sd5bgmh0.txt

Msg 156, Level 15, State 1, Line 792
Incorrect syntax near the keyword 'procedure'.
Msg 156, Level 15, State 1, Line 1255
Incorrect syntax near the keyword 'procedure'.
Msg 137, Level 15, State 2, Line 1268
Must declare the scalar variable "@unitId".
Msg 156, Level 15, State 1, Line 1306
Incorrect syntax near the keyword 'procedure'.
Msg 137, Level 15, State 2, Line 1324
Must declare the scalar variable "@parentUnitId".
Msg 102, Level 15, State 1, Line 1333
Incorrect syntax near ')'.

Further technical details

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer 3.1.1
Target framework: .NET Core 3.1
Operating system: Windows 10 Enterprise 1909
IDE: (e.g. Visual Studio 2019 16.3) VS 16.4.2

[ajcvickers]

Could be a dupe of #12911.
/cc @bricelam

[bricelam]

Not exactly a dupe. CREATE PROCEDURE calls are tricky since they need to be the first statement in a batch. EF6 wraps them inside EXEC:

EXECUTE('
    CREATE PROCEDURE ...
')

[ajcvickers]

Linked to docs: https://github.com/aspnet/EntityFramework.Docs/issues/694

[bricelam]

@sdpollack In case I was too subtle, you can work around this by changing your migration to use EXEC:

migrationBuilder.Sql(@"
    EXECUTE('
        CREATE PROCEDURE ...
    ')
");

[ajcvickers]

Tracked by documentation and #245