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 whole model: the predicate runs in the database (not over rows you fetched), and a verb that cannot become SQL is refused rather than run quietly over everything.

Summary#

You query data by writing C# LINQ over your entities. There is no query language to learn, no repository to write, and no mapping layer to configure:

entity Customer {
  [Required, MaxLength(80)] string Name;

  security { allow create, read, update when IsAuthenticated || IsAnonymous; }
}

entity Order {
  [Required, MaxLength(40)] string Code;
  decimal Total;
  bool Cancelled;
  Customer? Customer;

  security { allow create, read, update when IsAuthenticated || IsAnonymous; }   // an entity with no security block is denied to everyone
}

List<Order> BigOpenOrders(decimal floor) {
  return Order.Where(o => o.Total > floor && !o.Cancelled)
              .OrderByDescending(o => o.Total)
              .Take(20)
              .ToList();
}

That is one SQL statement. Two things follow from it, and together they are the whole mental model:

The predicate runs in the database. It is not a filter over rows you already fetched. A table with ten million rows costs you the twenty you asked for.
A verb that cannot become SQL is refused, loudly, at compile time — never run quietly over the whole table.

Description#

1. Three things you can query#

The same verbs work over three different sources, and knowing which one you are on tells you what it costs:

Source	What it is	Cost
An entity — `Order.Where(…)`	the table	SQL. You pay for the rows you asked for.
A collection — `order.Lines.Where(…)`	a parent's children (Child collections (navigating a relation))	SQL, correlated to the parent.
A local list — `items.Where(…)`	a `List<T>` you built in code (LINQ over a local list)	memory. You already have the elements.

The spelling is identical on purpose — you do not learn two query languages. But an entity query is a question to the database, and a list query is a loop over what you are holding. No security rule applies to values you constructed yourself, and a list of ten million elements is ten million elements in memory.

2. What order do the verbs go in?#

A query is built the way you would build it in C#: narrow, then order, then page, then finish.

<Entity>
  .Where(o => <predicate>)          // ONE predicate — combine conditions with && inside it
  .OrderBy(o => k).ThenBy(o => k2)  // any number of keys
  .Skip(n).Take(m)                  // a page (the counts may be runtime values)
  .Include(o => o.Lines)            // pre-load related rows
  .ToList();                        // materialise

A chain may carry more than one Where, and they compose — xs.Where(a).Where(b) is xs.Where(a && b), exactly as in LINQ, and it is still one statement. The two lambdas need not name their parameter the same.

And the chain need not all be in one place. Bind it to a var and the clauses you add later still join the SAME query — a chain is deferred until something asks for the answer, exactly as in C#. .ToList() is how you say "read it here". See Query<T>, which is also the type you write when a query crosses a function boundary.

The chain ends in a terminal, and the terminal is what decides the shape of the answer:

You want	Terminal	Page
the rows	`.ToList()`	ToList
one row	`.First()` · `.Single()` · `.Last()` · `.FirstOrDefault()` …	First / Single / Last / ElementAt
a number	`.Count()` · `.Sum(…)` · `.Average(…)` · `.Min/Max(…)`	Sum / Average / Min / Max / Count
a yes/no	`.Any(…)`	Where / Single / Count
a reshaped row	`.Select(o => new T { … })`	Select (projections)
a row per group	`.GroupBy(…).Select(g => …)`	GroupBy (and HAVING)

3. What is NOT there, and why#

The refusals are deliberate, and each is the same principle: a verb either becomes SQL or it is refused. The alternative — quietly fetching the table and finishing the job in memory — is how an app works fine on your laptop and falls over on real data.

TakeWhile / SkipWhile are recognised and refused: they cannot be expressed in SQL. Use Where + OrderBy + Skip/Take. (C# on a database refuses them too.)
All, Contains, Aggregate as query verbs do not exist. All(p) is !Any(!p); for Contains, see Dynamic IN (list.Contains in a query).
Full-text and vector search are not chain verbs — they are predicates inside a Where, and they need a [Searchable] property. See [Searchable].

4. A query sees what you have already written#

A server function commits when it returns (function) — so the rows you create partway through it are not in the database yet. A query in the same function sees them anyway:

List<Order> BusiestFirst() {
  var a = new Order { Code = "A", Total = 20m };
  var b = new Order { Code = "B", Total = 90m };
  var c = new Order { Code = "C", Total = 40m };

  return Order.Where(o => o.Total > 10m)      // …matches the three above AND anything already stored
              .OrderByDescending(o => o.Total)
              .ToList();                       // …and they are ordered together: 90, 40, 20
}

Your pending rows are matched by the Where, sorted into the order you asked for, paged by Skip/Take, and counted by Count(). You do not have to commit first, and you should not: committing early would give up the all-or-nothing guarantee that a fault discards everything the function wrote.

[Test]
void A_query_orders_the_rows_this_function_has_not_committed_yet() {
  var busiest = BusiestFirst();

  Assert.Equal(3, busiest.Count);
  Assert.Equal("B", busiest[0].Code);   // 90 — sorted WITH the pending rows, not appended after them
  Assert.Equal("C", busiest[1].Code);   // 40
  Assert.Equal("A", busiest[2].Code);   // 20
}

A pending edit counts too, not just a pending create. Change a field and the very next Where decides on the value you just wrote: a row your edit now matches is returned, and one it no longer matches is not — so "mark these delivered, then ask which are still outstanding" answers the question you actually asked.

[TestFixture]
void Stored() {
  var o = new Order { Code = "A", Total = 20m };
  UnitOfWork.Commit();                                     // A is now a stored row, Total = 20
}

[Test(Stored)]
void A_pending_edit_decides_the_filter() {
  var order = Order.Single(o => o.Code == "A");
  order.Total = 500m;                           // a pending edit to a STORED row — no UnitOfWork.Commit()

  Assert.Equal(1, Order.Where(o => o.Total > 100m).ToList().Count);   // it joined the set…
  Assert.Empty(Order.Where(o => o.Total < 100m).ToList());            // …and left the one it was in
  Assert.Equal(500m, order.Total);
}

Count() and Any() answer from the same reconciled set, so Where(p).Count() and Where(p).ToList().Count cannot disagree.

A filter on a reference works the same way, on rows and on links that are both still pending. This is the shape worth seeing, because a parent and its children are usually created together — neither exists in the database yet, and the question is still answered from what this function has written.

[Test]
void A_pending_row_is_found_by_the_reference_you_just_set() {
  var mine   = new Customer { Name = "Mine" };
  var theirs = new Customer { Name = "Theirs" };

  var a = new Order { Code = "A", Total = 10m, Customer = mine };
  var b = new Order { Code = "B", Total = 20m, Customer = theirs };

  Assert.Equal(1, Order.Where(o => o.Customer == mine).ToList().Count);    // …and not B

  a.Customer = theirs;                                                     // re-point it, still no UnitOfWork.Commit()
  Assert.Empty(Order.Where(o => o.Customer == mine).ToList());             // it left the set it was in…
  Assert.Equal(2, Order.Where(o => o.Customer == theirs).ToList().Count);  // …and joined the other
}

One honest limit: Sum / Average / Min / Max see only committed rows. A value aggregate does not include rows you created and have not committed — so a total is quietly short by exactly the rows you just added. Count(), Any() and ordinary queries all include them; only the value aggregates do not. If you need one over rows you have just created or changed, UnitOfWork.Commit() first.

5. Where the divergences from C# are#

Faithful C# is the goal, so the handful of places the language deliberately differs are worth knowing, because each one is a bug waiting to happen if you assume otherwise:

Sum / Average / Min / Max return null on an empty set, not zero — because SQL does. See Sum / Average / Min / Max / Count, which is the page that will save you the most debugging.
Average is always decimal, whatever the selector's type.
ThenBy is a synonym for a chained OrderBy. The keys accumulate into one composite sort — a second OrderBy does not re-sort as it would in C#.
Last requires an OrderBy. "The last row" is meaningless without an order, and the database will not guess.