ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore
Translate parameterized collection Contains to a native ClickHouse array parameter
- 主要语言
- C#
- 星标
- 23
- 派生
- 7
- 平均合并
- 14 天 3 小时
- 30 天内合并 PR
- 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.
贡献指南
这个仓库没有索引到贡献指南
调研方向
从 ClickHouseSqlTranslatingExpressionVisitor 和 ClickHouseArrayMethodTranslator 开始,追踪参数化 Contains 与现有的基于列的 has() 翻译有何不同。然后检查 ClickHouseQuerySqlGenerator 和 ClickHouseArrayTypeMapping,了解数组参数的呈现方式和元素类型。完成的标准是:受支持的集合生成一个 Array(T) 参数,空集合保留 EF 语义,并且列出的类型、模式、大集合和执行测试全部通过。
由索引模型根据 Issue 内容生成。
评估
- 技术栈
- clickhouse, csharp
- 领域
- database
- Issue 类型
- 功能
- 难度
- 5/5
- 预计耗时
- 一周以上
- 活跃度
- 冷清
- 描述清晰度
- 基本清楚
- 新手友好度
- 48/100