/DB

SELECT / Query

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

updated 3 May 20263 min readv0.1.82

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.

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

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

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

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

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

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

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