Array / Collection Helper Translation Plan

## Goal

Build out first-class LINQ translation for ClickHouse `Array(T)` and collection-typed
column helpers, complementing PR #15's `Contains(...) -> has(...)` baseline. The end state
should let users write idiomatic LINQ over array columns and have it compile to the native
ClickHouse `Array(*)` function set .
---

## Architecture (target)

Three pieces, one responsibility each:

1. **`ClickHouseArrayMethodTranslator`** — `Query/ExpressionTranslators/Internal/`.
`IMethodCallTranslator` + `IMemberTranslator`. Owns the SQL-expression-level
construction helpers (`TranslateContains`, `TranslateNotEmpty`, `TranslateLength`, and
any future `TranslateIndexOf` / `TranslateElementAt` / …). Registered in
`ClickHouseMethodCallTranslatorProvider` and `ClickHouseMemberTranslatorProvider`.

2. **`ClickHouseArrayLinqTranslator`** — `Query/Internal/`. The LINQ-expression-level
pattern matcher. Owned by `ClickHouseSqlTranslatingExpressionVisitor`, given a
`Func` back-callback for recursive `Visit`. Catches shapes EF
Core normalizes before the `IMethodCallTranslator` chain runs (`Queryable.*` variants,
`AsQueryable()`/`AsEnumerable()` markers, `Select(...).Contains(...)`,
predicate-overload lambdas).

3. **`ClickHouseSqlTranslatingExpressionVisitor`** — thin shell. Just `GenerateLeast`,
`GenerateGreatest`, and a one-line `VisitMethodCall` that delegates to the LINQ
translator.

For higher-order array functions (`arrayMap`, `arrayExists`, `arrayCount`, `arrayFilter`,
`arraySort`, …) the lambda argument is modeled as two new SqlExpression nodes:

- `ClickHouseArrayLambdaExpression(parameter, body)` — renders as ` -> `.
- `ClickHouseArrayLambdaReferenceExpression(name, type, typeMapping)` — sentinel that
stands in for the lambda parameter inside the body. Is a `SqlExpression`, so it threads
through the standard scalar translator chain unchanged when the visitor descends.

Both must be plumbed through:
- `ClickHouseQuerySqlGenerator.VisitExtension` (render `x -> body` / `x`)
- `ClickHouseSqlNullabilityProcessor.VisitCustomSqlExpression` (non-nullable; recurse into
body)
- `Quote()` + `Print()` + `Equals`/`GetHashCode` on the nodes themselves (compiled-query
cache).

---

## Gating & semantics (apply uniformly)

- **Type-mapping-driven gate.** Only fire when `expression.TypeMapping is
ClickHouseArrayTypeMapping`. Don't gate on the CLR type — `T[]`, `List`,
`IEnumerable`, `IList`, `ICollection`, `IReadOnlyList`,
`IReadOnlyCollection` all map to `ClickHouseArrayTypeMapping` via v0.2.0's
`EnumerableToArrayConverter` and should all light up automatically.
- **Structural pre-filter.** Before eagerly visiting `arguments[0]`, confirm it's a
`MemberExpression` or an `EF.Property(entity, "Name")` call (possibly wrapped in
marker methods). Otherwise the recursive `Visit` trips an `EnumerableExpression`
assertion inside EF Core's queryable pipeline on DbSet roots / subqueries. The
Select-then-Contains lambda branch needs the same pre-filter for symmetry.
- **Element-store-type alignment.** Search items must go through
`ApplyTypeMapping(item, arrayTypeMapping.ElementMapping)`. Without it, LINQ-provided
literals carry .NET-default mappings and ClickHouse does an implicit conversion at best
(Int32 vs Int64, FixedString(N) vs String, Enum8 vs String, …).
- **Explicit result mappings.** Bool/UInt8-returning functions (`has`, `notEmpty`,
`arrayExists`, `empty`) and Int32/Int64-returning ones (`length`, `arrayCount`,
`indexOf`) get `_typeMappingSource.FindMapping(typeof(bool|int|long))` passed
explicitly so projection/materialization works.
- **Nullability.** ClickHouse array functions never return NULL on non-nullable Array
columns. Declare `nullable: false` and `argumentsPropagateNullability: [false, …]` on
all of them. (`has(arr, NULL)` returns 0, not NULL; `arrayExists` over a NULL-returning
lambda body is treated as not-true, not NULL.)
- **`IN` vs `has` separation.** `localList.Contains(e.Id)` continues to flow through EF
Core's inline-collection / `IN` path. Only mapped-array-column `Contains` is rewritten.
- **EF Core's `Count > 0 → Any()` optimization.** EF rewrites `arr.Count > 0` (and
similar) to `Any()` *before* the provider sees it, so it emits `notEmpty(...)` not
`length(...) > 0`. Document this in CHANGELOG so users aren't surprised.

---

## Translation surface

### Tier 1 — non-lambda predicates and aggregates (done in PR #15 follow-up)

Already prototyped and tested. Should land as the first follow-up PR.

| LINQ shape | ClickHouse | Notes |
| -------------------------------- | --------------------------------------- | ----- |
| `arr.Contains(value)` | `has(arr, value)` | Enumerable + Queryable + `List.Contains` instance |
| `arr.Any()` | `notEmpty(arr)` | Enumerable + Queryable |
| `arr.Count()` / `.LongCount()` | `length(arr)` | Returns Int32 / Int64 respectively |
| `arr.Length` (member) | `length(arr)` | `T[]` only |
| `arr.Count` (member) | `length(arr)` | `List` + all interface variants |
| `!arr.Any()` | `NOT notEmpty(arr)` | EF negation composes naturally |
| `arr.AsQueryable()` / `AsEnumerable()` | strip wrapper | Visitor returns inner array SqlExpression |

### Tier 2 — predicate overloads (done in PR #15 follow-up)

Requires the lambda machinery (`ClickHouseArrayLambdaExpression` etc.).

| LINQ shape | ClickHouse |
| ------------------------------------- | ------------------------------------------- |
| `arr.Any(x => f(x))` | `arrayExists(x -> f(x), arr)` |
| `arr.Count(x => f(x))` | `arrayCount(x -> f(x), arr)` (Int32) |
| `arr.LongCount(x => f(x))` | `arrayCount(x -> f(x), arr)` (Int64) |
| `arr.Select(x => f(x)).Contains(v)` | `has(arrayMap(x -> f(x), arr), v)` |
| `arr.All(x => f(x))` | `arrayAll(x -> f(x), arr)` |

Lambda body translation: substitute the `ParameterExpression` with a
`ClickHouseArrayLambdaReferenceExpression` (typed with the array's element mapping), then
`Visit(substitutedBody)` so the body flows through the existing scalar translator chain
(so `x => x.ToLower()` becomes `lowerUTF8(x)` via `ClickHouseStringMethodTranslator`).
Fall back to `TypeMappingSource.FindMapping(body.Type)` when the translator left the
body's TypeMapping null (e.g. CASE expressions from `string.CompareTo`).

### Tier 3 — element access and ordering (next PR)

| LINQ shape | ClickHouse | Notes |
| ------------------------------------- | ------------------------------------------- | ----- |
| `arr[i]` / `arr.ElementAt(i)` | `arr[i + 1]` | 1-based ↔ 0-based offset |
| `arr.ElementAtOrDefault(i)` | `if(i < length(arr), arr[i + 1], …)` | Empty/OOB → default(T) |
| `arr.First()` | `arr[1]` | Throws via runtime if empty; EF semantics |
| `arr.FirstOrDefault()` | `if(notEmpty(arr), arr[1], default)` | Empty → default(T) |
| `arr.Last()` | `arr[-1]` | Negative indexing in ClickHouse |
| `arr.LastOrDefault()` | `if(notEmpty(arr), arr[-1], default)` | |
| `arr.Single()` / `SingleOrDefault()` | `arr[1]` + length guard | Or skip — rare on array columns |
| `arr.IndexOf(value)` | `indexOf(arr, value) - 1` | 1-based ↔ 0-based; returns 0 for missing in CH, −1 in .NET |
| `arr.Skip(n)` | `arraySlice(arr, n + 1)` | |
| `arr.Take(n)` | `arraySlice(arr, 1, n)` | |
| `arr.Skip(n).Take(m)` | `arraySlice(arr, n + 1, m)` | Combine into single call when both present |
| `arr.Reverse()` | `arrayReverse(arr)` | |
| `arr.OrderBy(x => x)` / `OrderByDescending` | `arraySort(arr)` / `arrayReverseSort(arr)` | Within array context only |
| `arr.OrderBy(x => f(x))` | `arraySort(x -> f(x), arr)` | Higher-order |
| `arr.Distinct()` | `arrayDistinct(arr)` | |

Each comes with empty-array semantics to think through. `arr[i]` on an empty array in
ClickHouse returns the type's default (no exception); .NET LINQ throws
`IndexOutOfRangeException`. Decide per-helper whether to add a runtime length guard or
document the semantic gap.

### Tier 4 — set-like operations (next PR or thereafter)

| LINQ shape | ClickHouse |
| ------------------------------------- | ------------------------------------------- |
| `a.Concat(b)` | `arrayConcat(a, b)` |
| `a.Union(b)` | `arrayDistinct(arrayConcat(a, b))` |
| `a.Intersect(b)` | `arrayIntersect(a, b)` |
| `a.Except(b)` | `arrayFilter(x -> !has(b, x), a)` |
| `a.SequenceEqual(b)` | `a = b` | ClickHouse compares arrays elementwise |
| `Enumerable.Repeat(value, n)` | `arrayWithConstant(n, value)` | When `n` is a SqlExpression |
| `Array.Empty()` / `new T[0]` | `emptyArrayT()` / `[]::Array(T)` | Only worth doing when a real query needs it |

### Tier 5 — aggregates over array elements (future)

| LINQ shape | ClickHouse |
| ------------------------------------- | ------------------------------------------- |
| `arr.Sum()` / `.Sum(x => f(x))` | `arraySum(arr)` / `arraySum(x -> f(x), arr)` |
| `arr.Min()` / `.Max()` | `arrayMin(arr)` / `arrayMax(arr)` |
| `arr.Average()` | `arrayAvg(arr)` |
| `arr.Aggregate(seed, fold)` | `arrayReduce('…', arr)` — not a clean fit; might skip |

### Tier 6 — Map / Tuple / other collection mappings (separate plans)

Out of scope for the array-helper plan but worth listing for completeness:

- `Map(K, V)` columns: `mapContains(m, k)`, `m[k]`, `mapKeys(m)`, `mapValues(m)`.
- `Tuple(T1, T2, …)`: element access via `tupleElement(t, n)` / projection through anonymous types.
- `Nested(...)`: rarely user-facing today; defer until concrete use cases.

---

## Edge cases and open questions

- **`Array(Nullable(T))` elements.** Materialization behavior needs pinning down before
any test really exercises this. `has(arr, NULL)` returns 0 — not NULL — even when `arr`
contains NULL elements; document the semantic gap from .NET LINQ. `arrayExists` over a
predicate body that returns NULL is treated as not-true.
- **`Nullable(Array(T))` columns.** Rare in practice; ClickHouse usually expresses
optionality at the element level instead. Decide whether to model these at all or
reject at model validation time.
- **Computed array expressions as sources.** Today the structural pre-filter rejects
shapes like `(condition ? a.Arr1 : a.Arr2).Contains(x)` — the source isn't a
`MemberExpression` or `EF.Property`. Loosening this is feasible but needs a more
careful gate to avoid the `EnumerableExpression` assertion on DbSet roots.
- **Subqueries returning arrays.** `(from … select e.Tags).First().Contains(x)` —
currently falls through to base. Could light up once Tier 3 ordering helpers exist,
but requires the gate to accept non-MemberExpression sources.
- **Empty-array LINQ semantics.** Several Tier 3 helpers (`First`, `ElementAt`, `Single`)
throw on empty in .NET but return the element-type default in ClickHouse. Decide
per-helper whether to add a length guard, document the semantic mismatch, or refuse to
translate when EF would observably see the difference.
- **Compiled-query cache.** Lambda SqlExpression `Quote()` uses cached reflection over
the public constructor. A single unit test that verifies `Quote()` returns a
`NewExpression` targeting the right constructor catches drift.
- **Marker-strip blast radius.** Stripping `AsQueryable()`/`AsEnumerable()` at the
`VisitMethodCall` entry has subtle side effects — the strip changes the apparent CLR
type of the expression and is unconditional on the visit-then-pattern-match path. Today
it's safe because the only call sites are guarded by `LooksLikeArrayColumnAccess`, but
if/when that gate is loosened, the strip needs revisiting too. Optional follow-up:
move the strip inside the helpers and reach it only after the gate.