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

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.

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

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

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.

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

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.

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

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(l => l.Qty > 2)	(SELECT COUNT(*) FROM json_each(...) WHERE ...)
e.StartDate > cutoff	json_extract(Data, '$.startDate') > @p0 (ISO 8601)

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.ToLower() == "alice"	Lowercase in C# before the query: var n = name.ToLower(); then compare exactly; or use raw SQL with LOWER(...).
string.IsNullOrEmpty(u.Name)	u.Name == null || u.Name == ""
u.Name.Equals(other, StringComparison.OrdinalIgnoreCase)	Raw SQL with LOWER(...).
Math.Abs(u.Score) > 5	Move computation out: Where(u => u.Score > 5 || u.Score < -5).
u.CreatedAt.AddDays(7) > DateTime.UtcNow	Compute the cutoff in C# first: var cutoff = DateTime.UtcNow.AddDays(-7); Where(u => u.CreatedAt > cutoff);.
(int)u.Age == 25 (explicit cast)	Drop the cast — the type is already int. Boxing via Convert is fine; explicit casts to a different type are not.
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	Build the string in C# before the predicate.
Custom instance methods on your types	Inline the logic into the expression. The visitor only sees the methods listed in the supported table above.

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.