Migrations: Down() drops a database-level object (Npgsql collation) before reverting the columns that use it

Start with MigrationsModelDiffer.Sort in src/EFCore.Relational/Migrations/Internal/MigrationsModelDiffer.cs, especially the ordering of alterDatabaseOperations and columnOperations described in the issue. Check how the differ orders operations for Down() when an AlterDatabaseOperation removes an object that a column uses. Done means the generated rollback alters the column before dropping its collation, with regression coverage for the ordering.

Bug description

When a single migration both adds a database-level object and makes a column use it, the generated Down() drops the database-level object before reverting the column. PostgreSQL then refuses the drop, so the migration cannot be reverted.

Concrete case with Npgsql: a custom collation (HasCollation, stored as a model annotation and emitted as an AlterDatabaseOperation) used by a column (UseCollation). The generated Down() is:

migrationBuilder.AlterDatabase()
    .OldAnnotation("Npgsql:CollationDefinition:case_insensitive", "und-u-ks-level2,und-u-ks-level2,icu,False"); // DROP COLLATION

migrationBuilder.AlterColumn<string>(
    name: "Name", table: "Blogs", type: "text", nullable: false,
    oldClrType: typeof(string), oldType: "text", oldCollation: "case_insensitive");                         // ALTER COLUMN ... COLLATE "default"

and reverting it fails with:

2BP01: cannot drop collation case_insensitive because other objects depend on it

Up() is ordered correctly (the collation is created before the column uses it). The cause looks like MigrationsModelDiffer.Sort, which always places alterDatabaseOperations before columnOperations (and the other table/column operations):

https://github.com/dotnet/efcore/blob/a5ed224217a32963366e0e9ac68a83e91951af0e/src/EFCore.Relational/Migrations/Internal/MigrationsModelDiffer.cs#L322-L343

The same ordering is used when the differ computes Down(), where an AlterDatabaseOperation that removes something would need to run after the operations that stop depending on it.

The same class of problem was reported for extensions in npgsql/efcore.pg#83 (worked around there by not dropping extensions). Related but different: #35016 / npgsql/efcore.pg#3604 (the drop missing from Down() entirely).

Workarounds: hand-edit Down() to move the AlterDatabase() call to the end, or split the change into two migrations (collation first, then the column).

Your code

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.14" PrivateAssets="all" />
  </ItemGroup>
</Project>

using Microsoft.EntityFrameworkCore;

// Step 1 model: no collation. Step 2 model (REPRO_STEP=2): a custom collation used by a column.
public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs => Set<Blog>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseNpgsql("Host=localhost;Port=55432;Username=postgres;Password=p;Database=repro");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        if (Environment.GetEnvironmentVariable("REPRO_STEP") == "2")
        {
            modelBuilder.HasCollation("case_insensitive", locale: "und-u-ks-level2", provider: "icu", deterministic: false);
            modelBuilder.Entity<Blog>().Property(b => b.Name).UseCollation("case_insensitive");
        }
    }
}

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

public static class Program
{
    public static void Main() { }
}

Steps (bash):

docker run -d --rm --name efcore-repro-pg -e POSTGRES_PASSWORD=p -p 55432:5432 postgres:16
REPRO_STEP=1 dotnet ef migrations add Initial
REPRO_STEP=2 dotnet ef migrations add AddCaseInsensitiveCollation
REPRO_STEP=2 dotnet ef database update            # OK
REPRO_STEP=2 dotnet ef database update Initial    # fails, 2BP01

dotnet ef migrations script AddCaseInsensitiveCollation Initial shows the order:

START TRANSACTION;
DROP COLLATION case_insensitive;

ALTER TABLE "Blogs" ALTER COLUMN "Name" TYPE text COLLATE "default";

DELETE FROM "__EFMigrationsHistory"
WHERE "MigrationId" = '20261108145011_AddCaseInsensitiveCollation';

COMMIT;

Stack traces

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP COLLATION case_insensitive;
Npgsql.PostgresException (0x80004005): 2BP01: cannot drop collation case_insensitive because other objects depend on it
    SqlState: 2BP01

EF Core version

10.0.14 (Microsoft.EntityFrameworkCore / .Relational / .Design 10.0.14, dotnet-ef 10.0.14)

Database provider

Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (PostgreSQL 16.15)

Target framework

.NET 10.0

Operating system

Windows 11