EF Core 6 - IsTemporal adds an unnecessary migration when DefaultSchema is configured at the DbContext Level

[jaliyaudagedara]

I am seeing this incorrect behavior with EF Core 6 in relation to TemporalTables.

Consider the below code.

public class MyDbContext : DbContext
{
    private const string DEFAULT_SCHEMA = "app";

    public DbSet<Student> Students { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("<ConnectionString>")
            .EnableSensitiveDataLogging()
            .LogTo(Console.WriteLine, LogLevel.Information);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(DEFAULT_SCHEMA);

        modelBuilder.Entity<Student>(builder =>
        {
            builder.ToTable(nameof(Student), x => x.IsTemporal());
        });
    }
}

public class Student
{
    public int Id { get; set; }

    public string Name { get; set; }
}

Now to reproduce the issue,

1. Add a migration
2. Add just another migration
   

Look at the highlighted line. It drops the annotation for the schema in History table.

I can get around this issue by removing the DefaultSchema configuration from the DbCotext level and introducing it into the table level. But then that has to be done for each table :(

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>(builder =>
    {
        builder.ToTable(nameof(Student), DEFAULT_SCHEMA, x => x.IsTemporal());
    });
}

The sample executable code is attached herewith.
EfCore6-TemporalTables.zip

Environment Info:

EF Core version: 6.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

[ajcvickers]

@maumar Repros on 7.0.0-alpha.1.21566.3 daily build. Servicing consider.

[maumar]

Problem here is that when we build RelationalModel in design time, when we create annotations for table we assign HistoryTableSchema the same as the table schema, if the history table schema wasn't set explicitly (so that table schema and history table schema are in sync). This becomes a problem, when the model differ looks at that RelationalModel and compares it with current model (where history table is not set) and hence generates the migration. When building actual migration sql we compensate for the difference and not generate actual sql code, but the problem persists.

We can fix this by leaving historytableschema as null if it wasn't explicitly set, so that the RelationalModel is the same as current model. However this complicates the migration generation step - now we need to sometimes generate extra migrations, because differ won't detect it. e.g.:

myEntity.ToTable("MyTable", "mySchema", tb => tb.IsTemporal())

changed to:

myEntity.ToTable("MyTable", "myModifiedSchema", tb => tb.IsTemporal())

model differ will only detect change in the table schema but we also need to change schema of the history table. This gets worse if you, say, also change the history table name - the differ doesn't know that the history table schema would be implicitly changed (when the temporal table schema changed), so it generates migration with potentially incorrect schema and we need to correct this.

There are probably more issues around this so we need to add a lot of tests to make sure we don't regress - too risky to patch especially given that there is a simple (but annoying) workaround.

Alternatively we can try a different approach - maybe make it so that in current model, history table schema also follows the table schema, So we avoid the differ discrepancy between relational model and current model, but we don't get the complications mentioned above. But we get into business of managing conventions - when schema changes on entity or global schema is added, we need to propagate the change to history tables.

[gtteamamxx]

Temp fix for that as mentioned above, just explictly set scheme in OnModelConfiguring:

from

.ToTable("Table", b => b.IsTemporal());

to

.ToTable("Table", b => b.IsTemporal(ttb => ttb.UseHistoryTable("TableHistory", "Scheme")));

also you should edit snapshot of adding temporal migration (.designer.cs) and also context snapshot.
and add line:

After that every migration will be empty.

[moander]

This did the trick for me

.ToTable(b => b.IsTemporal(tb => tb.UseHistoryTable(b.Name!, b.Schema!)));