Error in Generated Migration Script for dotnet-ef 8.0.11

[costinbanu]

We have a dotnet project that we upgraded from dotnet 7 to dotnet 8. We also updated dotnet-ef from 7.0.2.0 to 8.0.11 in the pipeline that we use. This change has introduced a bug when generating migration scripts

Generated SQL script with version 8.0.11

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'myMigrationName'
)
BEGIN
    EXEC sp_rename N'[dbo].[oldTableName]', N'newTableName', 'OBJECT';
    DECLARE @defaultSchema sysname = SCHEMA_NAME();
    EXEC(N'ALTER SCHEMA [' + @defaultSchema + N'] TRANSFER [dbo].[newTableName];');
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'migrationName2'
)
BEGIN
    EXEC sp_rename N'[dbo].[oldTableName2]', N'newTableName2', 'OBJECT';
    DECLARE @defaultSchema sysname = SCHEMA_NAME();
    EXEC(N'ALTER SCHEMA [' + @defaultSchema + N'] TRANSFER [dbo].[newTableName2];');
END;

This fails because the variable @defaultSchema is declared twice in the same SQL batch. Let's have a look at the old version SQL output:

Generated SQL script with version 7.0.20

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'migrationName2')
BEGIN
    EXEC sp_rename N'[dbo].[oldTableName2]', N'newTableName2';
    DECLARE @defaultSchema sysname = SCHEMA_NAME();
    EXEC(N'ALTER SCHEMA [' + @defaultSchema + N'] TRANSFER [dbo].[newTableName2];');
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'migrationName')
BEGIN
    EXEC sp_rename N'[dbo].[oldTableName]', N'newTableName';
    DECLARE @defaultSchema sysname = SCHEMA_NAME();
    EXEC(N'ALTER SCHEMA [' + @defaultSchema + N'] TRANSFER [dbo].[newTableName];');
END;
GO

Here you can see that besides the order of the migrations and some formatting, the new version is lacking the sql batch separator GO which is, essentially, the source of the problem

Steps to reproduce

• define two EF migrations that both rename a table
• generate migration SQL script with dotnet.ef 8.0.11
• execute SQL script

Expected behavior

The script is executed successfully

Actual behavior

The deployment fails / Azure DevOps pipeline step SqlAzureDacpacDeployment fails with message ##[error]The variable name '@defaultSchema' has already been declared. Variable names must be unique within a query batch or stored procedure.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Provider and version information

EF Core version: 8.0.11
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: private Azure DevOps pipeline agent running on Windows 10 x64
IDE:

[costinbanu]

And the same error happens also when using dotnet-ef version 9.0.0

[costinbanu]

After further testing, I concluded that even if I downgrade the dotnet-ef version the pipeline uses to 7.0.20 (which is known to have worked before), the migration scripts are still missing the batch separator between statements (thus throwing the same error "The variable name '@defaultSchema' has already been declared. Variable names must be unique within a query batch or stored procedure." even if dotnet-ef explicitly uses 7.0.20).

The reason for this might be the fact that the dotnet project that contains the migrations is targeting

    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="9.0.0">

Another hint that might point towards this direction is that running the command dotnet ef migrations script in the pipeline results in this message in the stage output: The Entity Framework tools version '7.0.20' is older than that of the runtime '9.0.0'. Update the tools for the latest features and bug fixes. See https://aka.ms/AAc1fbw for more information.

So, to conclude, our last working configuration was the combination of using dotnet-ef 7.0.20 in the pipeline and targeting Microsoft.EntityFrameworkCore.* 7.0.19 in the project. Once either of these targets a higher version, the migration script is no longer split into independent batches, resulting in the error mentioned above.

[ajcvickers]

@costinbanu The dotnet-ef command is just a thin wrapper to call into the runtime code. So you are effectively still using the tooling from EF Core 9 when you use the 7.x dotnet-ef tool unless you also downgrade the other packages.

[costinbanu]

Makes sense. Then I guess this is an issue within Microsoft.EntityFrameworkCore.SqlServer 9.0.0.

[karl-sjogren]

I just ran into this as well and found a possible workaround (even though it isn't perfect). Creating the migration script as usual removes the GO statements as reported above.

> dotnet ef migrations script --idempotent --context MyContext --project ./src/MyProject/MyProject.csproj --output ./migration-script.sql

BEGIN TRANSACTION;
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20210210142332_MigrationReset'
)
BEGIN
    CREATE TABLE [Companies] (
        [Id] uniqueidentifier NOT NULL,
        [Name] nvarchar(max) NOT NULL,
        [CreatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        [UpdatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        CONSTRAINT [PK_Companies] PRIMARY KEY ([Id])
    );
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20210210142332_MigrationReset'
)
BEGIN
    CREATE TABLE [Projects] (
        [Id] uniqueidentifier NOT NULL,
        [ProjectNumber] nvarchar(25) NOT NULL,
        [ProjectName] nvarchar(255) NOT NULL,
        [OperationsManager] nvarchar(255) NOT NULL,
        [CreatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        [UpdatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        CONSTRAINT [PK_Projects] PRIMARY KEY ([Id])
    );
END;

COMMIT;
GO

But, if I pass the --no-transactions parameter when generating my script it still generates the GO statements. Of course, there are no transactions so if the script fails halfway through we'll be left with a partial migration which isn't desireable.

> dotnet ef migrations script --idempotent --context MyContext --project ./src/MyProject/MyProject.csproj --output ./migration-script.sql --no-transactions

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20210210142332_MigrationReset'
)
BEGIN
    CREATE TABLE [Companies] (
        [Id] uniqueidentifier NOT NULL,
        [Name] nvarchar(max) NOT NULL,
        [CreatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        [UpdatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        CONSTRAINT [PK_Companies] PRIMARY KEY ([Id])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20210210142332_MigrationReset'
)
BEGIN
    CREATE TABLE [Projects] (
        [Id] uniqueidentifier NOT NULL,
        [ProjectNumber] nvarchar(25) NOT NULL,
        [ProjectName] nvarchar(255) NOT NULL,
        [OperationsManager] nvarchar(255) NOT NULL,
        [CreatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        [UpdatedAt] datetime2 NOT NULL DEFAULT (getdate()),
        CONSTRAINT [PK_Projects] PRIMARY KEY ([Id])
    );
END;
GO

[alvicode]

have this problem as well with temporal tables schema names.