Mise Queries

Read queries

A query builder describes a SELECT statement. Add projection, source, filters, joins, and ordering, then give the builder to a DbReader. Building and compiling a query do not execute it.

Select mapped columns

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}");

var result = await reader.ListAsync<CourseRow>(query, cancellationToken);

CourseTbl and CourseRow are defined in Table mappings and Result rows. Repeated Select calls add comma-separated terms in call order. A query must have a projection. From is optional for expressions such as SELECT 1, but can be set only once.

Prefer explicit columns for mapped rows. They make result names and nullability visible and avoid duplicate names introduced by SELECT * over joins.

Build conditionally

Builders are mutable. Add predicates only when the application needs them:

if (minimumCapacity is int capacity)
{
    query.Where($"{CourseTbl.CapacityCol:raw} >= {capacity}");
}

if (!string.IsNullOrWhiteSpace(titlePrefix))
{
    query.Where($"{CourseTbl.TitleCol:raw} LIKE {titlePrefix + "%"}");
}

Here, minimumCapacity is int? and titlePrefix is string?. LIKE wildcard semantics still belong to SQL; use an appropriate escape policy if the prefix should treat % and _ as literal characters.

Create a builder for each operation. Reusing a builder and repeatedly adding clauses accumulates them. A compiled snapshot keeps its SQL and parameter list even when later calls change the builder.

Remove duplicate rows

var campusIds = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CampusIdCol:raw}")
    .Distinct()
    .From($"{CourseTbl.Table:raw}");

Distinct applies to the whole selected row. It does not group courses or choose one course per campus. Use Aggregation for grouped values.

Choose a dialect

Use SqlServerQueryBuilder, PostgreSqlQueryBuilder, SqliteQueryBuilder, MySqlQueryBuilder, or MariaDbQueryBuilder from the matching runtime namespace. The core QueryBuilder also accepts a SqlDialect; its parameterless constructor uses ANSI syntax.

Generated references and child builders must match the intended database. Mise does not translate arbitrary SQL expressions between dialects. See Database dialects.

Execute with the right terminal

Use ListAsync<T> for all mapped rows, FirstOrNotFoundAsync<T> for one row, ScalarAsync<T> for one value, and ExistsAsync to ask whether a row exists. StreamAsync<T> yields mapped rows during enumeration.

See Readers and Streaming for empty results, cancellation, and lifetimes. Filtering, Joins, and Paging and ordering add the main SELECT clauses.