Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation

[meghanmae]

Problem

After upgrading from EF Core 8 to EF Core 9, we encountered a regression where using migrationBuilder.DropTable followed by recreating the same table in a single migration causes issues. The issue seems to be resolved when switching to a raw SQL command (migrationBuilder.Sql("DROP TABLE ...");) to drop the table instead of using migrationBuilder.DropTable.

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Assuming table Employees was previously created...

            // DropTable: causes the migration to fail
            migrationBuilder.DropTable(
                name: "Employees");

            // Uncomment this (and comment out DropTable) to run the migration successfully 
            //migrationBuilder.Sql("""
            //    Drop Table Employees;
            //    """);

            // Recreate table
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    EmployeeId = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.EmployeeId);
                });
        }

Exception & stack tack traces

System.Collections.Generic.KeyNotFoundException: 'The given key '(Employees, )' was not present in the dictionary.'

 	[Exception] System.Private.CoreLib.dll!System.ThrowHelper.ThrowKeyNotFoundException<T>(T key)	Unknown
 	[Exception] System.Private.CoreLib.dll!System.Collections.Generic.Dictionary<TKey, TValue>.this[TKey].get(TKey key)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.SqlServer.dll!Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.RewriteOperations(System.Collections.Generic.IReadOnlyList<Microsoft.EntityFrameworkCore.Migrations.Operations.MigrationOperation> migrationOperations, Microsoft.EntityFrameworkCore.Metadata.IModel model, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.SqlServer.dll!Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(System.Collections.Generic.IReadOnlyList<Microsoft.EntityFrameworkCore.Migrations.Operations.MigrationOperation> operations, Microsoft.EntityFrameworkCore.Metadata.IModel model, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Microsoft.EntityFrameworkCore.Migrations.Migration migration, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GetMigrationCommandLists.AnonymousMethod__2()	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(Microsoft.EntityFrameworkCore.DbContext context, string targetMigration, Microsoft.EntityFrameworkCore.Migrations.MigrationExecutionState state, bool useTransaction)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate.AnonymousMethod__20_1(Microsoft.EntityFrameworkCore.DbContext c, (Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator Migrator, string TargetMigration, Microsoft.EntityFrameworkCore.Migrations.MigrationExecutionState State, bool UseTransaction) s)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute.AnonymousMethod__0(Microsoft.EntityFrameworkCore.DbContext context, TState state)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation<TState, TResult>(System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> operation, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> verifySucceeded, TState state)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute<TState, TResult>(TState state, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, TResult> operation, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> verifySucceeded)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(string targetMigration)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade)	Unknown
>	[Exception] MyApp.Web.dll!Program.<Main>$(string[] args) Line 195	C#

Versions

EF Core version: 9.0.0
Database provider: Microsoft SQL Server (LocalDB)
Target framework: .NET 9.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.12.0

[devedse]

We are running into the same issue on our customer code base. The workaround also works for us.

[roji]

Poaching (have a bit of extra capacity to help with 9.0 regressions for the 9.0.1 cutoff). Confirmed regression from 8.0 to 9.0.

Repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.MigrateAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Generate an initial migration for the above, then add another empty migration, and paste the following into it:

            migrationBuilder.DropTable(
                name: "Blogs");

            migrationBuilder.CreateTable(
                name: "Blogs",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Blogs", x => x.Id);
                });

[roji]

@meghanmae and @AnthonyDewhirst, can you please confirm how you ended up with a single migration that has a DropTable and then a CreateTable on the same migration? Is this the result of manual editing of the migration, or did EF generate this itself? If it's the latter, can you provide more info on that?

[AnthonyDewhirst]

Hi @roji apologise once more...

By the time I got this all fixed which included rolling back a migration in code to see what 9.0.0 generated, I realise that there should never have been a drop table. This was a case of 2 of my devs doing competing work and checking the code in and fixing it, by instead of deleting the migrations and crafting one again, by hand manipulating the files.

So, yep, our bad, not something should have ever made it passed a PR but had worked previously. I used the raw sql work around. But it's on my todo list to go generate the migrations properly. It's live so I need to be careful, but I think it's straight forward.

So from my perspective, this is user error and not an issue we have with ef core.

I did mean to write this in early and was also on my todo list.

Thanks for the great work, much appreciated!

[molinch]

@roji Is there a prerelease package that would have the fix applied for it?

[NKnusperer]

Can we expect a fix for 9.0 ?

[roji]

@maumar to answer regarding backporting (and/or a workaround) - the current fix looks a bit large and risky for that.

[TimPurdum]

We ran into this as well. A common scenario where we have to do Drop/Add manually in a migration is that EF Core will occasionally build invalid Updates, like when a column changes type, that would fail on run. I don't have an exact repro, but hopefully that makes sense.

[codehunter13]

same problem over here

[roji]

@maumar ping

[maumar]

We could port the fix that makes the temporal information dictionary access more robust - that would solve the problems for non-temporal scenarios that used to work but got hit by the temporal table re-design. However the proper fix for the temporal table issues is unrealistic to be ported to 9 IMO. It's a big re-design of how we handle it, likely including some API changes (to flow the starting model)

[roji]

Yeah, IMHO it seems to be a regression that enough people are hitting to warrant a targeted fix (but can discuss in design).

[maumar]

reopening for potential patch

[Dennis-N8]

Was waiting for the fix, but it probably wasn't in today's Visual Studio update (still get that error).

[maumar]

@Dennis-N8 the fix will be available for EF Core 10 Preview 3 and EF Core 9.0.5