通过EF LINQ比较UTCTime的DateTimeOffset？ [英] Comparison of DateTimeOffset in UTC via EF LINQ?

问题描述

我正在尝试进行以下调用：

I am trying to make the following call:

itemsCount = await _db.Disposals
                      .Include(d => d.ItemIds)
                      .Where(d => d.OrganizationId == SelectedOrganizationID &&
                                  d.CreateDateTime.UtcDateTime > greaterThanUtc &&
                                  d.CreateDateTime.UtcDateTime < lessThanUtc)
                      .SelectMany(d => d.ItemIds)
                      .CountAsync();

SQL CreateDateTime 字段是一个 datetimeoffset 在SQL中键入。这个问题我99％肯定是这样的：在SQL中不存在 CreateDateTime.UtcDateTime ，所以必须在后端完成，而不是在数据库中。我可以把所有的数据带到这样的后端，然后进行比较：

The SQL CreateDateTime field is a datetimeoffset type in SQL. The problem, I am 99% sure, is that an equivalent of this: CreateDateTime.UtcDateTime does not exist in SQL, so it has to be done on the backend, rather than in the db. I can bring all the data to the backend like this and then do a comparison:

itemsCount = _db.Disposals
                .Include(d => d.ItemIds)
                .AsEnumerable()

但这显然是一个糟糕的解决方案和混乱异步。我的这个选项是什么异步获取计数？

but this is obviously a poor solution and messes with async. What are my options here to get the count asynchronously?

推荐答案

如果要强制将数据库中出现的所有DateTime对象指定为UTC，则需要添加T4变换文件，并为所有DateTime和可空的DateTime对象添加额外的逻辑，以便初始化为DateTimeKind.Utc

If you want to force all DateTime objects coming out of the database to be specified as UTC you'll need to add a T4 transform file and add additional logic for all DateTime and nullable DateTime objects such that they get initialized as DateTimeKind.Utc

1）为.edmx模型创建.tt文件

1) Create the .tt file for your .edmx model

2）打开.tt文件，找到WritePrimitiveTypeProperty方法。

2) Open the .tt file and find the "WritePrimitiveTypeProperty" method.

3）设置器代码。这是ReportPropertyChanging和ReportPropertyChanged方法回调之间的一切。

3) Replace the existing setter code. This is everything between the ReportPropertyChanging and the ReportPropertyChanged method callbacks.

这是一步一步的说明：

http ：//www.aaroncoleman.net/post/2011/06/16/forcing-entity-framework-to-mark-datetime-fields-at-utc.aspx

这篇关于通过EF LINQ比较UTCTime的DateTimeOffset？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！