dotnet / dotnet/efcore

SqlServer: KeyNotFoundException in RewriteOperations when a temporal table is renamed into the model's default schema

Open
#38,601 2 comments 0 reactions 1 assignee Claimed by @AndriySvyryd View on GitHub
area-migrations area-sqlserver area-temporal-tables regression
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Bug description

Generating a migration script (or applying migrations at runtime) throws `KeyNotFoundException` on SQL Server when a migration **renames a temporal table from an unspecified schema (`null`) into the schema that is also the model's default schema** (e.g. `modelBuilder.HasDefaultSchema("dbo")`).

This is the exact operation set EF produces when you take an existing model that has a temporal table with no explicit schema and later add `HasDefaultSchema("dbo")`. EF emits `EnsureSchema("dbo")` + `RenameTable(null → dbo)` + `AlterTable(dbo)` for the temporal table, and script generation fails with:

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

The failure is in the stock `SqlServerMigrationsSqlGenerator` — no custom code required. It reproduces with a plain `dotnet ef migrations script` and does **not** need a database connection.

The essential trigger is: **`RenameTable` of a temporal table from schema `null` → the default schema, followed by any further table operation on that (now `dbo`) table.**

#### Root cause

A **raw-vs-defaulted schema key inconsistency** in `SqlServerMigrationsSqlGenerator.RewriteOperations`.

The method builds a lookup keyed by `(TableName, Schema)` and **adds / looks up using the _raw_ schema** exactly as written on the operation (which may be `null`):

```csharp
var rawSchema = tableMigrationOperation.Schema; // e.g. null
var schema = rawSchema ?? model?.GetDefaultSchema(); // e.g. "dbo"
...
temporalInformation = temporalTableInformationMap[(tableName, rawSchema)]; // keyed by RAW schema
```

But the two `Remove` calls use the **_defaulted_ schema**. On the `RenameTableOperation` branch (`release/10.0`, lines 2850–2851):

```csharp
// add new entry under the RAW new schema:
temporalTableInformationMap[(renameTableOperation.NewName!, renameTableOperation.NewSchema)] = temporalInformation;
// remove old entry under the DEFAULTED old schema:
temporalTableInformationMap.Remove((tableName, schema)); // schema = rawSchema ?? GetDefaultSchema()
```

When a table is renamed from `null` → the default schema `"dbo"`:

1. the new entry is inserted under `("Widgets", "dbo")` (raw `NewSchema`), then
2. the removal key is `("Widgets", rawSchema ?? GetDefaultSchema())` = `("Widgets", "dbo")` — **the same key that was just inserted**, so it is immediately deleted.

The subsequent `AlterTable` / `AlterColumn` on `dbo.Widgets` then does `temporalTableInformationMap[("Widgets", "dbo")]` and throws. (The `DropTableOperation` branch has the same defaulted-key `Remove` and is likely affected by the equivalent scenario.)

#### When it was introduced

The `temporalTableInformationMap` mechanism **does not exist in `release/8.0`** — EF 8 handled temporal rewriting differently and never hits this path, which is why this model change scripted fine on EF Core 8. The dictionary-based rewrite (with this raw/defaulted key mismatch) was introduced in **EF Core 9.0** and is still present in 10.0 and `main` (11.0 previews).

#### Suggested fix

Make the `Remove` keys consistent with insert/lookup — remove by the **raw** schema (`Remove((tableName, rawSchema))`), or normalize *all* keys (insert, lookup, and remove) to the defaulted schema. The insert/lookup/remove keying must agree.

#### Workaround

Replace the offending migration's `Up()` with equivalent raw SQL via `migrationBuilder.Sql(...)`. Raw `SqlOperation`s are not `ITableMigrationOperation`, so they bypass the temporal-rewrite path entirely and avoid the crash.

### Your code

Minimal, self-contained repro (no database required).

Steps:

1. With `HasDefaultSchema` **commented out**: `dotnet ef migrations add Init`
2. **Uncomment** `modelBuilder.HasDefaultSchema("dbo");`, then: `dotnet ef migrations add MoveToDbo`
3. `dotnet ef migrations script` → crash

```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace Ef10Repro;

public class Widget
{
public int Id { get; set; }
public string? Name { get; set; }
}

public class AppDbContext : DbContext
{
public DbSet Widgets => Set();

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=Ef10Repro");

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// STEP 2 (the trigger): add this line, then add a second migration.
// modelBuilder.HasDefaultSchema("dbo");

modelBuilder.Entity().ToTable("Widgets", t => t.IsTemporal());
}
}

public class Factory : IDesignTimeDbContextFactory
{
public AppDbContext CreateDbContext(string[] args) => new();
}

// The generated MoveToDbo migration that triggers the crash:
//
// migrationBuilder.EnsureSchema(name: "dbo");
//
// migrationBuilder.RenameTable(
// name: "Widgets", newName: "Widgets", newSchema: "dbo")
// .Annotation("SqlServer:IsTemporal", true)
// .Annotation("SqlServer:TemporalHistoryTableName", "WidgetsHistory")
// .Annotation("SqlServer:TemporalHistoryTableSchema", null)
// .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
// .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
//
// migrationBuilder.AlterTable(name: "Widgets", schema: "dbo")
// .Annotation("SqlServer:IsTemporal", true)
// .Annotation("SqlServer:TemporalHistoryTableSchema", "dbo")
// // ...temporal annotations...
// .OldAnnotation("SqlServer:IsTemporal", true)
// .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null);
```

### Stack traces

```
System.Collections.Generic.KeyNotFoundException: The given key '(Widgets, dbo)' was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.RewriteOperations(IReadOnlyList`1 migrationOperations, IModel model, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, MigrationsSqlGenerationOptions options, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigrationImpl(String fromMigration, String toMigration, Boolean idempotent, Boolean noTransactions, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
```

The same exception occurs at runtime via `Migrator.Migrate()`, which also goes through `RewriteOperations`.

### Verbose output

`dotnet ef migrations script --verbose` (trimmed to the relevant portion):

```
Using DbContext factory 'Factory'.
Using context 'AppDbContext'.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Generating up script for migration '20260709085127_Init'.
Generating up script for migration '20260709085146_MoveToDbo'.
'AppDbContext' disposed.
System.Collections.Generic.KeyNotFoundException: The given key '(Widgets, dbo)' was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.RewriteOperations(...)
...
The given key '(Widgets, dbo)' was not present in the dictionary.
```

The `Init` migration scripts fine; the crash happens while generating the up-script for `MoveToDbo`.

### EF Core version

10.0.9 (also reproduces on 9.0.x and on `main` / 11.0 previews; not present in 8.0)

### Database provider

Microsoft.EntityFrameworkCore.SqlServer

### Target framework

.NET 10.0

### Operating system

Windows

### IDE

N/A — reproduced with the `dotnet ef` CLI (dotnet-ef 10.0.9)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.