ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore
Translate parameterized collection Contains to a native ClickHouse array parameter
- Dominant language
- C#
- Stars
- 23
- Forks
- 7
- Avg merge
- 14d 3h
- Merged PRs (30d)
- 1
Description
## Summary
Translate `Contains` over a **parameterized** collection (a captured local `int[]`/`List`/etc.) into a single native ClickHouse **array parameter** — `column IN {ids:Array(T)}` (or `has({ids:Array(T)}, column)`) — instead of expanding it to one bound parameter per element.
This removes the parameter-count ceiling users hit on large `WHERE ... IN (...)` queries (see #38) and keeps query text small.
## Background / current behavior
For a captured collection, EF Core's relational pipeline currently expands the query to one parameter per element:
```sql
WHERE `p`.`Id` IN ({ids1:Int32}, {ids2:Int32}, … , {idsN:Int32})
```
Investigation findings (verified against real ClickHouse via Testcontainers, capturing the executed command with a `DbCommandInterceptor`):
- **`ParameterTranslationMode` is currently a no-op in this provider.** `MultipleParameters`, `Constant`, and `Parameter` all produce the identical `IN (p1 … pN)` SQL with N real driver parameters (confirmed at 5 and 12,000 ids). A captured collection is not a ClickHouse `Array` column, so the existing `has(...)` translation in `ClickHouseArrayMethodTranslator` does not fire, and EF falls back to its legacy multiple-parameter expansion, where the mode has no effect.
- The provider's `ValuesExpression` / `SELECT … UNION ALL` rewrite only covers **inline** literal collections, not parameterized ones.
- A large list therefore sends N parameters; on some servers/proxies this hits a limit around ~10k (the OSS container in testing accepted 12k, so the exact ceiling is environment-specific — `max_query_size`, server profile, or a proxy).
## Why the array approach is the right fix
Verified working end-to-end via the raw driver — a single array parameter executes correctly:
```sql
SELECT count() FROM param_rows WHERE Id IN {ids:Array(Int32)}
-- p.Value = int[12000] → 1 parameter, 12000 rows matched
```
ClickHouse.Driver's parameter formatter serializes arrays natively, so this needs no driver changes. Benefits:
- **No parameter-count ceiling** — one parameter regardless of collection size.
- **Small query text** — avoids `max_query_size` pressure that both `MultipleParameters` and `Constant` (inline) can hit.
- ClickHouse is OLAP and does not reuse query plans by parameterization, so there is no downside to a single bound array vs. inlined constants.
## Proposed implementation
Scope is isolated to the query pipeline:
1. Detect `Contains` where the source is a **collection parameter** (not a column, not an inline collection) in `ClickHouseSqlTranslatingExpressionVisitor` / `ClickHouseArrayMethodTranslator`.
2. Emit an `Array(T)`-typed `SqlParameterExpression` and render `column IN {p:Array(T)}` (or `has({p:Array(T)}, column)`) in `ClickHouseQuerySqlGenerator`.
3. Serialize the array parameter with the element's store type (the existing `ClickHouseArrayTypeMapping.ElementMapping` alignment used by the column-based `has()` path covers the element-type concern).
4. Ideally route EF Core's `ParameterTranslationMode.Parameter` through this path so the standard knob works as documented.
## Tests
- Multiple element types (Int32/Int64/String/Guid/Enum/etc.).
- Empty-collection semantics (must match `WHERE 1=0` / no rows, consistent with EF).
- Large collection (e.g. 50k) sends a single parameter and executes.
- `MultipleParameters` and `Constant` modes still behave correctly (and document what each emits).
## Notes
- This is the EF-idiomatic answer to large `Contains` and supersedes the `UseParameterizedCollectionMode(Constant)` workaround discussed in #38.
- Related: #38.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.