Summary#
A query may stand anywhere a value of its type may — including inside another query's predicate — and the whole thing is still one SQL statement:
entity Item { string Name = ""; int Qty; }
int AtTheMax() => Item.Count(i => i.Qty == Item.Max(j => j.Qty)); // rows holding the largest Qty
int Outranked() => Item.Count(i => Item.Any(j => j.Qty > i.Qty)); // rows some other row beats
int FirstQty() { int v = Item.OrderBy(i => i.Qty).Select(i => i.Qty).FirstOrDefault(); return v; } // 0 over no rows
int MostOrZero() => Item.Select(i => i.Qty).DefaultIfEmpty(0).Max(); // 0 over no rowsOver rows of Qty 50, 150 and 300 these answer 1, 2, 50 and 300 — what new[] { 50, 150, 300 } answers with the
same LINQ.
Signature#
Entity.Count(x => x.F == Entity.Max(y => y.F)) // a query as a value inside a predicate
Entity.Count(x => Entity.Any(y => y.F > x.F)) // a correlated Any
T v = Entity.Select(x => x.F).FirstOrDefault(); // a value type: default(T) over no rows
T v = Entity.Select(x => x.F).DefaultIfEmpty(d).Max(); // d over no rows
Func<Entity, T> f = x => x.F; Entity.Count(x => f(x) > n); // a function value inside a clause
class Rows<T> where T : Entity { int N() => T.Count(x => …); } // a query over a type parameterDescription#
A query used as a value#
Item.Max(j => j.Qty) inside Item.Count(i => …) is a subquery: it is evaluated by the database as part of the
outer statement, not fetched first. It may read the outer row (Item.Any(j => j.Qty > i.Qty)), as a correlated
subquery does.
…OrDefault() over a projected value type#
Item.Select(i => i.Qty).FirstOrDefault() is an int, as in C#: over no rows it answers 0, never null, so it
can be assigned to an int without a guard. Without an OrderBy the database picks which row is first, just as an
unordered sequence does — add one when it matters.
DefaultIfEmpty(v).Max()#
Max, Min and Average over no stored rows have no value. DefaultIfEmpty(v) before them says what to answer
then, exactly as over a list. On a page it may stand in a field, a live var, a render slot or an action; the read
is served like any other read there.
A function value inside a clause#
A Func<Item, …> invoked inside a predicate is composed into it: Item.Count(i => qty(i) > 100) with
qty = i => i.Qty is Item.Count(i => i.Qty > 100). On a page, where the clause runs on the server and only data
reaches it from the browser, this holds for a lambda the code states with an expression body and never assigns
again — a local or a component field.
A query over a type parameter#
Inside class Rows<T> where T : Item, T.Count(…) queries the entity T is bound to, as Set<T>() does in EF:
new ItemRows().Big() with class ItemRows : Rows<Item> counts Item rows.
Examples#
entity Item { int Qty; security { allow read when IsAnonymous || IsAuthenticated; } }
[Route("/stock")] [AllowAnonymous]
component StockPage() {
Func<Item, int> qty = i => i.Qty;
live var most = Item.Select(i => i.Qty).DefaultIfEmpty(0).Max();
live var big = Item.Count(i => qty(i) > 100);
render { Text($"largest {most}, {big} over 100"); }
}entity Item { int Qty; }
class Rows<T> where T : Item { public int Big() => T.Count(i => i.Qty > 100); }
class ItemRows : Rows<Item> { }
int BigItems() => new ItemRows().Big();Errors#
| Message | Cause | Fix |
|---|---|---|
answers null over NO ROWS | Max / Min / Average over stored rows assigned to a value type with no default. | Add DefaultIfEmpty(v) before the aggregate, or read it as int?. |
a lambda needs a target type to bind its parameter from | The function value was declared with var. | Declare it as the Func<…> it is. |
See also#
- Where / Single / Count — the predicate, and
SinglevsFirstOrDefaultvsCount - Select (projections) — projections, and what may precede and follow one
- Sum / Average / Min / Max / Count —
Sum/Average/Min/Max/Count, and null-on-empty - First / Single / Last / ElementAt —
First/Single/Last, and what each does when there is no row - LINQ over a local list — the same verbs over a
List<T>you built yourself - Generic classes — type parameters, constraints and closing a generic base