# SELECT / Query

How to query rows from a database table using the generated Query() builder in Socigy.OpenSource.DB v0.1.83.

## Overview

Every generated entity class exposes a static `Query()` method that produces a fluent builder. You attach optional modifiers, provide a connection or transaction, and then execute — receiving results as an `IAsyncEnumerable<T>` that is streamed row-by-row without buffering the entire result set into memory.

## Fetching All Rows

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

```csharp
// C# — 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
// C# — 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 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 ...` | Adds an additional filter; AND-ed with any predicate passed to `Query()` |
| `.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 |
| `.Offset(n)` | `OFFSET n` | Rows to skip (used with `Limit` for pagination) |
| `.Select(x => new object[] { x.Id, x.Name })` | `SELECT id, name` | Project specific columns only |
| `.WithConnection(conn)` | — | Provide an open `DbConnection` (required if no transaction) |
| `.WithTransaction(tx)` | — | 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 select a subset of columns and reduce data transfer.

```csharp
// C# — 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 will be 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 — ideal for large result sets.

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

### Materializing with ToListAsync

`ToListAsync()` is a LINQ extension method that buffers all rows into a `List<T>`. Use it when you need random access or need to pass the result to code that expects a list.

```csharp
// C# — materialize to a List<T>
List<User> topUsers = await User.Query(x => x.Score > 100)
    .OrderByDesc(x => new object[] { x.Score })
    .Limit(10)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

> **TIP** Prefer `await foreach` with `ExecuteAsync()` for large tables. Use `ToListAsync()` only when you truly need all rows in memory at once.

## Chaining Where and the Initial Predicate

You can combine a predicate in `Query()` with one or more `.Where()` calls. All conditions are AND-ed together.

```csharp
// C# — two conditions joined with AND
var results = await User.Query(x => x.Status == "active")
    .Where(x => x.Score > 50)
    .OrderBy(x => new object[] { x.Name })
    .Limit(25)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## Full Example

```csharp
// C# — paginated, sorted, projected, filtered query
var page2 = await User.Query(x => x.Status == "active")
    .Where(x => x.Score >= 10)
    .OrderByDesc(x => new object[] { x.Score })
    .OrderBy(x => new object[] { x.Name })
    .Limit(20)
    .Offset(20)
    .Select(x => new object[] { x.Id, x.Name, x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## See Also

- [WHERE Operators](where) — full list of supported predicate operators
- [Sorting and Pagination](sorting-pagination) — ordering and paging patterns