efcore: Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation
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
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 5
- Comments: 18 (10 by maintainers)
Commits related to this issue
- Temporary fix for DropTable/CreateTable issue around temporal tables Fixes #35162 — committed to roji/efcore by roji 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Problem was that in 9.0 we changed how we process temporal tables, ... — committed to dotnet/efcore by maumar 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Problem was that in 9.0 we changed how we process temporal tables, ... — committed to dotnet/efcore by maumar 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Problem was that in 9.0 we changed how we process temporal tables, ... — committed to dotnet/efcore by maumar 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Problem was that in 9.0 we changed how we process temporal tables, ... — committed to dotnet/efcore by maumar 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Problem was that in 9.0 we changed how we process temporal tables, ... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Port of https://github.com/dotnet/efcore/pull/35764 Fixes #35162 Descriptio... — committed to dotnet/efcore by maumar 2 years ago
- Targeted fix for #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation (#35764) Problem was that in 9.0 we changed how we process temporal... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation Port of https://github.com/dotnet/efcore/pull/35764 Fixes #35162 Descriptio... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35162 - Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation (#35776) Port of https://github.com/dotnet/efcore/pull/35764 Fixes #35162 D... — committed to dotnet/efcore by maumar 2 years ago
@Dennis-N8 the fix will be available for EF Core 10 Preview 3 and EF Core 9.0.5
Yeah, IMHO it seems to be a regression that enough people are hitting to warrant a targeted fix (but can discuss in design).
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!