Osy#betaRun it on Osyrin: osyrin.com

Reference / Query

Composing queries

Item.Count(i => i.Qty == Item.Max(j => j.Qty))

The LINQ shapes that put one query, or a function, inside another — a query used as a VALUE inside a predicate, a correlated `Any`, `Select(…).FirstOrDefault()`, `Select(…).DefaultIfEmpty(0).Max()`, a `Func` invoked inside a clause, and a query over a type parameter in a generic class. Each answers what the same LINQ answers over a list in memory, in a function and on a page alike.

preview3 examples compiled by CIquerydatalinq

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 rows

Over 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 parameter

Description#

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#

MessageCauseFix
answers null over NO ROWSMax / 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 fromThe function value was declared with var.Declare it as the Func<…> it is.

See also#

Related

Querying data

How you read data in Osy#. You write C# LINQ; it becomes one SQL statement. The rules that follow from that are the…

Where / Single / Count

Query an entity by writing a predicate over it. The query runs in the database — not a filter over rows you already…

Select (projections)

Reshape what a query returns: one column, an anonymous row, or a `class` you declared. The projection becomes the SQL…

Sum / Average / Min / Max / Count

Fold rows down to a single number — a query or a `List<T>` you already hold. The one thing to know before you use them:…

First / Single / Last / ElementAt

The terminals that return ONE row. They differ in what they promise, and choosing the wrong one is how a bug hides:…

LINQ over a local list

Query a local `List<T>`, `HashSet<T>` or `T[]` — of your own `class` values OR of plain scalars like `string[]` and…

Generic classes

A class can declare type parameters, so one shape serves every type it is used with instead of being copied per entity…