ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore

No LINQ translation for DateTime members and methods

オープン
#55 コメント 1 件 リアクション 1 件 担当者 0 名 GitHub で見る
主要言語
C#
スター
23
フォーク
7
平均マージ
14日 3時間
マージ済み PR(30日)
1

説明

## Problem

The provider translates no `DateTime` members and no `DateTime` methods. Only a direct comparison works.

EF Core's base `RelationalMemberTranslatorProvider` registers no built-in translators. It adds plugins only (`RelationalMemberTranslatorProvider.cs:22`). `ClickHouseMemberTranslatorProvider` adds `ClickHouseArrayMethodTranslator` and `ClickHouseStringMethodTranslator` only. No date/time translator exists.

## Measured behaviour

Tested against a real ClickHouse server, on a `DateTime` property with column type `DateTime64(6, 'UTC')`:

| Expression | Result |
| --- | --- |
| `.Year` `.Month` `.Day` `.Hour` `.Minute` | `InvalidOperationException: The LINQ expression ... could not be translated` |
| `.Date` `.DayOfWeek` `.DayOfYear` `.TimeOfDay` | same |
| `.AddDays(1)` `.AddMonths(1)` | same |
| `DateTime.UtcNow` / `DateTime.Now` / `DateTime.Today` | same |
| `dt - dt` (result is `TimeSpan`) | `InvalidCastException: Unable to cast object of type 'System.TimeSpan' to type 'System.DateTime'` |
| `ts > new DateTime(2020, 1, 1)` | works: ``WHERE `d`.`ts` > '2020-01-01 00:00:00.000000'`` |

The `dt - dt` result is different from the others. It gives an `InvalidCastException` instead of a clear "could not be translated" message. This looks like a defect, not only a missing feature.

## Suggested work

Add one shared date/time member translator. Give it the CLR type as a parameter so that it serves `DateTime`, `DateOnly`, and `DateTimeOffset` from the same class. Npgsql uses this shape (`NpgsqlDateTimeMemberTranslator`).

ClickHouse functions to map:

| Member | ClickHouse function |
| --- | --- |
| `.Year` | `toYear` |
| `.Month` | `toMonth` |
| `.Day` | `toDayOfMonth` |
| `.Hour` | `toHour` |
| `.Minute` | `toMinute` |
| `.Second` | `toSecond` |
| `.DayOfYear` | `toDayOfYear` |
| `.DayOfWeek` | `toDayOfWeek` (note: ClickHouse counts Monday as 1, .NET counts Sunday as 0) |
| `.Date` | `toStartOfDay` |
| `.AddDays` / `.AddMonths` / `.AddYears` | `date_add` |
| `DateTime.UtcNow` | `now64` |

Also translate the `DateTime` difference to `date_diff`, or give a clear "not supported" message.

Take care with two ClickHouse behaviours:

- `toDayOfWeek` starts at Monday = 1. .NET `DayOfWeek` starts at Sunday = 0. The translation must adjust the value, in the same way the string translators adjust the 0-based to 1-based index.
- These functions read the column timezone. For a `DateTimeOffset` property the result must stay consistent with the UTC instant that the column holds.

## Notes

Found while I investigated #53. `DateTimeOffset` needs the same translators, so one shared class is better than a `DateTimeOffset`-only class. #53 itself needs comparison support only, which already works.

コントリビューションガイド

このリポジトリのコントリビューションガイドは索引されていません

調査の方向性

Start with ClickHouseMemberTranslatorProvider and RelationalMemberTranslatorProvider.cs:22, then compare NpgsqlDateTimeMemberTranslator with the existing ClickHouseArrayMethodTranslator and ClickHouseStringMethodTranslator. Verify the listed DateTime mappings against ClickHouse behavior, including DayOfWeek numbering and DateTimeOffset timezone consistency. Done also requires DateTime differences to translate or fail with a clear unsupported message.

索引モデルが issue の本文から書いたものです。

評価

技術スタック
clickhouse, csharp
領域
backend, databases
issue の種類
機能追加
難易度
5/5
見積もり時間
1週間以上
活発さ
静か
明瞭さ
おおむね明確
初心者へのやさしさ
48/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。