EFCore.ComplexIndexes.PostgreSQL 5.0.2

EFCore.ComplexIndexes.PostgreSQL

PostgreSQL index and constraint features for EFCore.ComplexIndexes, via Npgsql. The core package is included automatically.

Adds, on top of the core's complex-property, composite, unique, and filtered indexes:

• Index methods — GIN, GiST, BRIN, SP-GiST, Hash — plus operator classes, covering (INCLUDE) indexes, concurrent creation, and nulls-distinct control
• NULLS FIRST / NULLS LAST per-column null ordering
• Expression (functional) indexes — raw SQL or typed LINQ, on any entity, complex or not
• JSON member indexes — index members of ToJson() complex properties as ->> extractions
• Temporal UNIQUE … WITHOUT OVERLAPS constraints and temporal foreign keys (PostgreSQL 18)
• Exclusion (EXCLUDE) constraints — filtered overlap protection, on every supported version

Setup

Most features need nothing beyond installing the package. Two are rendered when migrations are applied rather than at design time, because they have no slot on EF Core's native index operation, and those need a one-time opt-in:

services.AddDbContext<AppDbContext>(options =>
    options
        .UseNpgsql(connectionString)
        .UseNpgsqlComplexIndexes());

Forgetting this does not produce a silently wrong index: affected indexes carry a sentinel column named __requires_UseNpgsqlComplexIndexes__, so the stock generator fails loudly with that name in the error message.

Building your own internal service provider? Register the generator directly instead:

var provider = new ServiceCollection()
    .AddEntityFrameworkNpgsql()
    .AddNpgsqlComplexIndexes()
    .BuildServiceProvider();

Usage

Index methods and options

builder.ComplexProperty(x => x.Payload, c =>
    c.Property(x => x.Json)
     .HasComplexIndex(idx => idx.UseGin().HasOperators("jsonb_path_ops"))
);

UseGin(), UseGist(), UseBrin(), UseHash(), UseSpGist(), HasOperators(...), IncludeProperties(...), IsCreatedConcurrently(), AreNullsDistinct(...).

Null ordering

builder.HasComplexCompositeIndex(
    x => new { x.Name, Reviewed = DbOrder.NullsLast(DbOrder.Desc(x.ReviewedAt)) });
// CREATE INDEX ... (name, reviewed_at DESC NULLS LAST);

Expression indexes

Raw SQL is emitted verbatim — no property-to-column resolution, no automatic quoting:

builder.HasExpressionIndex("lower(email)", isUnique: true, filter: "deleted_at IS NULL");

builder.HasExpressionIndex(idx => idx
    .Expression("country")
    .Expression("lower(email)").Descending()
    .UseGin()
    .HasName("ix_person_country_email_ci"));

Or pass a lambda and let property paths resolve against the finalized model, so HasColumnName, complex-property columns, and ToJson() members are honored automatically:

builder.HasExpressionIndex(x => x.Email.Value.ToLower(), isUnique: true);
// CREATE UNIQUE INDEX ... ON people ((lower("email")));

The translated subset is deliberately small — ToLower/ToUpper, Trim variants, Substring, Replace, string.Length, concatenation, ??, constants — and anything else throws NotSupportedException at declaration time, pointing at the raw-SQL overload.

JSON member indexes

When a complex property is mapped with ToJson(), its members have no table columns — yet the same index declarations keep working, resolving to extraction expressions instead:

builder.ComplexProperty(x => x.Name, c => c.ToJson("name"));
builder.HasComplexIndex(x => x.Name.ShortName, isUnique: true, indexName: "ux_employer_short_name");
// CREATE UNIQUE INDEX "ux_employer_short_name" ON employers (("name" ->> 'ShortName'));

Nested complex types become -> segments and HasJsonPropertyName is honored.

Temporal constraints — PostgreSQL 18

builder.HasTemporalConstraint(keyColumns: b => b.RoomId, period: b => b.ValidPeriod);
// ALTER TABLE bookings ADD CONSTRAINT ... UNIQUE (room_id, valid_period WITHOUT OVERLAPS);

The period must be a range or multirange column (daterange, tstzrange, NpgsqlRange<T>, …); anything else throws at migrations add. It stays a plain mapped column, deliberately not part of an EF key — EF Core forbids non-comparable range types in keys. Temporal foreign keys are available as HasTemporalForeignKey, and require a matching constraint on the principal.

Exclusion constraints

An exclusion constraint generalizes uniqueness, and unlike UNIQUE … WITHOUT OVERLAPS it accepts a WHERE predicate — so a filtered overlap guarantee can only be expressed this way:

builder.HasExclusionConstraint(
    equalityColumns: x => new { x.GranteeId, x.RoleId },
    overlapsColumn:  x => x.Period,
    filter:          "revoked_at IS NULL",
    name:            "ex_role_grant_active_period");

Constraint identity is the ordered elements plus the filter, so the same columns under different predicates give you two coexisting partial constraints (both must be named).

btree_gist

Scalar equality elements under gist need the extension; the differ injects CREATE EXTENSION IF NOT EXISTS btree_gist automatically. Use modelBuilder.UseBtreeGist() for explicit control or SuppressTemporalExtensionAutoInjection() to opt out.

Changelog

5.0.2

• Fixed: temporal UNIQUE … WITHOUT OVERLAPS constraints and temporal foreign keys are rendered at design time and no longer need UseNpgsqlComplexIndexes(). Without that wiring the stock Npgsql generator emitted a plain UNIQUE (key, period) — valid DDL that applied cleanly and silently dropped the entire non-overlap guarantee. Migrations scaffolded before this change keep working.
• Fixed: exclusion-constraint identity now includes the filter. Two EXCLUDE constraints over the same columns with different predicates coexist instead of the second silently replacing the first — the filtered-overlap case the API exists for.
• Fixed: duplicate exclusion-constraint names are rejected. Because every ADD CONSTRAINT is preceded by DROP CONSTRAINT IF EXISTS, a reused name did not fail — the migration applied and the second constraint quietly replaced the first.
• Fixed: the design-time differ is scoped to the Npgsql provider, so a solution that also references another satellite can no longer hand a PostgreSQL model to the wrong differ.
• Fixed: Npgsql:IndexSortOrder/IndexNullSortOrder are no longer forwarded, and setting either now throws with a pointer to DbOrder. They duplicated what DbOrder.Asc/Desc/ NullsFirst/NullsLast already express per column, so an index could carry two conflicting descriptions of its sort order with the annotation's half silently losing.
• Fixed: validation no longer inspects index operations this package did not create, so a plain native HasIndex carrying provider options is left alone.

5.0.1

• Changed: exclusion-constraint ADD CONSTRAINT DDL is preceded by DROP CONSTRAINT IF EXISTS, so adopting a pre-existing hand-written constraint of the same name applies cleanly instead of failing with 42P07.
• Fixed: renaming a table no longer drops and recreates the exclusion and temporal constraints it carries.
• Changed: a name-only change to an exclusion constraint, temporal constraint, or temporal foreign key emits ALTER TABLE … RENAME CONSTRAINT instead of rebuilding. Dependent temporal foreign keys survive untouched.

5.0.0

• New: HasExclusionConstraint — EXCLUDE constraints with WHERE predicates.
• New: typed LINQ expression indexes — HasExpressionIndex(x => x.Email.ToLower()).
• New: JSON member indexes for ToJson() complex properties.
• New: NULLS FIRST/NULLS LAST via DbOrder.NullsFirst/NullsLast and ExpressionIndexBuilder.NullsFirst()/NullsLast().
• Fixed: descending parts of expression indexes render DESC.
• Changed: IncludeProperties(...) entries resolve as property paths (complex members included) with verbatim column-name fallback.
• Changed: indexes requiring the custom generator carry a loud sentinel column, so a missing UseNpgsqlComplexIndexes() fails at apply time with an actionable error instead of applying a silently wrong index.

Full documentation: https://github.com/CaffeinatedCoder/EFCore.ComplexIndexes

MIT licensed.