Dynamic IN (list.Contains in a query)

Filter a query by membership in a RUNTIME list — list.Contains(e.Column) inside a Where lowers to SQL `= ANY(@param)`, passing the whole list as one array parameter. The list can be any local List<T> whose element type matches the column; an empty list matches nothing.

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; }
}

[Route("/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.

A held list or dictionary indexed by the row#

A list or dictionary you hold can also be indexed by the row inside a query — prices[i.Code], labels[s.Position] — in a Where, an OrderBy or a projection. The collection is sent the same way, as array parameters (a dictionary's keys and values side by side), and the database looks each row's value up. A key the dictionary does not hold, or an index past the end, makes the query fail as C#'s indexer throws. Keys and values may be text, a number, a bool, a Guid or a DateTime:

entity Part {
  [Required, MaxLength(20)] string Code;
  int Slot;
}

List<Part> CheapestFirst(Dictionary<string, decimal> price) =>
  Part.Where(p => price.ContainsKey(p.Code) && price[p.Code] < 100m).OrderBy(p => price[p.Code]).ToList();

List<string> Labelled(List<string> labels) =>
  Part.Where(p => p.Slot < labels.Count).Select(p => p.Code + ": " + labels[p.Slot]).ToList();