Dynamic IN (list.Contains in a query)

Summary#

Inside a query predicate, list.Contains(e.Column) tests each row's column for membership in a runtime List<T> — exactly the C#/LINQ spelling for SQL IN. It lowers to e.Column = ANY(@p), binding the whole list as one array parameter (stable SQL shape). The list is a local you build at run time; an empty list matches nothing.

Signature#

Entity.Where(e => <list>.Contains(e.<Column>))    // → e.Column = ANY(@p)

Description#

The receiver <list> is any local collection (a new List<T>() you populate, a Text.Split result, …) whose element type is comparable to the column — same rule as a literal IN or ==: exact for string/bool/DateTime, id-coercion between Guid and string, and numeric widening (int→long→decimal→double). An incompatible pair is a compile error.

This is position-sensitive: the same list.Contains(x) written outside a query predicate is the ordinary in-memory list-membership check. It becomes a SQL IN only inside a Where/Any/Count/… predicate, where x is a row column.

A literal list works too and is equivalent: [a, b, c].Contains(e.Column) (rendered as IN (…)); the runtime-list form is the one that lets the set be computed at run time.

Examples#

entity Ticket { string Status; }

List<Ticket> ByIds(List<Guid> ids) {
  return Ticket.Where(t => ids.Contains(t.Id)).ToList();   // t.Id = ANY(@p)
}

List<Ticket> ByStatuses() {
  var open = new List<string>();
  open.Add("New");
  open.Add("InProgress");
  return Ticket.Where(t => open.Contains(t.Status)).ToList();   // empty `open` → no rows
}

entity Tag {
  [Required, MaxLength(50)] string Name;
  bool Active;
  security { allow read, create, update when IsAnonymous || IsAuthenticated; }
}

entity Item {
  [Required, MaxLength(50)] string Title;
  Tag Tag;
  security { allow read, create, update when IsAnonymous || IsAuthenticated; }
}

[Page("/board")]
[AllowAnonymous]
[Render(CSR)]
component Board() {
  live var activeIds = Tag.Where(t => t.Active).Select(t => t.Id).ToList();
  live var work = Item.Where(i => activeIds.Contains(i.Tag.Id)).ToList();   // the set crosses the wire

  render {
    Stack {
      Text("in scope: " + work.Count);
      foreach (var w in work) { Text(w.Title); }
    }
  }
}

The captured list is sent to the server as one query input, so the filter runs in the database over the whole table — not over rows the page had already fetched. And because it is an input, the second read follows the first: when activeIds changes, work re-runs against the new set with nothing to wire up.

Both members are server reads, which osy validate will tell you:

ⓘ reads the SERVER holds: Board.activeIds, Board.work — these re-run when their DATA changes.

See also#

• Union / Concat / Intersect / Except — combining whole row-sets (Union/Intersect/Except)
• List OrderBy (in-memory) — ranking a local list in memory
• The reactivity & lifecycle model — what makes a live var re-run, and what a second one reading the first depends on