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(selector)	Group by property (for aggregate projections with Sql.* markers).
.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.
.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.

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();

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 });

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.

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.