dotnet-ef migrations script omits required GO separators in EF Core 9, causing SQL batch errors

[redJ4y]

Bug description

We effectively do stored procedure migrations like:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(
                """
                CREATE OR ALTER PROCEDURE SomeProcedure
                AS
                    BEGIN TRANSACTION
                    -- Example operation
                    INSERT INTO ExampleTable (Column1, Column2)
                    VALUES ('Value1', 'Value2');
                    COMMIT TRANSACTION
                GO
                """
            );
        }

Running dotnet-ef migrations script version 8.0.8 results in the following script:

BEGIN TRANSACTION;
GO

CREATE OR ALTER PROCEDURE SomeProcedure
AS
    BEGIN TRANSACTION
    -- Example operation
    INSERT INTO ExampleTable (Column1, Column2)
    VALUES ('Value1', 'Value2');
    COMMIT TRANSACTION
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'ExampleStoredProcedureMigration', N'8.0.8');
GO

COMMIT;
GO

However, running the same command on version 9.0.2 results in the following script:

BEGIN TRANSACTION;
CREATE OR ALTER PROCEDURE SomeProcedure
AS
    BEGIN TRANSACTION
    -- Example operation
    INSERT INTO ExampleTable (Column1, Column2)
    VALUES ('Value1', 'Value2');
    COMMIT TRANSACTION

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'ExampleStoredProcedureMigration', N'9.0.2');

COMMIT;
GO

Which is invalid due to the error [S0001][111] 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

We can work around the issue for now using migrationBuilder.Sql(sql, suppressTransaction: true), but that is not ideal.

This regression does not impact migration bundles—only the dotnet-ef migrations script tool appears to be affected.

Your code

    /// <inheritdoc />
    public partial class ExampleStoredProcedureMigration : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(
                """
                CREATE OR ALTER PROCEDURE SomeProcedure
                AS
                    BEGIN TRANSACTION
                    -- Example operation
                    INSERT INTO ExampleTable (Column1, Column2)
                    VALUES ('Value1', 'Value2');
                    COMMIT TRANSACTION
                GO
                """
            );
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // ...
        }
    }

EF Core version

9.0.2

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 9

Operating system

Windows 11