Index support for complex type properties in EF Core migrations — the missing piece for value object-driven architectures.

EF Core 8.0 introduced complex properties, but migration tooling doesn't automatically generate indexes for these nested value objects. This NuGet package bridges that gap with a clean, fluent API for defining single-column, composite, unique, and filtered indexes directly on complex type properties — and, on PostgreSQL, expression (functional) indexes.

Why it matters:

• Value Object Indexing: Seamlessly add database indexes to properties buried inside complex types (e.g., Person.EmailAddress.Value)
• DDD-Friendly: Supports the Domain-Driven Design pattern of encapsulating logic in value objects without sacrificing database performance
• Migration-Aware: Automatically generates proper CREATE INDEX and DROP INDEX operations during EF Core migrations
• Flexible Filtering: Supports SQL WHERE clauses for filtered indexes (e.g., soft deletes)
• Composite Indexes: Define multi-column indexes spanning both scalar and nested properties with a single, intuitive expression — with per-column ASC/DESC ordering via DbOrder.Asc/DbOrder.Desc
• Expression Indexes (PostgreSQL): Index arbitrary SQL expressions such as lower(email) or to_tsvector('english', body) — including on plain, non-complex entities
• Temporal Constraints (PostgreSQL 18): Declare UNIQUE … WITHOUT OVERLAPS constraints to guarantee no two rows occupy overlapping time periods — the database enforces scheduling integrity for you

Package	NuGet	Description
EFCore.ComplexIndexes		Core library — single-column, composite, unique, and filtered indexes on complex type properties. Works with any EF Core relational provider.
EFCore.ComplexIndexes.PostgreSQL		PostgreSQL extensions via Npgsql — adds GIN, GiST, BRIN, SP-GiST, and Hash index methods, operator classes, covering indexes (INCLUDE), concurrent creation, nulls-distinct control, expression (functional) indexes, and temporal UNIQUE constraints (WITHOUT OVERLAPS).

Which package do I need? Install only the core package if you use SQL Server, SQLite, or any provider where the default B-tree index type is sufficient. Add the PostgreSQL package when you need PostgreSQL-specific index types or expression indexes — it includes the core automatically.

---

Getting started

Complex-property indexes (core)

The complex-property, composite, and provider-method index features are wired up automatically through EF Core's design-time tooling. Just install the package, configure your indexes in OnModelCreating, and run dotnet ef migrations add — zero additional ceremony.

Expression indexes (PostgreSQL) — one-time setup

Expression indexes are the one exception: rendering CREATE INDEX … ((expr)) requires a custom migrations SQL generator that runs when migrations are applied. EF Core does not auto-wire runtime services, so you must opt in once when configuring your DbContext:

services.AddDbContext<AppDbContext>(options =>
    options
        .UseNpgsql(connectionString)
        .UseNpgsqlComplexIndexes());   // ← required for HasExpressionIndex(...)

⚠️ UseNpgsqlComplexIndexes() is a prerequisite for HasExpressionIndex. Without it, applying a migration that contains an expression index will fail (the stock generator can't render the expression). All other features — complex-property indexes, composite indexes, and the GIN/GiST/etc. methods — do not require this call; they flow through Npgsql's own SQL generator.

Using a custom Internal Service Provider? If your application builds its own IServiceProvider and passes it to .UseInternalServiceProvider(...), EF Core prevents .UseNpgsqlComplexIndexes() from modifying services. Instead, register the generator directly on your IServiceCollection:

var provider = new ServiceCollection()
.AddEntityFrameworkNpgsql()
.AddNpgsqlComplexIndexes() // ← Add this for expression indexes
.BuildServiceProvider();

---

Usage

Single-column index on a complex property

builder.ComplexProperty(x => x.EmailAddress, c =>
    c.Property(x => x.Value)
     .HasComplexIndex(isUnique: true, filter: "deleted_at IS NULL")
);

Composite index across scalar and nested properties

builder.HasComplexCompositeIndex(
    x => new { x.Name, x.EmailAddress.Value },
    isUnique: true);

Per-column sort direction

Wrap any member in DbOrder.Desc(...) (or DbOrder.Asc(...), the default) to control its sort order. Because a wrapped member is a method call, C# requires you to name it in the anonymous type:

builder.HasComplexCompositeIndex(
    c => new { c.HybridDateTime.DateTime, Counter = DbOrder.Desc(c.HybridDateTime.Counter), c.Id },
    indexName: "IX_Commits_DateTime_Counter_Id");
// CREATE INDEX "IX_Commits_DateTime_Counter_Id" ON ... ("DateTime", "Counter" DESC, "Id");

Direction maps to EF Core's native CreateIndexOperation.IsDescending, so it is rendered by every relational provider (SQL Server, SQLite, PostgreSQL) — no extra wiring required. Re-declaring an index over the same columns updates its direction.

PostgreSQL index methods on a complex property

Use the builder-callback overload to reach the PostgreSQL-specific options (GIN, GiST, BRIN, SP-GiST, Hash, operator classes, INCLUDE, concurrent creation, nulls-distinct):

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

Expression (functional) indexes — PostgreSQL

Requires UseNpgsqlComplexIndexes() (see Getting started). Available as an extension on EntityTypeBuilder<TEntity>, so it works on any entity — complex or not.

Each string is emitted verbatim — there is no property-to-column resolution and no automatic quoting. Write the final SQL exactly as it should appear inside the index, referencing real column names.

Single expression:

// CREATE INDEX "IX_person_lowerlastname" ON person ((lower(last_name)));
builder.HasExpressionIndex("lower(last_name)");

With unique / filter / explicit name:

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

Multiple ordered parts + provider options (builder callback):

builder.HasExpressionIndex(idx => idx
    .Expression("country")            // a plain column, written as raw SQL
    .Expression("lower(email)")       // a SQL expression
    .IsUnique()
    .HasFilter("deleted_at IS NULL")
    .HasName("ix_person_country_email_ci"));
// CREATE UNIQUE INDEX "ix_person_country_email_ci"
//   ON person ((country), (lower(email)))
//   WHERE deleted_at IS NULL;

Full-text / JSONB with a GIN index:

builder.HasExpressionIndex(idx => idx
    .Expression("to_tsvector('english', body)")
    .UseGin());
// CREATE INDEX ... ON articles USING gin ((to_tsvector('english', body)));

Covering expression index (INCLUDE):

builder.HasExpressionIndex(idx => idx
    .Expression("lower(email)")
    .IsUnique()
    .IncludeProperties("display_name"));

Quoting tip

Strings are passed through untouched, so identifiers that need PostgreSQL quoting (e.g. PascalCase columns) must include the quotes yourself. C# raw string literals keep this readable:

// CREATE INDEX ... ON "People" ((lower("Email")));
builder.HasExpressionIndex(""" lower("Email") """.Trim());

Roadmap: the expression API is built on an IIndexExpression seam. A future LINQ add-on will let you write HasExpressionIndex(x => x.Email.ToLower()) and have it translated to SQL — flowing through the exact same pipeline.

Temporal UNIQUE constraints (WITHOUT OVERLAPS) — PostgreSQL 18

Requires UseNpgsqlComplexIndexes() (see Getting started). Available as an extension on EntityTypeBuilder<TEntity>, so it works on any entity — complex or not.

PostgreSQL 18 introduced WITHOUT OVERLAPS for unique constraints — a long-requested feature for scheduling, booking, and versioning scenarios. Instead of only checking "is this exact value already present?", the database enforces "no two rows for the same key have overlapping time periods".

ALTER TABLE bookings
  ADD CONSTRAINT ak_bookings_room_period
    UNIQUE (room_id, period WITHOUT OVERLAPS);

HasTemporalConstraint exposes this as a first-class EF Core API. You supply scalar key columns (the "group" — e.g. a room, a resource, an employee) and a period column (a PostgreSQL range type such as daterange, tstzrange, or NpgsqlRange<T>):

Single key column:

builder.HasTemporalConstraint(
    keyColumns: b => b.RoomId,
    period:     b => b.ValidPeriod);
// ALTER TABLE "Bookings" ADD CONSTRAINT "AK_Bookings__RoomId_ValidPeriod"
//   UNIQUE ("RoomId", "ValidPeriod" WITHOUT OVERLAPS);

Composite key columns:

builder.HasTemporalConstraint(
    keyColumns: b => new { b.Facility, b.RoomId },
    period:     b => b.ValidPeriod);
// UNIQUE ("Facility", "RoomId", "ValidPeriod" WITHOUT OVERLAPS)

Explicit constraint name:

builder.HasTemporalConstraint(
    keyColumns: b => b.RoomId,
    period:     b => b.ValidPeriod,
    name:       "uk_room_no_overlap");

How the period column is validated

The migration differ validates the period property at migration-generation time (dotnet ef migrations add). It must be mapped to a PostgreSQL range or multirange store type (anything ending in range — e.g. daterange, tstzrange, int4multirange) or have a CLR type of NpgsqlRange<T> / a multirange struct from NpgsqlTypes. Using an incompatible type such as string, int, or DateOnly throws an InvalidOperationException before any SQL is generated:

The temporal constraint period property 'Start' on entity 'Booking' does not appear to be a range or multirange type. Found CLR type 'DateTime' (store type: 'timestamp with time zone'). Expected NpgsqlRange<T>, a PostgreSQL range/multirange column type, or a store type ending in 'range' (e.g., daterange, int4multirange).

The period column stays a plain mapped column — it is deliberately not part of an EF key, because EF Core forbids non-comparable range types in primary keys. Use a surrogate or scalar EF primary key for change tracking; the temporal constraint handles the non-overlap guarantee independently.

btree_gist extension

Temporal constraints over scalar key columns require the btree_gist PostgreSQL extension. The differ injects CREATE EXTENSION IF NOT EXISTS btree_gist; automatically when a temporal constraint is first added. You can take explicit control or opt out:

// Explicit: declare the extension yourself (Npgsql's own differ handles it)
modelBuilder.UseBtreeGist();

// Opt out: e.g. if the extension is provisioned out-of-band by your DBA
modelBuilder.SuppressTemporalExtensionAutoInjection();

When UseBtreeGist() is present, automatic injection backs off to avoid a duplicate CREATE EXTENSION statement.

Idempotency and renames

Re-declaring a temporal constraint on the same key + period replaces the previous one. Removing HasTemporalConstraint from the model causes the differ to emit a DROP CONSTRAINT in the next migration (unless the table itself is being dropped).

Temporal foreign keys (PERIOD) — PostgreSQL 18

Requires UseNpgsqlComplexIndexes() because PostgreSQL's temporal FK syntax needs custom migration SQL rendering.

HasTemporalForeignKey adds PostgreSQL 18 temporal referential integrity. The scalar key columns are matched by equality, and the dependent period must be fully covered by matching principal periods.

A typical subscription/add-on model looks like this:

modelBuilder.Entity<Subscription>(b =>
{
    // Principal side: PostgreSQL requires the referenced columns to have
    // a temporal UNIQUE/PRIMARY KEY constraint with WITHOUT OVERLAPS.
    b.HasTemporalConstraint(
        keyColumns: x => x.SubscriptionId,
        period:     x => x.ValidDuring);
});

modelBuilder.Entity<SubscriptionAddOn>(b =>
{
    b.HasTemporalForeignKey<SubscriptionAddOn, Subscription>(
        dependentKeyColumns: x => x.SubscriptionId,
        dependentPeriod:     x => x.ActiveDuring,
        principalKeyColumns: x => x.SubscriptionId,
        principalPeriod:     x => x.ValidDuring,
        name:                "fk_addons_subscriptions_temporal" 
    );
});

Generated SQL:

ALTER TABLE subscription_addons
  ADD CONSTRAINT fk_addons_subscriptions_temporal
    FOREIGN KEY (subscription_id, PERIOD active_during)
    REFERENCES subscriptions (subscription_id, PERIOD valid_during);

Composite keys use anonymous types on both sides:

b.HasTemporalForeignKey<SubscriptionAddOn, Subscription>(
    dependentKeyColumns: x => new { x.TenantId, x.SubscriptionId },
    dependentPeriod:     x => x.ActiveDuring,
    principalKeyColumns: x => new { x.TenantId, x.SubscriptionId },
    principalPeriod:     x => x.ValidDuring 
);

Restrictions and validation

• PostgreSQL 18+ only.
• Period columns must be PostgreSQL range or multirange columns (daterange, tstzrange, NpgsqlRange<T>, etc.).
• The referenced principal columns must have a matching HasTemporalConstraint in the model. PostgreSQL requires a referenced temporal UNIQUE/PRIMARY KEY constraint with WITHOUT OVERLAPS.
• Temporal foreign keys emit NO ACTION referential actions. PostgreSQL does not support temporal FK CASCADE, RESTRICT, SET NULL, or SET DEFAULT actions.
• This API emits standalone database constraints; it does not try to model the temporal relationship as an EF navigation/relationship key.

The standalone design is intentional. The period column remains a normal mapped property, not an EF key member. EF keys require key values suitable for change tracking, while Npgsql range values are not suitable EF key members; PostgreSQL enforces the temporal relationship independently at the database level.

---

The package integrates seamlessly with EF Core's design-time tooling. Apart from the one-time UseNpgsqlComplexIndexes() call for PostgreSQL-specific SQL generation, there is no additional ceremony — just configure and migrate.