ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore

Translate parameterized collection Contains to a native ClickHouse array parameter

Open
#39 0 comments 0 reactions 0 assignees View on GitHub
enhancement
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.