EFCore.ComplexIndexes.PostgreSQL 5.3.0

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, nulls-distinct control, per-column collation, and storage parameters (WITH (fillfactor=70))
• NULLS FIRST / NULLS LAST per-column null ordering
• Expression (functional) indexes — raw SQL or typed LINQ, on any entity, complex or not
• JSON indexes — index members of ToJson() complex properties as ->> extractions, or the whole document (or a complex collection) with a GIN over the jsonb column
• 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.

Since 5.1.0 the same call also registers the PostgreSQL differ at runtime, so Database.EnsureCreated() and GenerateCreateScript() build the declared indexes and constraints, and the pending-model-changes check in Migrate() sees a complex index that was never scaffolded. Both run the runtime differ, which the design-time wiring never reaches; without the call, EnsureCreated() creates the tables and silently none of the indexes.

Building your own internal service provider? Register the generator and differ 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. A value object mapped through a converter resolves through its member: x.Email.Value is the email column.

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). Filters — here and on indexes — resolve {Property.Path} placeholders to columns or JSON extractions at migrations add, and every filter: has a typed form: x => x.RevokedAt == null, or HasFilter(x => …) on the builders (HasFilter<TEntity> on the non-generic index builders). The translated subset is small — null checks, comparisons, &&/||/!, the string functions above — and refuses enums and dates at the declaration.

Declared constraints can be read back — GetExclusionConstraints() on an entity type or the model, FindExclusionConstraint(name) — with elements, method, filter, deferrability and name, from the mutable model in OnModelCreating as well as the finalized one; and amended there: AddExclusionConstraintFilter(predicate, where) ANDs a predicate onto every selected constraint's filter, idempotently.

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.

Documentation

• PostgreSQL — indexes — index methods, expression and typed LINQ indexes, JSON member indexes, null ordering
• PostgreSQL — temporal and exclusion constraints — WITHOUT OVERLAPS, temporal foreign keys, EXCLUDE, btree_gist
• Full documentation

Changelog

This package's changelog, or the root changelog covering all three packages.

MIT licensed.