# SELECT / Query

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

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

```csharp
// 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.

```csharp
// 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.

```csharp
// 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.

```csharp
// 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](https://www.nuget.org/packages/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.

```csharp
// 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 `&&`.

```csharp
// 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

```csharp
// 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

- [WHERE Operators](/database/0.3.2/querying/reading/where) for the full list of supported predicate operators
- [Sorting and Pagination](/database/0.3.2/querying/reading/sorting-pagination) for ordering and paging patterns
- [Aggregates and Scalars](/database/0.3.2/querying/reading/aggregates) for COUNT, SUM, AVG, MIN, MAX