EF Core 11: Indexing Nested Complex Type Properties
EF Core 11 RC1 lets you declare indexes on properties nested inside complex types directly in the model. The author checks the resulting PostgreSQL plans with Npgsql and shows where indexing into JSON-mapped data still falls short. A practical read for .NET developers designing data models with complex types.
Key points
- Properties inside complex types, such as g.Summary.Leader.PlayerId, can be indexed with HasIndex when they map to ordinary columns.
- Composite indexes over nested properties, such as Victory.Kind and Leader.Score, can serve both the filter and the sort.
- Indexes on properties inside a JSON-mapped collection land on the whole Board column in RC1 and are not useful yet.
- Complex collections must be mapped with ToJson(), and the containing root type must be JSON too.
- Union types cannot be mapped in RC1; closed hierarchies can be flattened into a snapshot type.
- A hand-written GIN index on the jsonb column gave about a 15x speedup at 20,000 games, but the planner does not always choose it.
With EF Core 11 RC1, you can declare indexes on properties nested inside complex types straight from the model using HasIndex. In the author's board game sample, indexes on leader player ID and victory data stored in ordinary columns become real PostgreSQL indexes through Npgsql, and the queries use them.
Indexes placed inside a JSON-mapped collection don't help yet: the provider creates them over the whole JSON column. The author therefore adds a hand-written PostgreSQL GIN index for containment queries, which the planner only picks up in some cases. The post also covers unions and closed hierarchies, which RC1 rejects, and the mapping workarounds used instead.
Real-world use
- An application that stores game, order or configuration records and filters them by nested value objects.
- List screens that show the records for one player or condition, sorted by score or date.
- A team that stores collections in a JSON column and needs to measure whether a query can use an index.
Why it matters
If you map value objects to ordinary columns, you can index those fields directly from the model in EF Core 11. For queries over JSON collections, re-check provider support and the EXPLAIN output before relying on an index.