Projections & Streaming

Projections

Project query results into a different shape at the SQL level using .Select() — no full document deserialization needed. The expression API is provider-agnostic; the generated SQL uses each provider’s native JSON functions.

// Flat projection
var results = await store.Query<User>()
    .Where(u => u.Age == 25)
    .Select(u => new UserSummary { Name = u.Name, Email = u.Email })
    .ToList();

// Nested source properties
var results = await store.Query<Order>()
    .Where(o => o.Status == "Shipped")
    .Select(o => new OrderSummary { Customer = o.CustomerName, City = o.ShippingAddress.City })
    .ToList();

// All documents with projection
var results = await store.Query<Order>()
    .Select(o => new OrderDetail { Customer = o.CustomerName, LineCount = o.Lines.Count() })
    .ToList();

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

// Collection methods in projections: Count(), Any(), Count(predicate), Any(predicate)
// .Select(o => new OrderDetail { HasLines = o.Lines.Any(), GadgetCount = o.Lines.Count(l => l.ProductName == "Gadget") })

// The property form (o.Lines.Count / o.Tags.Length) also works and emits the same
// native array-length function as o.Lines.Count(). string.Length / dictionary .Count
// throw NotSupportedException (they are not JSON array lengths) — use .Count() / .Any().

Projection expressions reference

The SQL output below shows SQLite syntax — other providers generate equivalent SQL using their native JSON functions.

Expression	SQL Output (SQLite)
x => new R { A = x.Name }	json_object('name', json_extract(Data, '$.name'))
x => new R { C = x.Nav.Prop }	json_object('c', json_extract(Data, '$.nav.prop'))
x => new R { N = x.Lines.Count() }	json_array_length(Data, '$.lines')
x => new R { N = x.Lines.Count } (property form)	json_array_length(Data, '$.lines')
x => new R { N = x.Lines.Count(l => ...) }	(SELECT COUNT(*) FROM json_each(Data, '$.lines') WHERE ...)
x => new R { B = x.Tags.Any() }	CASE WHEN json_array_length(...) > 0 THEN json('true') ELSE json('false') END
x => new R { B = x.Tags.Any(t => ...) }	CASE WHEN EXISTS (...) THEN json('true') ELSE json('false') END
x => new R { N = x.Lines.Sum(l => l.Qty) }	(SELECT SUM(json_extract(value, '$.qty')) FROM json_each(...))
x => new R { N = x.Lines.Max(l => l.Price) }	(SELECT MAX(json_extract(value, '$.price')) FROM json_each(...))
x => new R { N = x.Lines.Min(l => l.Price) }	(SELECT MIN(json_extract(value, '$.price')) FROM json_each(...))
x => new R { N = x.Lines.Average(l => l.Price) }	(SELECT AVG(json_extract(value, '$.price')) FROM json_each(...))

Runtime field projection (string-based)

When the fields are chosen at runtime — a REST sparse fieldset (?fields=name,email), a configurable export — use Project(fields, JsonTypeInfo<T>). It returns IDocumentQuery<JsonObject>, so no result DTO is needed; rows come back as System.Text.Json.Nodes.JsonObject (reflection-free, AOT-safe).

IReadOnlyList<JsonObject> rows = await store.Query<User>()
    .Where("Age >= 30", ctx.User)
    .OrderBy("Name", ctx.User)
    .Project("Name, Email", ctx.User)
    .ToList();

var name = rows[0]["name"]!.GetValue<string>();

// Pagination, Count, Any and streaming all work on the projected query.
var page = await store.Query<User>().Project("name,email", ctx.User).PageResult(1, 20);

It emits a json_object('name', json_extract(Data, '$.name'), …) projection from the resolved JSON paths. Field names follow the same matching rules as the string OrderBy/Where (case-insensitive CLR or JSON name, dotted paths). Each output key is the leaf JSON name (so ShippingAddress.City → city) unless overridden with as alias; two fields resolving to the same key throw ArgumentException.

Projections can also apply the same scalar functions as the string Where grammar (lower, length, substring, year, soundex, …), which require an alias:

var rows = await store.Query<User>()
    .Project("name, lower(email) as email, length(name) as nameLen, year(created) as year", ctx.User)
    .ToList();

After Project, the query is terminal-shaped — ToList/ToAsyncEnumerable/Count/Any/Paginate are supported, while Where/OrderBy/Select/aggregates throw. Project(string) is supported on every provider: the relational providers build a server-side json_object projection, while CosmosDB, MongoDB, LiteDB, and IndexedDB project client-side (the same compile-free path that powers their in-memory predicates). Scalar functions in projections work on all of them.

Streaming

Use .ToAsyncEnumerable() instead of .ToList() to stream results one-at-a-time without buffering the entire result set into memory.

// Stream all documents
await foreach (var user in store.Query<User>().ToAsyncEnumerable())
{
    Console.WriteLine(user.Name);
}

// Stream with filter and sort
await foreach (var user in store.Query<User>()
    .Where(u => u.Age > 30)
    .OrderBy(u => u.Name)
    .ToAsyncEnumerable())
{
    Console.WriteLine(user.Name);
}

// Stream with projection
await foreach (var summary in store.Query<Order>()
    .Where(o => o.Status == "Shipped")
    .Select(o => new OrderSummary { Customer = o.CustomerName, City = o.ShippingAddress.City })
    .ToAsyncEnumerable())
{
    Console.WriteLine($"{summary.Customer} in {summary.City}");
}

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

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

Note

Streaming on shared-connection providers (SQLite, SQLCipher, DuckDB) holds the per-store semaphore for the lifetime of enumeration — calling other store methods inside the same await foreach will block until it completes. On pooled providers (PostgreSQL, MySQL, SQL Server) the streaming reader uses one connection from the driver pool and does not block concurrent ops on the same store.