Querying

Fluent Query Builder

The fluent query builder is the primary way to query documents. Start with store.Query<T>() and chain builder methods, then terminate with a materialization method.

Builder methods (non-executing)

Method	Description
.Where(predicate)	Filter by LINQ expression. Multiple calls combine with AND.
.Where(filter[, jsonTypeInfo])	Filter by a runtime filter string (e.g. "Age >= 30 and Status == 'open'") — AOT-safe. Supports and/or/not, comparisons, is [not] null, in (…), contains/startsWith/endsWith.
.WhereIn(selector, values[, nulls]) / .WhereNotIn(selector, values[, nulls])	Set-membership filter (IN / NOT IN) from an in-memory collection, lowered to each store’s native construct. nulls is a NullHandling (Ignore default / Raw / Match). Empty set ⇒ WhereIn matches nothing, WhereNotIn matches everything. A string property-name overload mirrors string OrderBy/Where.
.OrderBy(selector) / .OrderByDescending(selector)	Sort by property (expression).
.OrderBy(name[, jsonTypeInfo]) / .OrderByDescending(name[, jsonTypeInfo])	Sort by property name (string) — AOT-safe via JsonTypeInfo<T>. Supports dotted paths.
.OrderBy(name, direction[, jsonTypeInfo])	Sort by property name with a runtime direction string ("asc"/"ascending"/"desc"/"descending", case-insensitive; empty defaults to ascending).
.GroupBy(keySelector)	Group into one row per key for an aggregate projection — see Grouping & aggregation.
.Paginate(offset, take)	Limit results with SQL LIMIT/OFFSET.
.Select(selector, resultTypeInfo?)	Project into a different shape via JSON object construction.
.Project(fields[, jsonTypeInfo])	Project a runtime-chosen field list (e.g. "name,email") into IDocumentQuery<JsonObject> — AOT-safe. Ideal for REST sparse fieldsets; no DTO required. Supports scalar functions with an alias: "name, lower(email) as email, year(created) as yr" (all providers).

Terminal methods (execute SQL)

Method	Returns	Description
.ToList()	Task<IReadOnlyList<T>>	Materialize all results into a list.
.ToAsyncEnumerable()	IAsyncEnumerable<T>	Stream results one-at-a-time without buffering.
.Count()	Task<long>	Count matching documents.
.Any()	Task<bool>	Check if any documents match.
.ExecuteDelete()	Task<int>	Delete matching documents and return count deleted.
.ExecuteUpdate(property, value)	Task<int>	Update a property on all matching documents and return count updated.
.Max(selector)	Task<TValue>	Maximum value of a property.
.Min(selector)	Task<TValue>	Minimum value of a property.
.Sum(selector)	Task<TValue>	Sum of a property.
.Average(selector)	Task<double>	Average of a property.
.PageResult(page, pageSize, zeroBased?)	Task<PagedResults<T>>	Run the query and return records + total count in one call. 1-based by default.
.ToCursorPage(cursor, take)	Task<CursorPage<T>>	One forward seek/keyset page; null cursor for the first page, NextCursor null on the last. See Pagination.
.ToCursorStream(pageSize?)	IAsyncEnumerable<T>	Walk every cursor page automatically — a resumable full scan with no deep-offset cost.
.ToQueryString()	DocumentQueryString	Build the SQL (or MongoDB BSON) the query would run without executing it — see Inspecting the generated query.

Expression-based queries

Property names are resolved from JsonTypeInfo metadata, so [JsonPropertyName] attributes and naming policies are respected automatically. The expression API is provider-agnostic — the same C# expressions work across all providers; only the generated SQL differs.

// Equality and comparisons
var results = await store.Query<User>().Where(u => u.Name == "Alice").ToList();
var older = await store.Query<User>().Where(u => u.Age > 30).ToList();

// Logical operators
var results = await store.Query<User>().Where(u => u.Age == 25 && u.Name == "Alice").ToList();
var results = await store.Query<User>().Where(u => u.Name == "Alice" || u.Name == "Bob").ToList();

// Null checks
var noEmail = await store.Query<User>().Where(u => u.Email == null).ToList();

// String methods
var results = await store.Query<User>().Where(u => u.Name.Contains("li")).ToList();
var results = await store.Query<User>().Where(u => u.Name.StartsWith("Al")).ToList();

// Nested properties
var results = await store.Query<Order>().Where(o => o.ShippingAddress.City == "Portland").ToList();

// Collection queries with Any()
var results = await store.Query<Order>()
    .Where(o => o.Lines.Any(l => l.ProductName == "Widget"))
    .ToList();
var results = await store.Query<Order>()
    .Where(o => o.Tags.Any(t => t == "priority"))
    .ToList();

// Collection queries with Count()
var results = await store.Query<Order>().Where(o => o.Lines.Count() > 1).ToList();
// Property form — collection .Count / array .Length work the same as .Count()
var empty = await store.Query<Order>().Where(o => o.Lines.Count == 0).ToList();

// DateTime comparisons (ISO 8601 formatted)
var cutoff = new DateTime(2025, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var upcoming = await store.Query<Event>().Where(e => e.StartDate > cutoff).ToList();

// Captured variables
var targetName = "Alice";
var results = await store.Query<User>().Where(u => u.Name == targetName).ToList();

Scalar functions in predicates

Where predicates translate a library of scalar functions to each backend’s native form — relational providers emit native SQL, MongoDB uses $expr aggregation, CosmosDB uses native NoSQL functions, and the in-memory providers (LiteDB/IndexedDB) evaluate them directly. The same C# works everywhere.

String functions

.Where(u => u.Name.ToLower() == "alice")        // ToUpper, ToLowerInvariant, ToUpperInvariant
.Where(u => u.Name.Length == 5)
.Where(u => u.Name.Trim() == "Alice")           // TrimStart, TrimEnd (no-arg)
.Where(u => u.Name.Substring(0, 3) == "Ali")    // Substring(start) or Substring(start, length)
.Where(u => u.Name.Replace("i", "y") == "Alyce")
.Where(u => u.Name.IndexOf("ice") == 2)
.Where(u => string.IsNullOrEmpty(u.Name))
.Where(u => (u.FirstName + u.LastName) == "AliceSmith")   // string concatenation

These compose with the existing Contains/StartsWith/EndsWith — e.g. u.Name.ToLower().Contains("li").

Math functions

.Where(p => Math.Abs(p.Balance) > 100)
.Where(p => Math.Round(p.Price) == 10)
// also: Ceiling, Floor, Sqrt, Pow, Sign

SQLite: Abs and Round are always available; Ceiling/Floor/Sqrt/Pow require a SQLite build with math functions enabled.

Date and time parts

.Where(e => e.StartDate.Year == 2026)
.Where(e => e.StartDate.Month == 7)
// also: Day, Hour, Minute, Second  (DateTime and DateTimeOffset)

Flag enums

Flag enums (stored numerically — the System.Text.Json default) support membership tests. HasFlag and the (x & flag) == flag idiom both lower to the same bitwise test (BITAND on Oracle, $bitsAllSet on MongoDB):

[Flags] enum Permissions { None = 0, Read = 1, Write = 2, Delete = 4, Admin = Read | Write | Delete }

.Where(a => a.Permissions.HasFlag(Permissions.Write))
.Where(a => (a.Permissions & Permissions.Write) == Permissions.Write)

Do not enable JsonStringEnumConverter if you query flags — bitwise tests require the numeric representation.

String-stored enums

Plain (non-flag) enum comparisons work whether the enum is stored numerically (the default) or as a string via JsonStringEnumConverter. ==, !=, and in bind the exact member name the converter persisted, on both the LINQ and string surfaces:

// Options: new JsonSerializerOptions { Converters = { new JsonStringEnumConverter() } }
.Where(t => t.Level == Priority.High)     // matches the stored "High"
.Where("Level == 'High'")                 // string grammar, same result

Only flag enums require numeric storage (see the note above); ordered comparisons (>, <) on a string-stored enum compare member names lexicographically, since that is all the database holds.

Phonetic search — DocumentFunctions.Soundex

var smiths = await store.Query<Person>()
    .Where(p => DocumentFunctions.Soundex(p.Name) == DocumentFunctions.Soundex("Smith"))
    .ToList();   // matches "Smith", "Smyth", "Smithe", …

Soundex support per provider:

Provider	Mechanism
SQL Server, MySQL, Oracle	native SOUNDEX()
SQLite	registered connection UDF (the bundled build omits SQLITE_SOUNDEX)
PostgreSQL	native soundex() — set EnableFuzzyStringMatch = true on PostgreSqlDatabaseProvider to provision the fuzzystrmatch extension
LiteDB, IndexedDB	the canonical C# implementation runs in-memory
DuckDB, CosmosDB, MongoDB	no native/registered soundex — use the computed stored-field pattern below

Computed stored-field pattern (works on every provider, recommended for CosmosDB/MongoDB/DuckDB and at scale — the equality query is indexable). Store a precomputed Soundex key and query it, computing the search term’s key in C# first:

options.OnBeforeInsert<Person>((p, ct) => { p.NameSoundex = DocumentFunctions.Soundex(p.Name); return Task.CompletedTask; });

var key = DocumentFunctions.Soundex("Smith");
var matches = await store.Query<Person>().Where(p => p.NameSoundex == key).ToList();

Composable full-text — DocumentFunctions.LuceneMatch / LuceneScore

Full-text as a composable predicate (rather than the terminal store.FullTextSearch(...) call). LuceneMatch(field, luceneQuery) filters inside a Where and LuceneScore(field, luceneQuery) ranks inside an OrderBy/projection, both over an existing MapFullTextProperty index. The Lucene grammar covers terms, "phrases", AND/OR/NOT, grouping, prefix foo*, fuzzy foo~, proximity "a b"~5, and boost foo^2.

var hits = await store.Query<Article>()
    .Where(a => a.Category == "tech"
             && DocumentFunctions.LuceneMatch(a.Body, "orleans AND grain NOT deprecated"))
    .OrderByDescending(a => DocumentFunctions.LuceneScore(a.Body, "orleans grain"))
    .Take(20)
    .ToList();

// string grammar (AOT-safe): lucenematch(...) / lucenescore(...)
store.Query<Article>().Where("lucenematch(body, 'title:quick AND brown~')");

Supported on SQLite, PostgreSQL, MySQL, SQL Server (match + score), Oracle (match only), and the in-memory providers (full grammar); DuckDB/CosmosDB/MongoDB use FullTextSearch instead. Operators a provider can’t express throw NotSupportedException. See Full-Text Search → Composable queries for the full capability matrix.

Custom function translations

Register your own method → SQL function mapping for the relational providers. The exemplar captures the target method (so it stays trim/AOT-safe — no reflection by name):

options.MapFunctionTranslation(() => MyFunctions.Reverse(default!), "REVERSE");

// MyFunctions.Reverse has a real body, so LiteDB/IndexedDB run it directly;
// the relational providers emit REVERSE(...). Ensure the function (or a UDF) exists on the database.
var rev = await store.Query<Code>().Where(c => MyFunctions.Reverse(c.Value) == "ZYX").ToList();

Counting

var count = await store.Query<User>().Where(u => u.Age == 25).Count();

// Check existence
var any = await store.Query<User>().Where(u => u.Name == "Alice").Any();

// Raw SQL count (SQLite example)
var count = await store.Count<User>(
    "json_extract(Data, '$.age') > @minAge",
    new { minAge = 30 });

Grouping & aggregation

GroupBy rolls the filtered set up into one output row per group. Use g.Key for the group value and the Sql group aggregates — g.Count(), g.Sum(x => x.Total), g.Avg, g.Min, g.Max — in the projection. The whole-set terminals (.Count(), .Sum(), .Average() above) remain the simplest path for a single total over the entire filtered set; reach for GroupBy when you want a total per key.

// Revenue + order count per status
var rollup = await store.Query<Order>()
    .Where(o => o.CreatedAt >= since)
    .GroupBy(o => o.Status)                              // group key = a JSON property
    .Select(g => new StatusRollup
    {
        Status  = g.Key,                                // the group key
        Count   = g.Count(),
        Revenue = g.Sum(o => o.Total),
        AvgLine = g.Avg(o => o.Total)
    })
    .OrderByDescending(r => r.Revenue)                  // order the grouped rows (by an output column)
    .ToList();

The group key can be a nested property (o => o.ShippingAddress.Country), a derived scalar you never declared a column for (o => o.CreatedAt.Month for a “revenue by month” rollup), or an anonymous type for a multi-column key:

.GroupBy(o => new { o.Status, o.Region })              // g.Key.Status / g.Key.Region

Filtering groups — Having

Having filters the groups themselves by an aggregate (SQL HAVING). Call it more than once to AND predicates:

var bigStatuses = await store.Query<Order>()
    .GroupBy(o => o.Status)
    .Having(g => g.Sum(o => o.Total) > 10_000)
    .Select(g => new StatusRollup { Status = g.Key, Revenue = g.Sum(o => o.Total) })
    .ToList();

Grouped results are ordinary rows — OrderBy (over an output column), Paginate, ToList, ToAsyncEnumerable, Count (number of groups), and ToQueryString all flow through. For hot rollups, index the group-key path with MapIndexedProperty or a materialized computed property.

String grammar

GroupBy("field") opens the string-grammar surface, mirroring the string Where/Project API. The aggregate functions count(), sum(x), avg(x), min(x), max(x) are available (each requires an alias) and bare identifiers resolve to the group key:

var rows = await store.Query<Order>()
    .GroupBy("status")
    .Having("sum(total) > 10000")
    .Project("status, count() as orders, sum(total) as revenue")   // → JsonObject rows
    .ToList();

The LINQ and string surfaces lower to the same GROUP BY / HAVING SQL. String grouping is a single-key, plain-property surface; multi-column and derived keys use the typed GroupBy(keySelector) form.

Provider support

Provider	Grouping	Notes
SQLite / SQLCipher	✅ Push-down	GROUP BY json_extract + HAVING + grouped ORDER BY + multi/derived keys.
PostgreSQL / MySQL	✅ Push-down	Same portable ANSI SQL.
SQL Server / Oracle	✅ Push-down	Same; grouped OrderBy + paging valid.
DuckDB	✅ Push-down	Analytics engine — grouping is its wheelhouse.
MongoDB / Cosmos	⚠️ Client-side	Grouped like their Select: the filtered set is read and aggregated in memory. String grammar is relational-only.
LiteDB / IndexedDB	⚠️ Client-side	In-memory LINQ grouping; loads the matched set — pre-filter with Where. Typed surface only.
Azure Table / DynamoDB	❌ Throws	Key-partitioned, no server grouping — NotSupportedException, no silent scan.

Ordering

Sort results at the SQL level using .OrderBy() and .OrderByDescending().

// Ascending order
var youngest = await store.Query<User>().OrderBy(u => u.Age).ToList();

// Descending order
var oldest = await store.Query<User>().OrderByDescending(u => u.Age).ToList();

// Combined with filter
var results = await store.Query<User>()
    .Where(u => u.Age > 25)
    .OrderBy(u => u.Name)
    .ToList();

// With streaming
await foreach (var user in store.Query<User>().OrderByDescending(u => u.Age).ToAsyncEnumerable())
{
    Console.WriteLine(user.Name);
}

Generated SQL (SQLite example):

SELECT Data FROM documents WHERE TypeName = @typeName
ORDER BY json_extract(Data, '$.age') ASC;

Note

The table name documents is the default. When using table-per-type mapping, the query targets the mapped table and the TypeName filter is omitted for dedicated tables. The generated SQL varies by provider — each provider uses its own JSON extraction syntax.

String-based OrderBy (dynamic sort)

When the sort column is chosen at runtime (e.g. a user clicks a column header), use the string-based overloads. They resolve the property through JsonTypeInfo<T> — no runtime reflection on T, so they are AOT-safe.

Tip

The jsonTypeInfo argument is optional on every string overload (Where, OrderBy, OrderByDescending, Project). When omitted, the query reuses the JsonTypeInfo<T> it resolved when it was created (from Query(ctx.User) or the registered JsonSerializerContext) — so store.Query(ctx.User).OrderBy("Name") works without re-passing the context. The examples pass it explicitly for clarity.

// Sort by CLR property name
var results = await store.Query<User>()
    .OrderBy("Name", ctx.User)
    .ToList();

// Or by JSON property name (matches the naming policy on JsonSerializerOptions)
var results = await store.Query<User>()
    .OrderBy("name", ctx.User)
    .ToList();

// Descending overload
var results = await store.Query<User>()
    .OrderByDescending("Age", ctx.User)
    .ToList();

// Nested path
var orders = await store.Query<Order>()
    .OrderBy("ShippingAddress.City", ctx.Order)
    .ToList();

// Driven by external input (e.g. an API query string)
var sortColumn = request.Query["sort"];  // "name", "age", etc.
var results = await store.Query<User>()
    .Where(u => u.Active)
    .OrderBy(sortColumn, ctx.User)
    .ToList();

// Direction as a runtime string too (e.g. "?sort=name&dir=desc").
// Accepts "asc"/"ascending"/"desc"/"descending" (case-insensitive);
// an empty/null/whitespace direction defaults to ascending.
var results = await store.Query<User>()
    .OrderBy(sortColumn, request.Query["dir"], ctx.User)
    .ToList();

Matching rules: case-insensitive against either the CLR property name or the JSON property name (after naming policy). Dotted segments traverse nested types — each nested type must also be registered in your JsonSerializerContext. Throws ArgumentException if a segment doesn’t resolve. The OrderBy(name, direction, jsonTypeInfo) overload parses the direction string and delegates to the OrderBy / OrderByDescending string overloads; an unrecognized direction throws ArgumentException.

String-based Where (dynamic filter)

When the filter itself is supplied at runtime (a REST ?filter= parameter, a saved view, an admin search box), Where(string, JsonTypeInfo<T>) parses a small expression language into the same expression tree a compiled predicate produces — so it runs through the existing translator and stays AOT-safe (it never calls Compile() and resolves fields through JsonTypeInfo).

var open = await store.Query<User>()
    .Where("Age >= 30 and Status == 'open'", ctx.User)
    .ToList();

// Combines with compiled predicates
var results = await store.Query<User>()
    .Where(u => u.Active)
    .Where(request.Query["filter"], ctx.User)
    .ToList();

Supported syntax:

• Logical and, or, not, and parentheses.
• Comparisons == (or =), != (or <>), >, >=, <, <=. Relational operators are rejected for string/bool/Guid fields.
• field is null / field is not null (and field == null / field != null).
• field in (a, b, c).
• Predicate functions contains(field, 'x'), startsWith(field, 'x'), endsWith(field, 'x'), isnullorempty(field), hasflag(field, 'Flag').
• Scalar functions on either side of a comparison — lower/upper, length, trim/ltrim/rtrim, substring(f, start[, len]), replace(f, 'a', 'b'), indexof(f, 'x'), abs/ceiling/floor/round/sqrt/sign, year/month/day/hour/minute/second, soundex — and they nest:

.Where("lower(name) = 'alice'", ctx.User)
.Where("length(trim(name)) > 5", ctx.User)
.Where("year(startDate) = 2026", ctx.Event)
.Where("hasflag(permissions, 'Write')", ctx.Account)
.Where("soundex(name) = soundex('Smith')", ctx.Account)

Field names follow the same rules as the string OrderBy (case-insensitive CLR or JSON name, dotted paths). String literals use single or double quotes; double the quote to escape ('O''Brien'). Literals are coerced to each field’s CLR type. The same grammar (and the same AOT-safe translation) backs every backend. Syntax errors and unknown fields throw ArgumentException.

Interpolated filters (parameterized values)

When the filter shape is fixed but its values come from code, use an interpolated string. Each {value} hole is captured as a strongly-typed argument and bound as a parameter — never formatted into the filter text — so you don’t quote strings, you don’t escape embedded quotes, and a hostile value can’t tamper with the filter (the InterpolatedSql / Dapper pattern):

var status = request.Query["status"];   // e.g. "open"
var minAge = 30;

var open = await store.Query<User>()
    .Where($"Age >= {minAge} and Status == {status}", ctx.User)
    .ToList();

{status} is treated as a literal value, so a value like open' or '1'=='1 matches a status equal to that string rather than rewriting the predicate. Holes are valid anywhere a literal would appear — a comparison right-hand side, an in (...) list, or a string-function argument (contains(Email, {fragment})) — but never as a field name. Each value is coerced to the field’s CLR type (a string hole into a Guid/DateTime/enum field parses just as a quoted literal would; a null value becomes an is null check).

An interpolated string literal binds to this overload in preference to the raw Where(string) overload, so the two coexist: pass a plain string variable (e.g. the raw ?filter= text) to use the parsed-string form, and an interpolated $"..." to capture values safely.

Set membership — WhereIn / WhereNotIn

WhereIn(selector, values) filters to documents whose property is one of an in-memory collection of values (the IN pattern); WhereNotIn is its complement (NOT IN). The collection is passed as a single value and lowered to each store’s native construct — relational IN (…), Cosmos IN, MongoDB $in, LiteDB/IndexedDB in-memory — rather than being expanded into the query text, so one call behaves identically across every provider.

var statuses = new[] { "Open", "Pending", "Review" };

var open = await store.Query<Order>()
    .WhereIn(o => o.Status, statuses)
    .ToList();

var rest = await store.Query<Order>()
    .WhereNotIn(o => o.Status, statuses)
    .ToList();

Null handling

The optional NullHandling argument controls how nulls in the value set are treated (default Ignore):

NullHandling	Effect
Ignore (default)	Strip nulls from the set. Removes the classic NOT IN (…, NULL) “matches no rows” trap; a null field simply never matches.
Match	A null in the set is explicit intent about null fields: WhereIn also matches rows whose field is null (… OR field IS NULL); WhereNotIn also excludes them (… AND field IS NOT NULL).
Raw	Pass the set through untouched and inherit the store’s native three-valued logic. Faithful to raw SQL, but a null in the set means different things across providers — prefer Ignore/Match.

// alice's rows, plus rows with no assignee
var mine = await store.Query<Order>()
    .WhereIn(o => o.AssignedTo, new string?[] { "alice", null }, NullHandling.Match)
    .ToList();

Notes

• Value types: works with string, numeric types (int/long/decimal/…), Guid, enum, and DateTime/DateTimeOffset — the same scalar types a == comparison supports. Guids are matched as their JSON string form and enums as their underlying numeric value, consistently across providers.
• Empty set is well-defined: WhereIn matches nothing, WhereNotIn matches everything (no IN () syntax error).
• A string property-name overload — WhereIn("Status", values) — mirrors the string OrderBy/Where helpers for runtime-chosen fields.
• The string filter’s field in (…) form lowers through the same path, so Where("Status in ('Open','Pending')") and WhereIn(o => o.Status, …) produce identical native queries.

Pagination

.Paginate(offset, take)

Paginate(offset, take) appends pagination to the generated SQL. It is a builder method that does not execute the query — it stores state until a terminal method is called.

// First page (items 0-19)
var page1 = await store.Query<User>()
    .OrderBy(u => u.Name)
    .Paginate(0, 20)
    .ToList();

// Second page (items 20-39)
var page2 = await store.Query<User>()
    .OrderBy(u => u.Name)
    .Paginate(20, 20)
    .ToList();

// With filtering
var page = await store.Query<User>()
    .Where(u => u.Age >= 18)
    .OrderBy(u => u.Age)
    .Paginate(0, 10)
    .ToList();

// With projection
var page = await store.Query<User>()
    .OrderBy(u => u.Name)
    .Paginate(0, 10)
    .Select(u => new UserSummary { Name = u.Name, Email = u.Email })
    .ToList();

// With streaming
await foreach (var user in store.Query<User>()
    .OrderBy(u => u.Name)
    .Paginate(0, 50)
    .ToAsyncEnumerable())
{
    Console.WriteLine(user.Name);
}

.PageResult(page, pageSize) — records + total count

PageResult is a terminal extension that runs the query and returns a PagedResults<T> envelope containing the page records and the total count across all pages — the typical shape for paged REST/UI responses.

public record PagedResults<T>(
    IEnumerable<T> Records,
    int TotalCount,
    int Page,
    int PageSize
);

// 1-based by default — page 1 is the first page
var result = await store.Query<User>()
    .Where(u => u.Active)
    .OrderBy(u => u.Name)
    .PageResult(page: 1, pageSize: 20);

Console.WriteLine($"Page {result.Page} of ~{Math.Ceiling((double)result.TotalCount / result.PageSize)}");
foreach (var user in result.Records) { ... }

// Zero-based opt-in — page 0 is the first page
var result = await store.Query<User>()
    .OrderBy(u => u.Name)
    .PageResult(page: 0, pageSize: 20, zeroBased: true);

• TotalCount reflects the current Where predicates (and any global query filters) — pagination state is ignored when counting, so the total spans all pages, not just the returned slice.
• Any prior .Paginate(...) call on the query is overridden by PageResult.
• Validation: pageSize must be greater than zero; page must be >= 1 (or >= 0 when zeroBased: true). Otherwise throws ArgumentOutOfRangeException.

.ToCursorPage(cursor, take) — seek / keyset pagination

ToCursorPage is a forward-only, seek-based alternative to offset paging. Instead of an ever-growing OFFSET, each page hands back an opaque cursor that anchors the next page to where the last one ended. The keyset is derived from the query’s own OrderBy (an Id tiebreaker is appended automatically so the order is always total). Pass null for the first page; a null NextCursor marks the last page.

string? cursor = null;
do
{
    var page = await store.Query<Order>()
        .Where(o => o.Status == "open")
        .OrderByDescending(o => o.CreatedAt)   // the cursor keyset is DERIVED from this OrderBy
        .ToCursorPage(cursor, take: 50);

    Render(page.Items);
    cursor = page.NextCursor;                  // null ⇒ last page
}
while (cursor != null);

CursorPage<T> carries the Items and the NextCursor (plus a convenience HasMore). There is no total count — a cursor page skips the extra COUNT(*) round-trip that PageResult pays.

Auto-following stream. ToCursorStream walks every page for you — a resumable full scan that never pays deep-offset cost:

await foreach (var order in store.Query<Order>()
    .OrderByDescending(o => o.CreatedAt)
    .ToCursorStream(pageSize: 200))
{
    Export(order);
}

Offset vs cursor — which to use

Use offset (Paginate / PageResult) when…	Use cursor (ToCursorPage) when…
You need a page number (“page 7 of 90”) or a total count	You page deep, forever-scroll, or export large stable sets
The UI lets users jump to an arbitrary page	You only ever move forward, one page at a time
Result sets are small and shallow	Per-page cost must stay flat regardless of depth
—	Rows are inserted/removed between page fetches (offset drifts; keyset doesn’t)

Why cursor paging is cheaper at depth. OFFSET N scans and discards N rows on every relational backend, so page 500 is measurably slower than page 1. A keyset seek (WHERE (sortkey, Id) > (…) ORDER BY … LIMIT n) is O(log n) per page with the right index — regardless of depth — and it doesn’t skip or duplicate rows when documents are written between fetches.

Index the sort key

Keyset is only O(log n) if an index covers the sort key (plus Id). Add MapIndexedProperty (or a MapComputedProperty(indexed: true)) on the property you order by for hot cursor paths. Without an index it is still correct — it just scans, the same as offset.

Rules and caveats:

• Filters and ordering must be identical across page calls. A cursor is only valid for the exact query shape that produced it — a shape hash over the OrderBy catches the common “wrong sort” mistake and throws InvalidOperationException. Toggling IgnoreQueryFilters or changing the Where between pages is unsupported.
• Forward-only. No previous/last and no random access in v1.
• Not after Select / Project / GroupBy. Those drop the Id and raw sort columns the keyset needs, so they throw NotSupportedException.
• take must be > 0 (and ≤ 10,000); otherwise ArgumentOutOfRangeException.
• NULL sort keys. A keyset boundary that lands on a NULL sort value can skip rows — order by non-nullable columns for cursor paths (the Id tiebreaker is always non-null).
• Opaque, not secret. The token base64url-encodes the trailing row’s sort-key values (e.g. a timestamp) — fine for the common case; don’t treat it as confidential.

Provider support (11.0). Keyset cursors work on every relational provider (SQLite, PostgreSQL, MySQL, SQL Server, Oracle, DuckDB) and on LiteDB, IndexedDB and MongoDB (the document/in-memory providers page the keyset client-side). The key-partitioned NoSQL providers (Cosmos, DynamoDB, Azure Table) don’t opt in yet and throw NotSupportedException — native-continuation-token support for those is a follow-up.

Bulk delete with ExecuteDelete

Delete documents matching a predicate in a single SQL DELETE — no need to query first.

// Simple predicate — returns number of deleted rows
int deleted = await store.Query<User>().Where(u => u.Age < 18).ExecuteDelete();

// Complex predicates
int deleted = await store.Query<Order>()
    .Where(o => o.ShippingAddress.City == "Portland" || o.Status == "Cancelled")
    .ExecuteDelete();

// Captured variables
var cutoffAge = 65;
int deleted = await store.Query<User>().Where(u => u.Age > cutoffAge).ExecuteDelete();

Bulk update with ExecuteUpdate

Update a single property on all matching documents in a single SQL UPDATE — no deserialization needed.

// Update a scalar property on filtered docs
int updated = await store.Query<User>()
    .Where(u => u.Age < 18)
    .ExecuteUpdate(u => u.Age, 18);

// Update a nested property
int updated = await store.Query<Order>()
    .Where(o => o.ShippingAddress.City == "Portland")
    .ExecuteUpdate(o => o.ShippingAddress.City, "Eugene");

// Set a property to null
int updated = await store.Query<User>()
    .Where(u => u.Name == "Alice")
    .ExecuteUpdate(u => u.Email, null);

// Update all documents of a type (no Where)
int updated = await store.Query<User>().ExecuteUpdate(u => u.Age, 0);

Raw SQL queries

Raw SQL queries use provider-specific syntax. The examples below show SQLite; other providers use their own JSON functions (e.g. JSON_VALUE for SQL Server, JSON_EXTRACT for MySQL, #>> for PostgreSQL).

var results = await store.Query<User>(
    "json_extract(Data, '$.name') = @name",
    parameters: new { name = "Alice" });

// With dictionary parameters (fully AOT-safe)
var parms = new Dictionary<string, object?> { ["name"] = "Alice" };
var results = await store.Query<User>(
    "json_extract(Data, '$.name') = @name",
    parameters: parms);

// Streaming with raw SQL
await foreach (var user in store.QueryStream<User>(
    "json_extract(Data, '$.name') = @name",
    parameters: new { name = "Alice" }))
{
    Console.WriteLine(user.Name);
}

Inspecting the generated query — ToQueryString()

Call .ToQueryString() on any query to see the provider query it would run, without executing it — handy for debugging, logging, and learning how an expression translates. It works for both the LINQ and string-based Where forms (they share the same pipeline).

var qs = store.Query<User>()
    .Where(u => u.Age > 28)
    .OrderByDescending(u => u.Age)
    .Paginate(0, 10)
    .ToQueryString();

qs.Sql;        // "SELECT Data FROM "documents" WHERE TypeName = @typeName AND
               //  (json_extract(Data, '$.age') > @p0) ORDER BY json_extract(Data, '$.age') DESC LIMIT 10 OFFSET 0;"
qs.Parameters; // { ["@typeName"] = "User", ["@p0"] = 28 }

// ToString() renders the values as a comment header above the SQL — copy/paste ready:
Console.WriteLine(qs);
// -- @typeName='User'
// -- @p0=28
// SELECT Data FROM "documents" WHERE TypeName = @typeName AND (json_extract(Data, '$.age') > @p0) ...

ToQueryString() reflects the .ToList() form of the query — .Where, .OrderBy, .Paginate, .Select(...), and .Project("...") are all included.

Provider support (DocumentQueryString):

Provider	Sql contains	Parameters
SQLite, SQL Server, PostgreSQL, MySQL, Oracle, DuckDB	Parameterized SQL (provider JSON dialect)	name → value map
Cosmos	Cosmos SQL	name → value map
MongoDB	The rendered BSON filter as JSON (or the full find command when ordering/pagination is set)	empty (values are inlined in the BSON)
LiteDB, IndexedDB	—	throws NotSupportedException (queries are evaluated in-memory, no query language)

The same NotSupportedException applies to client-side projections — after .Select(...)/.Project("...") on the document providers (MongoDB/Cosmos/LiteDB/IndexedDB), where the projection runs in C# rather than in the query.

Supported Expression Reference

The expression API is provider-agnostic. The SQL output below shows SQLite syntax — other providers generate equivalent SQL using their native JSON functions.

Expression	SQL Output (SQLite)
u.Name == "Alice"	json_extract(Data, '$.name') = @p0
u.Age > 25	json_extract(Data, '$.age') > @p0
u.Age == 25 && u.Name == "Alice"	(... AND ...)
u.Name == "A" || u.Name == "B"	(... OR ...)
!(u.Name == "Alice")	NOT (...)
u.Email == null	... IS NULL
u.Email != null	... IS NOT NULL
u.Name.Contains("li")	... LIKE '%' || @p0 || '%'
u.Name.StartsWith("Al")	... LIKE @p0 || '%'
u.Name.EndsWith("ob")	... LIKE '%' || @p0
o.ShippingAddress.City == "X"	json_extract(Data, '$.shippingAddress.city') = @p0
o.Lines.Any(l => l.Name == "X")	EXISTS (SELECT 1 FROM json_each(...) WHERE ...)
o.Tags.Any(t => t == "priority")	EXISTS (SELECT 1 FROM json_each(...) WHERE value = @p0)
o.Tags.Any()	json_array_length(Data, '$.tags') > 0
o.Lines.Count() > 1	json_array_length(Data, '$.lines') > 1
o.Lines.Count == 0 (property form)	json_array_length(Data, '$.lines') = 0
o.Lines.Count(l => l.Qty > 2)	(SELECT COUNT(*) FROM json_each(...) WHERE ...)
e.StartDate > cutoff	json_extract(Data, '$.startDate') > @p0 (ISO 8601)
u.Name.ToLower() == "x"	LOWER(...) = @p0 (ToUpper → UPPER)
u.Name.Length == 5	LENGTH(...) = @p0
u.Name.Trim() == "x"	TRIM(...) (TrimStart→LTRIM, TrimEnd→RTRIM)
u.Name.Substring(0, 3)	SUBSTR(..., 0 + 1, 3) (0-based normalized)
u.Name.Replace("a","b")	REPLACE(..., @p0, @p1)
u.Name.IndexOf("x")	(INSTR(..., @p0) - 1)
string.IsNullOrEmpty(u.Name)	(... IS NULL OR ... = @p0)
Math.Abs(u.Score) > 5	ABS(...) > @p0 (also Round/Ceiling/Floor/Sqrt/Pow/Sign)
e.StartDate.Year == 2026	CAST(strftime('%Y', ...) AS INTEGER) = @p0 (also Month/Day/Hour/…)
a.Perms.HasFlag(Perms.Write)	((... & @p0) = @p0) (BITAND on Oracle)
(a.Perms & Perms.Write) == Perms.Write	same as HasFlag
DocumentFunctions.Soundex(u.Name)	soundex(...) / native SOUNDEX(...)

Unsupported expressions

The expression visitor translates a subset of C#. Anything below throws NotSupportedException at query time — fall back to raw SQL via Query<T>("...", parameters) when you need them. (Raw SQL is not available on LiteDB or IndexedDB — see Limitations.)

Pattern	Workaround
u.Name.Equals(other, StringComparison.OrdinalIgnoreCase)	Compare u.Name.ToLower() == other.ToLower().
dict.Count (dictionary, not a collection length)	Throws rather than silently emit a dead path. Use .Count() / .Any() for collection/array length.
u.CreatedAt.AddDays(7) > DateTime.UtcNow (date arithmetic)	Date part access (.Year, .Month, …) is supported, but date arithmetic isn’t — compute the cutoff in C# first: var cutoff = DateTime.UtcNow.AddDays(-7); Where(u => u.CreatedAt > cutoff);.
Select(u => new { u.Name }) (anonymous type)	Use a named DTO: Select(u => new UserName { Name = u.Name }).
Select(u => (u.Name, u.Age)) (tuple)	Same — use a named DTO.
GroupBy(o => new { o.Status, o.Country }) (multi-key)	Single-key only. Concatenate or denormalize: GroupBy(o => o.StatusCountryKey).
Join, SelectMany, Distinct, Union	Not a relational store — embed the joined data on the document, or do the join in C# after two queries.
string.Format("{0}-{1}", a, b), interpolation	Use a + b (translated) or build the string in C# before the predicate.
Custom instance methods on your types	Register a translation with MapFunctionTranslation, or inline the logic into the expression.

Query cost — performant vs slow patterns

Same C# can produce wildly different SQL cost. A short field guide:

Pattern	Cost	Why
Where(u => u.Email == x) with index on Email	Fast (B-tree lookup)	Indexed equality.
Where(u => u.Email == x) without index	Linear (full type scan + JSON parse per row)	Always create indexes for predicates you run often.
Where(u => u.Name.StartsWith("Al")) with index	Fast (B-tree range scan)	LIKE 'Al%' uses the index.
Where(u => u.Name.Contains("li"))	Linear, even with an index	LIKE '%li%' — leading wildcard defeats B-trees.
Where(u => u.Name.EndsWith("son"))	Linear, even with an index	Same — LIKE '%son'.
Where(o => o.Lines.Any(l => l.Qty > 1))	Linear, expands child array per row	If frequent, denormalize an indexable boolean property and filter on it.
.Count() / .Any() instead of .ToList().Count	Always preferred	One scalar query vs materializing the full set.
.Select(u => new UserSummary { Name = u.Name })	Cheaper than full deserialize	Builds the projection at SQL level — fewer bytes, no full document parse.
Query<T>().Where(...).ExecuteUpdate(p, v)	One server-side UPDATE	Beats load-modify-save loop every time.
Query<T>().Where(...).ExecuteDelete()	One server-side DELETE	Same.
Any LINQ predicate on LiteDB or IndexedDB	Always linear in C# after a full load	These providers don’t translate predicates. Use SQLite-in-WASM for Blazor when this matters.

For a deeper treatment — batch sizes, streaming semantics, transaction boundaries, provider-specific notes — see Performance.