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)	Sort ascending by property.
.OrderByDescending(selector)	Sort descending by property.
.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.

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 via json_set() 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.

Expression-based queries

Property names are resolved from JsonTypeInfo metadata, so [JsonPropertyName] attributes and naming policies are respected automatically.

// 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
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:

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

Pagination

Paginate(offset, take) appends LIMIT {take} OFFSET {offset} 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);
}

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 via json_set() — 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

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

Expression	SQL Output
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)