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 — 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.