Regression from EF Core 8 to 9: Invalid migration scripts involving multiple Temporal Tables

[CedNZ]

Problem

Migration scripts involving Temporal Tables are invalid due to duplicate @historyTableSchema definition

Include your code

Sample project https://github.com/CedNZ/EF9-migration-bug

public class SampleDbContext : DbContext
{
    public DbSet<One> Ones => Set<One>();
    public DbSet<Two> Twos => Set<Two>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<One>(e =>
        {
            e.ToTable(nameof(Ones), builder => builder.IsTemporal());
        });

        modelBuilder.Entity<Two>(e =>
        {
            e.ToTable(nameof(Twos), builder => builder.IsTemporal());
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=EF9_historyTableSchema;Trusted_Connection=True;");
    }
}

Generate migration script:

dotnet ef migrations script --output C:\Temp\Migration.sql

Include verbose output

Problematic Sql declares @historyTableSchema twice

BEGIN TRANSACTION;
DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Ones] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    CONSTRAINT [PK_Ones] PRIMARY KEY ([Id]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[OnesHistory]))');

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Twos] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    CONSTRAINT [PK_Twos] PRIMARY KEY ([Id]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[TwosHistory]))');

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20241122014357_SampleMigration', N'9.0.0');

COMMIT;
GO

Include provider and version information

EF Core version: 9.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .Net 9 Operating system: Windows 11 23H2 IDE: Visual Studio 2022 17.13 Preview 1.0