/DB

SELECT / Query

Query rows from a table with the generated Query() builder. Fluent, parameterized, and streamed row-by-row as an IAsyncEnumerable.

updated 5 Jun 20263 min readv0.3.2View as Markdown

Overview

Every generated entity class exposes a static Query() method that returns a fluent TableQueryBuilder. You attach optional modifiers, provide a connection or transaction, then execute. Results arrive as an IAsyncEnumerable<T> and stream row-by-row, so the full result set is never buffered into memory unless you ask for it.

Fetching All Rows

Call Query() with no arguments to fetch every row in the table.

// fetch all users
await foreach (var user in User.Query()
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Name);
}

Filtered Query

Pass a lambda predicate to Query() to apply a WHERE clause immediately.

// fetch only active users with a score of at least 10
await foreach (var user in User.Query(x => x.Status == "active" && x.Score >= 10)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Name);
}

The predicate is translated to parameterized SQL at build time. Values are captured as SQL parameters, never string-interpolated into the query.

Builder Method Reference

All modifiers are optional and can appear in any order before the execution call.

Method SQL equivalent Notes
.Where(x => ...) WHERE ... Sets the filter predicate, replacing any predicate passed to Query() or a previous .Where()
.OrderBy(x => new object?[] { x.Col1, x.Col2 }) ORDER BY col1, col2 ASC Multi-column ascending sort
.OrderByDesc(x => new object?[] { x.Col1 }) ORDER BY col1 DESC Multi-column descending sort
.Limit(n) LIMIT n Maximum rows returned (alias .Top(n))
.Offset(n) OFFSET n Rows to skip, paired with Limit for pagination
.Select(x => new object?[] { x.Id, x.Name }) SELECT id, name Project specific columns only
.WithConnection(conn) (none) Provide an open DbConnection (required if no transaction)
.WithTransaction(tx) (none) Provide an active DbTransaction (required if no connection)
NOTE
.WithConnection(conn) and .WithTransaction(tx) are mutually exclusive. Provide exactly one before calling ExecuteAsync().

Column Projection

Use .Select() to fetch a subset of columns and cut data transfer.

// select only Id and Name
await foreach (var user in User.Query()
    .Select(x => new object?[] { x.Id, x.Name })
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Id}: {user.Name}");
}

Columns not listed in the projection are left at their default CLR value in the returned instance.

Executing a Query

Streaming with ExecuteAsync

ExecuteAsync() returns IAsyncEnumerable<T>. Rows are yielded one at a time as they arrive from the database driver, which is ideal for large result sets.

// stream rows
await foreach (var user in User.Query(x => x.Score > 100)
    .WithConnection(conn)
    .ExecuteAsync())
{
    await ProcessAsync(user);
}

Materializing with ToListAsync

ToListAsync() is not a builder method. It is a System.Linq.Async extension over the IAsyncEnumerable<T> that ExecuteAsync() returns. It buffers every row into a List<T>. Use it when you need random access or want to hand the result to code that expects a list.

// materialize to a List<T>
using System.Linq; // System.Linq.Async

List<User> topUsers = await User.Query(x => x.Score > 100)
    .OrderByDesc(x => new object?[] { x.Score })
    .Limit(10)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
TIP
ToListAsync() and FirstOrDefaultAsync() come from the System.Linq.Async package and apply to the stream, not the builder. Prefer await foreach over ExecuteAsync() for large tables, and materialize only when you truly need all rows in memory.

Where replaces the predicate

.Where() sets the builder's single WHERE predicate. Calling it after Query(predicate) (or calling it more than once) replaces the predicate rather than AND-ing them. To combine conditions, express them in a single lambda with &&.

// combine conditions in one predicate with &&
List<User> results = await User.Query(x => x.Status == "active" && x.Score > 50)
    .OrderBy(x => new object?[] { x.Name })
    .Limit(25)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

Full Example

// paginated, sorted, projected, filtered query
List<User> page2 = await User.Query(x => x.Status == "active" && x.Score >= 10)
    .OrderByDesc(x => new object?[] { x.Score })
    .Limit(20)
    .Offset(20)
    .Select(x => new object?[] { x.Id, x.Name, x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

See Also