/DB

Aggregates & scalars

Run real SQL COUNT, SUM, AVG, MIN, MAX, and single-value scalar reads. Server-side, parameterized, and fully instrumented, from the query builder or the database context.

updated 5 Jun 20262 min readv0.3.2View as Markdown

Aggregates run as a single server-side scalar query (SELECT COUNT(*) …, SELECT SUM("col") …), reusing the same WHERE translation as the rest of the query API. They are available both on the query builder (when you hold a DbConnection) and on the database context (I{Table}Set).

Query builder

Build a query with an optional Where(...), then call a scalar terminal instead of ExecuteAsync():

await using var conn = factory.Create();

long active   = await User.Query(u => u.IsActive).WithConnection(conn).CountAsync();
decimal? rev  = await Order.Query(o => o.Paid).WithConnection(conn).SumAsync<decimal>(o => o.Total);
double?  avg  = await User.Query().WithConnection(conn).AvgAsync<double>(u => u.Age);
DateTime? max = await Login.Query(l => l.UserId == id).WithConnection(conn).MaxAsync<DateTime>(l => l.At);
Guid?    minId= await User.Query().WithConnection(conn).MinAsync<Guid>(u => u.Id);

// A single column's value from the first matching row:
string? email = await User.Query(u => u.Id == id).WithConnection(conn).ScalarAsync<string>(u => u.Email);
Method SQL Returns
CountAsync() SELECT COUNT(*) … long
SumAsync<T>(x => x.Col) SELECT SUM("col") … T?
AvgAsync<T>(x => x.Col) SELECT AVG("col") … T?
MinAsync<T>(x => x.Col) SELECT MIN("col") … T?
MaxAsync<T>(x => x.Col) SELECT MAX("col") … T?
ScalarAsync<T>(x => x.Col) SELECT "col" … (first row) T?
  • The Where(...) predicate is translated and parameterized exactly like a normal query.
  • SUM, AVG, MIN, and MAX return null when no rows match (the aggregate is SQL NULL), so the result type is T?.
  • SumAsync, AvgAsync, MinAsync, and MaxAsync are constrained to value types (where T : struct), meaning numeric and date/time columns. For other shapes, use ScalarAsync<T> or a .sql procedure.
  • The selector must be a single mapped column (x => x.Amount). Anything else throws NotSupportedException. An [Encrypted] column also cannot be aggregated, because encryption is non-deterministic.
  • Every call is reported through the diagnostics pipeline as a SELECT (see Diagnostics).

Database context

The same operations live on each I{Table}Set, take an optional predicate, and run inside the unit-of-work scope (enlisting the ambient transaction automatically):

long active = await db.ExecuteAsync(d => d.Users.CountAsync(u => u.IsActive));

await db.ExecuteTransactionAsync(async d =>
{
    decimal? total = await d.Orders.SumAsync<decimal>(o => o.Total, o => o.UserId == userId);
    DateTime? last = await d.Logins.MaxAsync<DateTime>(l => l.At, l => l.UserId == userId);
    // ...
});

I{Table}Set exposes CountAsync(predicate?), SumAsync<T>(selector, predicate?), AvgAsync, MinAsync, MaxAsync, and ScalarAsync<T>(selector, predicate?).

NOTE
CountAsync is a real SELECT COUNT(*). It does not stream and count rows client-side.

See also