如何使用.NET Core WebAPI和OData v4过滤SQL Server DateTime？

Question

我正在运行一个简单的.NET Core WebApi应用程序，使用OData查询v4和SQL Server 2012。

尽管这个应用程序能够正常工作，但是它的速度非常缓慢：

GET /api-endpoint?$filter=date(MyDateTimeField) ge 2018-01-01&$top=100

以上URL生成的SQL查询语句：

SELECT TOP 100 * FROM MyTable WHERE ((((DATEPART(year, [MyDateTimeField]) * 10000) + (DATEPART(month, [MyDateTimeField]) * 100)) + DATEPART(day, [MyDateTimeField])) >= (((2018 * 10000) + (1 * 100)) + 1))

当我尝试执行以下操作时：

GET /api-endpoint?$filter=MyDateTimeField ge 2018-01-01T00:00:00.00Z&$top=100

它会生成以下SQL查询语句：

SELECT TOP 100 * FROM MyTable WHERE [MyDateTimeField] > '2018-01-01T00:00:00.0000000'

该操作返回以下错误：

转换字符日期和/或时间失败。

如果要生成类似于此的SQL查询，OData查询语法是什么？

SELECT TOP 100 * FROM MyTable WHERE [MyDateTimeField] > '2018-01-01'

- AndreFeijo

---

不确定我是否在问一个愚蠢的问题：为什么不使用 datetime2？如果您使用 datetime2，您的查询应该可以正常工作。 - itminus

嗨 @itminus，这是一个传统数据库，我没有更改它的权限。 - AndreFeijo

Answer 1

假设MyDateTimeField字段的类型是datetime而不是datatime2，首先使用列注释[Column(TypeName="datetime")]来修饰MyDateTimeField：

public class MyTable
{
    // ...  other props

    [Column(TypeName="datetime")]
    public DateTime MyDateTimeField {get;set;}
}

要使用 datetime 进行查询，需将其转换为 DateTimeOffset：

?$filter=MyDateTimeField ge cast(2018-01-01T00:00:00.00Z,Edm.DateTimeOffset)

生成的sql大致如下：

  SELECT ..., [$it].[MyDateTimeField], 
  FROM [MyTable] AS [$it]
  WHERE [$it].[MyDateTimeField] >= '2018-01-01T08:00:00.000'

请注意上面的datetime是2018-01-01T08:00:00.000而不是2018-01-01T00:00:00.0000000。

Demo的截图：

- itminus

---

谢谢 - 我简直不敢相信 ODataV4 居然像“啊，没人用它一样”放弃了 DateTime - 当然我们的数据库使用 DateTimeOffset 会更好，但是在 ODataV4 之前做出了一些决定，强制开发人员仅“将所有 datetime 列转换为 datetimeoffsets” 是很粗鲁的 - 它至少应该像 V3 中一样工作 - 无论如何，您可能已经救了我的命 :) - steve

Answer 2

在自己的挫败感中苦苦寻找解决方案后，我终于找到了一个解决方案，可以使我的api消费者不必将DateTime字符串强制转换为一个丑陋、冗长、令人不安的表达式。

我还希望我的模型透明地使用DateTimeOffset而不是DateTime，这将允许我在未来重构数据库并最终使用DateTimeOffset，即使到目前为止我不需要处理时区。

我的限制是我不能更新我的遗留数据库，所以这里是解决方案。 这个解决方案适合您吗？ 只有在以下情况下，此解决方案才有用：

• 您不能（或不想）更新您的db列类型（如果您可以，您应该解决问题）
• 您使用或可以更改实体以使用DateTimeOffset而不是DateTime（如果这是一个新的api，请考虑这样做以符合odata标准，并允许您在未来重构底层数据库）
• 您不需要处理不同的时区（这可以做到，但您需要努力解决方案）
• 您使用EF Core >= 2.1

解决方案 更新您的实体使用 DateTimeOffset。

public class MyEntity {
    [...]
    public DateTimeOffset CreatedDateTime { get; set; }
    public DateTimeOffset ModifiedDateTime { get; set; }
    [...]
}

2. 创建一个ValueConverter

public static class ValueConvertes
{
    public static ValueConverter<DateTimeOffset, DateTime> DateTimeToDateTimeOffset =
        new ValueConverter<DateTimeOffset, DateTime>(
            model => model.DateTime, 
            store => DateTime.SpecifyKind(store, DateTimeKind.UTC));
}

更新您的模型映射。

public void Configure(EntityTypeBuilder<QuestionQML> builder)            
{
    builder.ToTable("MyEntityTable");
    builder.Property(e => e.CreatedDateTime)
       .HasColumnName("CreatedDateTime") // On DB datetime Type
       .HasConversion(ValueConvertes.DateTimeToDateTimeOffset);
    builder.Property(e => e.ModifiedDateTime)
       .HasColumnName("ModifiedDateTime") // On DB datetime Type
       .HasConversion(ValueConvertes.DateTimeToDateTimeOffset);
    [...]          
}

这使您能够以以下方式进行筛选：

?$filter=CreatedDateTime gt 2010-01-25T02:13:40Z ?$filter=CreatedDateTime gt 2010-01-25T02:13:40.01234Z ?$filter=CreatedDateTime gt 2010-01-25

特别感谢chris-clark。

编辑： 当数据库中存储的日期时间为UTC时，可以使用正确的代码DateTimeKind.UTC。如果您在不同的时区存储，则需要将Kind设置为您使用的时区，但这会改变结果中显示日期时间的方式，例如，对于英国时区，显示为Z(GMT时间)或+01:00(BST时间)。

- Norcino