Projections & Streaming

Projections

Project query results into a different shape at the SQL level via json_object using .Select() — no full document deserialization needed.

// 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") })

Projection expressions reference

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

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
await foreach (var user in store.QueryStream<User>(
    "json_extract(Data, '$.name') = @name",
    parameters: new { name = "Alice" }))
{
    Console.WriteLine(user.Name);
}