ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore
Translate parameterized collection Contains to a native ClickHouse array parameter
- 主要言語
- C#
- スター
- 23
- フォーク
- 7
- 平均マージ
- 14日 3時間
- マージ済み PR(30日)
- 1
説明
## 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.
コントリビューションガイド
このリポジトリのコントリビューションガイドは索引されていません
調査の方向性
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.
索引モデルが issue の本文から書いたものです。
評価
- 技術スタック
- clickhouse, csharp
- 領域
- database
- issue の種類
- 機能追加
- 難易度
- 5/5
- 見積もり時間
- 1週間以上
- 活発さ
- 静か
- 明瞭さ
- おおむね明確
- 初心者へのやさしさ
- 48/100