代码之家 › 专栏 › 技术社区 › RotundChinchilla

EF6 LINQ查询与给定本地时间戳在同一天的UTC数据库时间戳?

linq-to-entities entity-framework-6 linq

RotundChinchilla · 技术社区 · 1 年前

我正在尝试编写一个EF6 LINQ查询,该查询接受DateTimeOffset并检索具有与给定本地日期相同的时间戳的所有数据库条目。数据库将所有时间戳都设置为UTC,但DateTimeOffset参数包含UTC的本地偏移,作为“同一天”标识的基础。

例如,假设数据库有两个条目,其时间戳分别为“2024-09-01下午4点UTC+0”和“2024-09-02凌晨5点UTC+0”。

如果我传递了一个名为“2024-09-01上午11点UTC-7”的DateTimeOffset,我希望这两个条目都匹配:“2024年9月1日下午4点UTC+0”转换为“20240年9月01日上午9点UTC-4”,“2024月9日凌晨5点UTC+0”转换成“2024至09月1日晚上10点UTC-6”,并且这两个结果的日期都与参数的日期2024年09月1号相匹配。

如果我传入的DateTimeOffset为“2024-08-31 9PM UTC-7”,则不应有匹配项,因为将数据库时间戳转换为UTC-7会产生落在“2024-09-1”而不是“2024-08-31”的日期。请注意,这里的给定时间戳转换为“2024-09-01 4AM UTC+0”,但没有匹配项,因为本地偏移量是基础,而不是UTC。

最后,给出“2024-09-02 3AM UTC+1”的DateTimeOffset应该只匹配时间戳为“2024年9月2日5AM UTC+0”的数据库条目,因为该时间戳转换为具有匹配的2024年09月2日6AM UTC+2日期的“2024月9日-0日下午4点”的条目转换为“20240年9月1日5PM UTC+1日”,但不匹配。

如上所述,编写一个C#函数来查找给定DateTimeOffset和时间戳列表的匹配项似乎很简单。但是,如果可能的话,我想在数据库上进行所有处理,以利用索引并尽量减少检索到的行数(即只获取通过检查的条目)。

因此,我无法使用以下内容:

return context.Table.Where(row => row.Timestamp.ToOffset(givenDTO.Offset).Date == givenDTO.Date).ToList();

因为 ToOffset() 和 Date LINQ to Entities不支持。

我试着使用系统。数据。实体。DbFunctions,但我找不到很多使用示例,所以我的结果都是错误的或抛出异常。例如:

return context.Table.Where(row => DbFunctions.DiffDays(row.Timestamp, givenDTO) <= 1).ToList()

第二种情况“2024-08-31 9PM UTC-7”失败。

尝试通过以下方式为每个条目创建新的DateTimeOffset DbFunctions.CreateDateTimeOffset() 要与给定的DateTimeOffset进行比较,请传递 givenDTO.Offset.Hours 对于 int? timeZoneOffset 如果给定的DateTimeOffset的偏移量为负,则参数会产生OverflowException。

我不知道如何写这个查询。这有可能吗?

1 回复 | 直到 1 年前

T N 1 年前

而不是尝试转换数据库 Timestamp 将值转换为本地时间进行比较,我相信您可以更容易地将给定的本地参考时间转换为UTC日期/时间范围,然后将其用作查询中的筛选器。

类似于:

DateTime fromUtc = givenDTO.Date - givenDTO.Offset; // Not sure of the exact expression
DateTime toUtc = fromUtc.AddDays(1);

return context.Table
    .Where(row => row.Timestamp >= fromUtc && row.Timestamp < toUtc)
    .ToList();

这种方法的一大优点是,生成的查询是 sargable ,表示索引 时间戳 列可用于有效地选择所需的行。

使用您的示例:给定datetimeoffset为 2024-09-01 11:00 UTC-7 ,我们将截断日期并调整为UTC产量 fromUtc = 2024-09-01 07:00 然后增加24小时 toUtc = 2024-09-02 07:00 。然后,我们查询具有以下内容的行 Timestamp >= '2024-09-01 07:00' && Timestamp < '2024-09-02 07:00' 。这将选择两个具有时间戳值的示例行 2024-09-01 16:00 和 2024-09-02 05:00 .

请注意 toUtc 值是独家匹配值为不包括在结果中。