SQL Server: custom model-level HiLo sequence causes repeated DropSequence migrations and pending model changes #39324

Related: npgsql/efcore.pg#3613. That report reproduces with SQL Server as well as Npgsql; each provider has its own annotation generator containing the same problematic behavior.

With a custom model-level HiLo sequence name, the initial migration is correct, but every subsequent migration drops EntityFrameworkHiLoSequence, despite no model changes. Database.HasPendingModelChanges() remains true, and applying migrations throws PendingModelChangesWarning on EF Core 9+.

Repro

Create a .NET 8 console project referencing Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Design, both 9.0.8, and use dotnet-ef 9.0.8. No running database is needed to scaffold the migrations or observe the pending-model mismatch.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var context = new XContext();
Console.WriteLine(context.Database.HasPendingModelChanges());

public class XContext : DbContext
{
    public DbSet<Item> Items { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=HiLoRepro;Integrated Security=true;TrustServerCertificate=true")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.UseHiLo("EntityFrameworkHiLoSequenceXContext");
}

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

Run:

dotnet ef migrations add Initial
dotnet ef migrations add NoChanges1
dotnet run
dotnet ef migrations add NoChanges2

Expected: both subsequent migrations are empty and the program prints False.

Actual: the program prints True, and both subsequent migrations contain:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropSequence(
        name: "EntityFrameworkHiLoSequence");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateSequence(
        name: "EntityFrameworkHiLoSequence",
        incrementBy: 10);
}

dotnet ef database update also throws the pending-model exception before connecting.

Cause

The snapshot contains both:

SqlServerModelBuilderExtensions.UseHiLo(modelBuilder, "EntityFrameworkHiLoSequenceXContext");
// Within the entity configuration:
SqlServerPropertyBuilderExtensions.UseHiLo(b.Property<int>("Id"));

SqlServerAnnotationCodeGenerator.GenerateValueGenerationStrategy reads the property's sequence-name/schema annotations but does not fall back to the inherited model configuration. It therefore emits parameterless property-level UseHiLo(), which creates the default sequence rather than inheriting the custom one. The reconstructed snapshot contains an extra sequence, and each successive comparison generates its removal again.

This can be corrected in the provider-specific snapshot generator by preserving the effective sequence name and schema; the equivalent Npgsql fix is being handled independently in the original repository.

Versions

Reproduced with SQL Server provider/EF Core 9.0.8 and 10.0.13. The corresponding Npgsql behavior is already present with provider/EF Core 8.0.0, so EF Core 9's pending-model validation exposed an older mismatch rather than introducing it.

Searched open and closed issues and found no exact duplicate. #28223 is a related HiLo/default-schema lookup issue, but this repro does not configure a default schema.