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

[CaffeinatedCoder]

With EF Core 11's support for indexes over complex properties, HasIndex(x => x.Details.Slug).IsUnique()
on a ToJson() complex property creates a unique index on the whole "Details" column on SQLite.
Uniqueness is then enforced over the entire document, so two rows with the same Slug are accepted.
Nothing fails or warns along the way.

Repro

Microsoft.EntityFrameworkCore.Sqlite 11.0.0-rc.1.26425.128, bundled SQLite 3.53.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: UNIQUE constraint failed; 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.UseSqlite("Data Source=repro.db");

    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 NOT NULL CONSTRAINT "PK_Blogs" PRIMARY KEY AUTOINCREMENT,
    "Details" TEXT NOT NULL
);
CREATE UNIQUE INDEX "IX_Blogs_Details_Slug" ON "Blogs" ("Details");

Blogs with Slug 'hello': 2

Cause

RelationalAnnotationProvider.For(ITableIndex, bool) puts Relational:JsonIndex (the
RelationalJsonIndex carrying the member path) on the CreateIndexOperation, whose Columns hold
only the container column. SqlServerMigrationsSqlGenerator renders the path from that annotation;
SqliteMigrationsSqlGenerator doesn't read it, so the base generator emits a plain index on
"Details".

Expected

An expression index on the member. I tried three spellings against the repro database (SQLite
3.53.4). All three reject the duplicate insert, but only the one spelled like EF's own query
translation is used for queries:

Index expression	Duplicate insert	Plan for ctx.Blogs.Where(b => b.Details.Slug == "hello")
"Details" ->> 'Slug'	rejected	SEARCH b USING INDEX IX_Blogs_Details_Slug (<expr>=?)
"Details" ->> '$.Slug'	rejected	SCAN b
json_extract("Details", '$.Slug')	rejected	SCAN b

EF translates that query to WHERE "b"."Details" ->> 'Slug' = 'hello', and SQLite only uses an
expression index when the expressions match, so the index expression should come from the same
place as the query translation's. If JSON-member indexes are out of scope for SQLite in 11.0, a
model-validation error would still be far better than an index on the container.

Npgsql has the same symptom with a different cause (its annotation provider drops the annotation):
npgsql/efcore.pg#3918. The SQL Server provider has a related problem with IsUnique(), filed
separately.

For context: I maintain EFCore.ComplexIndexes,
which provided complex-type and JSON-member indexes on EF Core 10, and ran into this while testing it
against EF Core 11 rc.1. Happy to test a fix.