Repository navigation
.NET 11 RC1: Index on a member of a JSON-mapped complex type indexes the whole jsonb column #3918
Description
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.
Activity
roji commentedon Oct 15, 2026
AI Triage
The below is an AI-generated analysis and may contain inaccuracies.
Confirmed; classified as Bug. On PostgreSQL 18.4, the reported RC1 versions generate an index on the entire
Detailsdocument, and both inserts withSlug = "hello"succeed. The resulting count is2. A manually created unique expression index on("Details" ->> 'Slug')rejects the second insert with SQLSTATE23505.Versions and regression assessment
10.0.010.0.0HasIndex(x => x.Details.Slug)with an invalid member-access-expression exception.11.0.0-preview.611.0.0-preview.6.26359.11811.0.0-rc.111.0.0-rc.1.26431.11811.0.0-rc.1.111.0.0-rc.1.26425.12811.0.0-rc.2.26504.105and11.0.0-rc.2.26509.113This is not RC1-specific, and no previously working implementation was found. It is a defect in the newly accepted EF Core 11 configuration, rather than a regression from functioning JSON-member indexes. There is nevertheless a behavior change from EF Core 10: a configuration that previously failed now succeeds with incorrect semantics.
Cause and provider scope
The reported cause is confirmed. On the exact RC1 repro, the generated
CreateIndexOperationhasColumns = ["Details"]and zero annotations. NpgsqlAnnotationProvider.For(ITableIndex, bool) does not forward the base provider's JSON-index annotation. Independently, NpgsqlMigrationsSqlGenerator renders ordinary index columns without handling JSON-member paths. Forwarding the annotation alone would therefore not fix the SQL.The symptom is not exclusive to PostgreSQL, but the missing Npgsql annotation forwarding and SQL generation are provider-specific:
11.0.0-rtm.26480.113packages. This is already tracked by dotnet/efcore#39064, closed by dotnet/efcore#39089. The merged fix should not be assumed present in the particular packages tested here.CREATE JSON INDEX, but the tested RC1 and RC2 builds silently omit uniqueness. The newer11.0.0-rtm.26480.113build instead throws anInvalidOperationExceptionidentifying the unsupportedIsUniqueoption. This was checked through script generation, without a SQL Server instance. See dotnet/efcore#39065 and its fix, dotnet/efcore#39090.Those upstream fixes do not implement Npgsql's JSON index SQL. This issue should remain actionable in this repository: either generate PostgreSQL expression indexes consistent with query translation, or explicitly reject unsupported JSON-member index configurations.
Related issues
The closest existing Npgsql issue is #2568, the open feature request to make
HasIndexwork on JSON properties. This report adds the concrete bug that the configuration is now accepted but targets the wrong value. Upstream dotnet/efcore#29623 covers SQL Server JSON index support; dotnet/efcore#28605 explicitly concerns indexing the container column, not members. No additional shared-EF duplicate was found beyond the provider-specific reports above.minimal repro
No database or seed data is needed to demonstrate the incorrect target:
GenerateCreateScript()alone reproduces it. The two-insert behavior described above was also verified separately.Use a .NET 11 console project with these package references:
The exact reported EF build requires the public
dotnet11feed, as discussed in #3913. Alternatively, the11.0.0-rc.1.1provider with EF11.0.0-rc.1.26425.128reproduces the same problem using public release packages.The generated index is:
For this string member, the intended expression index would instead be: