asp.net-core - 如何使用带有 OData v4 的 .NET Core WebAPI 过滤 SQL Server DateTime

Question

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

这有效，但速度非常慢：

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

上面的 URL 生成的 SQL Query：

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'

标签： asp.net-coreodataasp.net-core-webapiodata-v4

Answer 1

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

public class MyTable
{
    // ...  other props

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

用 查询datetime，cast它变成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截图：