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.

Transactions

Atomic multi-document operations with automatic commit/rollback:

await store.RunInTransaction(async tx =>
{
    await tx.Insert(new User { Id = "u1", Name = "Alice", Age = 25 });
    await tx.Insert(new User { Id = "u2", Name = "Bob", Age = 30 });
    // Commits on success, rolls back on exception
});

The tx parameter is a full IDocumentStore, so you can use any operation inside the transaction — queries, counts, removes, SetProperty, RemoveProperty, everything.

Unit of Work

When you need to gather changes across multiple methods or services and commit them together, use CreateUnitOfWork(). It buffers Add/Update/Remove operations and applies them atomically inside a single RunInTransaction when you call Commit().

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

await uow.Commit();
// All three operations committed in a single transaction.
// On success the queue is cleared automatically.

Member	Purpose
Add<T>(document)	Queue an Insert
Update<T>(document)	Queue a full-document Update (Id required)
Remove<T>(id)	Queue a Remove by Id
Commit(ct)	Apply all queued operations atomically, then clear
Clear()	Discard the queue without executing
PendingCount	Number of operations currently queued

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

try
{
    await uow.Commit();
}
catch (InvalidOperationException)
{
    // Nothing was written. uow.PendingCount still reflects the queued operations.
    uow.Clear();
}

When to use it. Prefer RunInTransaction for a tight self-contained block of operations. Prefer CreateUnitOfWork() when you need to thread a single transactional batch through multiple methods or services without passing an IDocumentStore around as the transaction context.

Note

A UnitOfWork instance is not thread-safe. Use one per logical operation or per request scope.