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

Research direction

Start in ClickHouseSqlTranslatingExpressionVisitor and ClickHouseArrayMethodTranslator, tracing how parameterized Contains differs from the existing column-based has() translation. Then inspect ClickHouseQuerySqlGenerator and ClickHouseArrayTypeMapping for array parameter rendering and element types. Done means supported collections emit one Array(T) parameter, empty collections preserve EF semantics, and the listed type, mode, large-collection, and execution tests pass.

Written by the indexing model from the issue text.

Assessment

Tech stack
clickhouse, csharp
Domain
database
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.