Indexes & Transactions

Index Management

For frequently queried JSON properties, create expression indexes to speed up lookups. These methods are on DocumentStore directly (not on IDocumentStore).

// Create an index — up to 30x faster queries
await store.CreateIndexAsync<User>(u => u.Name, ctx.User);

// Nested properties
await store.CreateIndexAsync<Order>(o => o.ShippingAddress.City, ctx.Order);

// Drop a specific index
await store.DropIndexAsync<User>(u => u.Name, ctx.User);

// Drop all indexes for a type
await store.DropAllIndexesAsync<User>();

CreateIndexAsync uses IF NOT EXISTS, so calling it multiple times is safe. Index names are deterministic (idx_json_{typeName}_{jsonPath}).

Generated SQL per provider

CreateIndexAsync emits a partial expression / functional index on each SQL provider — same C# call, different DDL.

Provider	Emitted DDL
SQLite / SQLCipher	CREATE INDEX IF NOT EXISTS idx_json_User_name ON documents (json_extract(Data, '$.name')) WHERE TypeName = 'User';
PostgreSQL	CREATE INDEX IF NOT EXISTS idx_json_User_name ON documents ((Data #>> '{name}')) WHERE TypeName = 'User';
MySQL	CREATE INDEX idx_json_User_name ON documents ((CAST(JSON_EXTRACT(Data, '$.name') AS CHAR(255))));
SQL Server	ALTER TABLE [documents] ADD cc_idx_json_User_name AS CAST(JSON_VALUE(Data, '$.name') AS NVARCHAR(450)); CREATE INDEX idx_json_User_name ON [documents] (cc_idx_json_User_name) WHERE TypeName = N'User';

Note

SQL Server cannot index a JSON_VALUE expression directly, so the provider adds a PERSISTED computed column (cc_{indexName}) and indexes that. DropIndexAsync removes both the index and the computed column.

Note

When using table-per-type mapping, the index is created on the mapped table and the WHERE TypeName = '...' clause is omitted — it’s already implicit. The index becomes a plain expression index rather than a partial one.

Performance impact

JSON property indexes dramatically speed up equality queries by letting the database use a B-tree lookup instead of scanning every row with JSON extraction.

Flat query (filter by name, 1,000 records):

Method	Mean
Without index	270 us
With index	8.52 us

~32x faster. Indexes give the biggest wins on selective queries that return few results.

When indexes don’t help

Some predicates cannot use an expression index, even if one exists for the property. Either rewrite the query, accept the scan cost, or denormalize.

Pattern	Reason
Where(u => u.Name.Contains("li"))	Leading wildcard (LIKE '%li%') — B-trees can’t satisfy this.
Where(u => u.Name.EndsWith("son"))	Same — LIKE '%son'.
Where(o => o.Lines.Any(l => l.Qty > 1))	Requires expanding the child collection per row. Consider promoting the predicate’s outcome into a top-level property (e.g. a denormalized HasLargeLine boolean) and indexing that.
Case-insensitive matches	The functional index would need to match the case-folded expression, which is not emitted.
Where(... && unindexedExpr)	Database may still pick the index, but only if the indexed predicate is selective enough.
All predicates on LiteDB and IndexedDB	These providers do not push predicates down — see Provider Reference.

StartsWith("Al") does use an index — it translates to LIKE 'Al%', which a B-tree can satisfy with a range scan.

Composite / multi-column indexes

Pass multiple property-access expressions to index over several JSON paths in a single B-tree:

// Composite index on (Country, Age) for User
await store.CreateIndexAsync(ctx.User, u => u.Country, u => u.Age);

// Drop the same composite by repeating the key in order
await store.DropIndexAsync(ctx.User, u => u.Country, u => u.Age);

Composite indexes use the naming convention idx_json_{typeName}__{path1}__{path2}… (paths joined with double underscore) and follow standard B-tree leftmost-prefix rules: a query filtering only on Country can still use the composite, but one filtering only on Age cannot.

Provider	Emitted DDL
SQLite / SQLCipher	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON documents (json_extract(Data, '$.country'), json_extract(Data, '$.age')) WHERE TypeName = 'User';
PostgreSQL	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON "documents" ((Data #>> '{country}'), (Data #>> '{age}')) WHERE TypeName = 'User';
MySQL	CREATE INDEX idx_json_User__country__age ON \documents` ((CAST(JSON_EXTRACT(Data, ‘$.country’) AS CHAR(255))), (CAST(JSON_EXTRACT(Data, ‘$.age’) AS CHAR(255))));`
DuckDB	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON "documents" (json_extract_string(Data, '$.country'), json_extract_string(Data, '$.age'));
SQL Server	One PERSISTED computed column per path (cc_{indexName}_0, cc_{indexName}_1, …), then a single CREATE INDEX over all of them filtered by TypeName. DropIndexAsync discovers the backing columns via sys.index_columns and removes them after the index.

If most of your workloads filter on just one of the columns, prefer two single-column indexes and let the planner combine — composite indexes earn their keep when you consistently filter (or sort) on the leftmost prefix.

Write amplification

Every expression index adds a B-tree write to every Insert / Update / Upsert / BatchInsert. Five indexes ≈ 6× the write cost of an unindexed table. Index the properties you actually query; do not index defensively. See Performance for more.

Unique indexes

CreateIndexAsync builds performance indexes. Uniqueness is different: it is a rule about the data, so it is declared on the document type and the store enforces it on every provider except DuckDB, on every write path — Insert, Update, Upsert, SetProperty, set-based ExecuteUpdate, batches, sessions and backup import.

options.ConfigureDocument<Customer>(cfg =>
{
    cfg.MapUniqueIndex(x => x.Email);                               // one property
    cfg.MapUniqueIndex(x => new { x.Region, x.AccountNumber });     // composite: an anonymous type
    cfg.MapUniqueIndex(x => x.Address.PostalCode);                  // nested property
    cfg.MapProperty(x => x.Sku, p => p.Unique());                   // shorthand for a single property
});

A write that would give two documents the same key stores nothing and throws UniqueConstraintException:

try
{
    await store.Insert(customer);
}
catch (UniqueConstraintException ex)
{
    logger.LogWarning("{Type} {Id} collides on {Properties} ({Index})", ex.TypeName, ex.DocumentId, ex.PropertyNames, ex.IndexName);
}

Member	Description
TypeName / DocumentType	The document type whose index was violated
IndexName	uq_{Type}_{Properties}, or uq_{Type}_{name} when MapUniqueIndex(..., name: "...") was given (capped at 51 characters). Relational indexes are created under this name plus a short hash of the table, because PostgreSQL and Oracle scope index names to the schema
PropertyNames	The key’s properties, in order
DocumentId	The rejected document’s id — null for a set-based write, where the store cannot tell which row collided

The duplicated value is deliberately not in the message: unique keys are very often personal data (an email address, a national id), and exception messages end up in logs. UniqueConstraintException derives from InvalidOperationException.

Tip

Catch the exception rather than querying first. “Check, then insert” races with every other writer — the index is the only thing that actually guarantees uniqueness.

The rules — identical everywhere

• Scoped to the document type. Other types in the same table may hold the same value. On a shared-table multi-tenant store the index is also scoped to the tenant.
• A null or missing key part is not constrained. Any number of customers may have no email; a composite key with one null part is unconstrained too.
• Values compare exactly, so uniqueness is case-sensitive — even on SQL Server and MySQL, whose default collations are not. For case-insensitive uniqueness, normalize the value before writing it (for example, store the email lower-cased).
• Key parts are plain property chains. A method call such as x => x.Email.ToLower(), or the same property twice, throws ArgumentException.
• Randomized-encrypted properties can’t be a key part or be read by the filter — equal values encrypt differently — and fail when the store is built. EncryptionMode.Deterministic works.

Filters and soft delete

A filter: constrains only the documents it matches, and is re-evaluated on every write — updating a document back into the filter can collide, and moving it out releases its value. The natural pairing is soft delete, so a removed user gives their email back:

options.ConfigureDocument<User>(cfg =>
{
    cfg.AddSoftDelete(x => x.IsDeleted);
    cfg.MapUniqueIndex(x => x.Email, filter: x => !x.IsDeleted);
});

await store.Remove<User>("u1");                                     // flags u1
await store.Insert(new User { Id = "u2", Email = "alice@x.com" });  // fine — u1 no longer holds the value

Global query filters (AddQueryFilter) never apply to a unique index; only its own filter: does.

How each provider enforces it

Provider	Mechanism
SQLite / SQLCipher	Partial unique expression index — CREATE UNIQUE INDEX uq_Customer_Email_<table hash> ON documents (json_extract(Data, '$.email')) WHERE TypeName = 'Customer' (plus the filter)
PostgreSQL / CockroachDB	Partial unique expression index over the same typed extraction a query uses. An entry larger than ~2.7 KB is rejected by the engine; key and filter expressions must be immutable (a DateTime comparison is not)
MySQL	Functional unique index. Each key part is CASE WHEN TypeName = … AND <filter> THEN SHA2(<JSON text>, 256) END, so another type’s or a filtered-out row is NULL and never collides, and a long value can’t hit the key-length limit. ON DUPLICATE KEY UPDATE and INSERT IGNORE treat a collision on any unique key as the conflicting row, so upserts and backup Replace/SkipExisting of a unique-indexed type take a read-then-write path instead
MariaDB	The same key parts as VIRTUAL generated columns ({index}_k0, …) with a unique index over them — MariaDB has no functional key parts
SQL Server	PERSISTED computed columns — a SHA-256 hash per key part plus a discriminator ({index}_k0, …, {index}_x) — under a unique index filtered by TypeName. SQL Server treats NULLs as equal in a unique index, which the discriminator works around. String values longer than 4000 characters are not constrained
Oracle	Function-based unique index whose parts are STANDARD_HASH of the extracted value, NULL for an excluded row (Oracle leaves all-NULL keys out of an index). String values longer than 4000 characters are not constrained
DuckDB	Not supported. DuckDB can’t index an expression over a JSON value (nor add a generated column to an existing table), so a mapped unique index fails when the store is built with DocumentConfigurationException
MongoDB / Amazon DocumentDB	Native unique index on data.<path> with a partialFilterExpression of the type name, a $type check per key part (so null and missing are excluded), and the translated filter. The filter must be expressible there: &&, ||, !boolProp, ==, < <= > >=, == null, and Contains over a constant list; || and Contains need MongoDB 6.0+, and Amazon DocumentDB needs 5.0+. Anything else fails at index creation
Cosmos DB	A reservation item per key (__unique__<sha-256>) in the document’s own logical partition, committed in the same TransactionalBatch as the document. Released keys are deleted after the commit; one left behind is taken over by the next writer once its owner no longer holds the value. Document ids starting with __unique__ are reserved
LiteDB	A reservation document per key in a {collection}_unique sidecar collection, written in the same LiteDB transaction as the document (or the unit of work’s)
IndexedDB	A reservation record per key (~uq:<sha-256>) in the document’s object store, checked and written in the same readwrite transaction as the document
DynamoDB	A reservation item per key (pk = unique#<sha-256>) written in the same TransactWriteItems as the document
Azure Table	A reservation row per key (~uq-<sha-256>) in the document’s partition, committed in the same entity-group transaction
Firestore	A reservation document per key in {collection}_unique, committed in the same Firestore transaction
Redis	A reservation key per key (uq:<sha-256>) claimed in the same Lua script that writes the document. Needs a single-shard deployment
RavenDB	A compare-exchange value per key (uq/<sha-256>), claimed before the session saves and released after. Not atomic: a claim orphaned by a crash between the claim and the save blocks its value for 30 seconds, after which another writer can take it over

Batch, Clear, ExecuteUpdate and ExecuteDelete operations on the reservation-based providers are atomic per document or per chunk, the same boundary those operations already have there.

Existing data, and changing an index

• Native-index providers (the relational providers, MongoDB, Amazon DocumentDB) create the index when the table/collection is first used. If documents already stored there share a key, that fails with a DocumentConfigurationException naming the index — resolve the duplicates and it is retried on the next call.
• The index is created once and never altered. After changing a key or filter, drop the old index yourself, or give the new one a different name:.
• Reservation-based providers don’t back-fill: documents stored before the mapping was added hold no reservation, so they can’t block a new document. Redis, LiteDB and IndexedDB re-reserve a document on its next write; the others only once a write changes its key.

Transactions — the session (unit of work)

Grouping writes into a single transaction is done through an IDocumentSession opened from the store (store.OpenSession()). It buffers Add/AddRange/Update/Upsert/Remove operations and applies them atomically when you call SaveChanges — all commit or all roll back. There is no RunInTransaction; SaveChanges is the implicit transaction, and session.BeginTransaction() is the explicit one (see below).

await using var session = store.OpenSession();
session.Add(new User { Id = "u1", Name = "Alice" })
       .Update(existingUser)
       .Remove<User>("u2");

await session.SaveChanges();
// All three operations committed in a single transaction.
// On success the buffer is cleared automatically.

Contiguous same-type inserts are coalesced into the batch-insert fast path, so grouping inserts in a session is as fast as BatchInsert. A session is a write buffer, not a change tracker: reads against the store don’t see operations still buffered before SaveChanges. For read-modify-write atomicity, use ETag/CAS (IfMatch) + retry, or an explicit await using var tx = await session.BeginTransaction(); with a LockMode.Update read (relational providers).

Member	Purpose
Add<T>(document)	Queue an Insert
AddRange<T>(documents)	Queue a batch Insert
Update<T>(document)	Queue a full-document Update (Id required)
Upsert<T>(patch)	Queue an Upsert (Id required)
Remove<T>(id)	Queue a Remove by Id
SaveChanges(ct)	Apply all queued operations atomically, then clear
ClearPending()	Discard the buffer without executing
PendingCount	Number of operations currently queued
BeginTransaction([IsolationLevel])	Open an explicit transaction — locking reads + set-based writes (relational)

Error handling. If SaveChanges() fails the transaction is rolled back and the buffer is preserved, so you can inspect or amend it and retry:

try
{
    await session.SaveChanges();
}
catch (InvalidOperationException)
{
    // Nothing was written. session.PendingCount still reflects the queued operations.
    session.ClearPending();
}

Inject a scoped IDocumentSession in ASP.NET Core (add .AddScopedDocumentSession()), open one from the singleton IDocumentSessionFactory where there’s no request scope, or store.OpenSession() directly.

Note

An IDocumentSession is not thread-safe (the EF-DbContext contract). Use one per logical operation / request scope, and await using it.