.NET 11 RC1: Index on a member of a JSON-mapped complex type indexes the whole jsonb column

[CaffeinatedCoder]

With EF Core 11's support for indexes over complex properties, HasIndex(x => x.Details.Slug) on a
ToJson() complex property produces an index on the entire jsonb column rather than on Slug.
With IsUnique(), uniqueness is enforced over the whole document, so two rows with the same Slug
are accepted. Nothing fails or warns along the way: the migration scaffolds and applies cleanly.

Repro

Npgsql.EntityFrameworkCore.PostgreSQL 11.0.0-rc.1, Microsoft.EntityFrameworkCore 11.0.0-rc.1.26431.118
(restored from the dnceng dotnet11 feed, see #3913), PostgreSQL 18.4, net11.0.

using Microsoft.EntityFrameworkCore;

using var ctx = new BlogContext();
Console.WriteLine(ctx.Database.GenerateCreateScript());

ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();

ctx.Blogs.Add(new Blog { Details = new() { Slug = "hello", Owner = "alice" } });
ctx.SaveChanges();
ctx.Blogs.Add(new Blog { Details = new() { Slug = "hello", Owner = "bob" } });
ctx.SaveChanges(); // expected: 23505 unique_violation; actual: succeeds

Console.WriteLine($"Blogs with Slug 'hello': {ctx.Blogs.Count(b => b.Details.Slug == "hello")}");

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs => Set<Blog>();

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<Blog>(b =>
        {
            b.ComplexProperty(x => x.Details, d => d.ToJson());
            b.HasIndex(x => x.Details.Slug).IsUnique();
        });
}

public class Blog
{
    public int Id { get; set; }
    public Details Details { get; set; } = new();
}

public class Details
{
    public string Slug { get; set; } = "";
    public string Owner { get; set; } = "";
}

Output:

CREATE TABLE "Blogs" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Details" jsonb NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_Blogs_Details_Slug" ON "Blogs" ("Details");

Blogs with Slug 'hello': 2

The same model on SQL Server renders
CREATE JSON INDEX [IX_Blogs_Details_Slug] ON [Blogs]([Details]) FOR (N'$.Slug');.

Cause

EF Core 11's RelationalAnnotationProvider.For(ITableIndex, bool) yields Relational:JsonIndex (a
RelationalJsonIndex carrying the JSON path) for indexes over JSON-mapped properties, and
CreateIndexOperation.Columns holds only the container column. The SQL Server generator renders
the path from that annotation. NpgsqlAnnotationProvider.For(ITableIndex, bool) overrides the
method without calling base, so the annotation never reaches the operation (its annotation list
is empty in the repro above), and NpgsqlMigrationsSqlGenerator has no handling for it either. What
remains is a plain index on "Details".

Expected

An expression index over the extracted member, e.g.

CREATE UNIQUE INDEX "IX_Blogs_Details_Slug" ON "Blogs" (("Details" ->> 'Slug'));

which on PostgreSQL 18 rejects the second insert with 23505. Or, if indexes on JSON members are
out of scope for 11.0, an exception at model validation or migration generation. The one thing
it should not do is silently index the whole column.

A few details a fix would need to settle: nested members need -> for the intermediate steps
("Details" -> 'Author' ->> 'Name'); ->> yields text, so non-string members would compare as text
unless cast, and casts such as text::timestamptz are not IMMUTABLE and cannot be indexed; members
of JSON collections have no single-expression equivalent.

SQLite shows the same DDL. It does keep the annotation but its generator ignores it, which is on
the EF Core side.

For context: I maintain EFCore.ComplexIndexes,
which adds complex-type, expression and JSON-member indexes on top of Npgsql's migrations differ and
SQL generator, and ran into this while testing it against EF Core 11 rc.1. Happy to test a fix.