EF Core 7实现UTC转本地时区查询（匹配SQL效果）

我能用SQL实现以下查询，返回预期结果：

SELECT *
FROM [T1].[dbo].[Orders]
WHERE CONVERT(DATE, OrderDate AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time') 
BETWEEN '2023-12-06' AND '2023-12-07'
ORDER BY OrderId DESC;

但我希望在不使用Enumerable或ToList的前提下，通过EF Core实现相同效果。

我尝试了以下代码：

query = query.Where(order =>
     EF.Functions.DateDiffDay(
         TimeZoneInfo.ConvertTimeFromUtc(order.OrderDate, TimeZoneInfo.Utc),
         TimeZoneInfo.ConvertTimeFromUtc(startDateTime, TimeZoneInfo.Local)
     ) >= 0 &&
     EF.Functions.DateDiffDay(
         TimeZoneInfo.ConvertTimeFromUtc(endDateTime, TimeZoneInfo.Local),
         TimeZoneInfo.ConvertTimeFromUtc(order.OrderDate, TimeZoneInfo.Utc)
     ) >= 0);

却出现如下错误，说明我对时区转换的理解有误：

System.InvalidOperationException: 尝试计算LINQ查询参数表达式时引发了异常。有关详细信息，请参阅内部异常。要显示其他信息，请调用'DbContextOptionsBuilder.EnableSensitiveDataLogging'。 ---> System.ArgumentException: 无法完成转换，因为提供的DateTime的Kind属性未正确设置。例如，当Kind属性为DateTimeKind.Local时，源时区必须是TimeZoneInfo.Local。(参数'sourceTimeZone')

更新
我尝试了两种已有解决方案，但结果与SQL查询不一致。原SQL查询结果正确，新方案却缺失部分数据，结果跨3天，详见附图：

解决方案 #

要在EF Core中完全匹配原SQL的逻辑，需利用EF Core对SQL时区转换语法的支持，直接在LINQ中映射AT TIME ZONE操作：

写法一：匹配原SQL日期截断逻辑 #

var targetTimeZone = "AUS Eastern Standard Time";
var startDate = new DateTime(2023, 12, 6);
var endDate = new DateTime(2023, 12, 7);

query = query.Where(order =>
    EF.Functions.DateFromParts(
        EF.Functions.DatePart("year", EF.Functions.ConvertTimeZone(order.OrderDate, "UTC", targetTimeZone)),
        EF.Functions.DatePart("month", EF.Functions.ConvertTimeZone(order.OrderDate, "UTC", targetTimeZone)),
        EF.Functions.DatePart("day", EF.Functions.ConvertTimeZone(order.OrderDate, "UTC", targetTimeZone))
    ).IsBetween(startDate, endDate)
).OrderByDescending(order => order.OrderId);

写法二：通过时间范围避免边界问题 #

var targetTimeZone = "AUS Eastern Standard Time";
// 包含2023-12-07当天的最后一刻，避免日期截断遗漏数据
var startDateTime = new DateTime(2023, 12, 6);
var endDateTime = new DateTime(2023, 12, 8).AddTicks(-1);

query = query.Where(order =>
    EF.Functions.ConvertTimeZone(order.OrderDate, "UTC", targetTimeZone) >= startDateTime &&
    EF.Functions.ConvertTimeZone(order.OrderDate, "UTC", targetTimeZone) <= endDateTime
).OrderByDescending(order => order.OrderId);

方案说明 #

1. 原SQL核心逻辑：将OrderDate（视为UTC时间）转换为澳大利亚东部标准时间，提取日期部分后判断范围。
2. 两种写法均在数据库端执行时区转换，避免客户端求值（不会触发隐式ToList）。
3. 写法一完全对应CONVERT(DATE, ...)的SQL行为；写法二通过覆盖结束日期的完整时间范围，解决边界数据遗漏问题。
4. 避免使用TimeZoneInfo客户端转换方法，这类方法无法被EF Core转换为SQL，会引发Kind属性错误及客户端求值问题。

内容的提问来源于stack exchange，提问作者traveler3468