Как фильтровать SQL Server DateTime с помощью .NET Core WebAPI с OData v4

Я запускаю простое приложение .NET Core WebApi с OData Query v4 и SQL Server 2012.

Это работает, но очень медленно:

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

SQL-запрос, сгенерированный указанным выше URL-адресом:

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'

Который возвращает эту ошибку:

Ошибка преобразования при преобразовании даты и/или времени из строки символов.

Каким должен быть синтаксис запроса OData для создания запроса SQL, подобного этому?

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

asp.net-core asp.net-core-webapi odata odata-v4
person AndreFeijo    schedule 10.05.2019    source источник
comment
Не уверен, что задаю глупый вопрос: почему бы не использовать datetime2 ? Если вы используете datetime2, ваш запрос должен работать нормально.   -  person itminus    schedule 10.05.2019
comment
Привет @itminus, это устаревшая база данных, и у меня нет разрешения на ее изменение.   -  person AndreFeijo    schedule 10.05.2019

Ответы (2)


arrow_upward
2
arrow_downward

Предполагая, что поле 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.

Скриншот демо:

введите здесь описание изображения

person itminus    schedule 13.05.2019
comment
спасибо - я не могу поверить, что ODatav4 просто отбросил DateTime, как будто никто его не использовал - конечно, нашим базам данных было бы лучше использовать DateTimeOffset, но некоторые решения были приняты до ODataV4, и заставлять разработчиков просто преобразовывать все столбцы даты и времени в datetimeoffsets просто грубо - по крайней мере, он должен работать так же, как в V3 - в любом случае, вы, возможно, спасли мой бекон :) - person steve; 13.08.2019

arrow_upward
0
arrow_downward

После того, как я недоверчиво утонул в собственном разочаровании, я наконец нашел решение, которое не заставляло бы моих потребителей API приводить строку DateTime к уродливому, многословному, тревожному выражению. Я также хочу, чтобы моя модель прозрачно выглядела как использующая DateTimeOffset вместо DateTime, это позволит мне в будущем провести рефакторинг базы данных и, наконец, использовать DateTimeOffset, даже если я пока не не нужно обрабатывать часовые пояса. Мое ограничение заключается в том, что я не могу обновить свою устаревшую базу данных, и вот решение.

Подходит ли вам это решение?

Это решение полезно, только если:

  • Вы не можете (или хотите) обновить тип столбца db (если вы можете, вы должны решить проблему)
  • Вы используете или можете изменить свои объекты для использования DateTimeOffset вместо DateTime (если это новый API, рассмотрите возможность сделать это, чтобы соответствовать стандарту odata и позволить вам провести рефакторинг в будущем База данных подложки)
  • Вам не нужно обрабатывать разные часовые пояса (это можно сделать, но вам нужно поработать над решением, чтобы сделать это)
  • Вы используете EF Core >= 2.1

Решение

  1. Обновите свою сущность, используя DateTimeOffset.
public class MyEntity {
    [...]
    public DateTimeOffset CreatedDateTime { get; set; }
    public DateTimeOffset ModifiedDateTime { get; set; }
    [...]
}
  1. Создайте ValueConverter< /а>
public static class ValueConvertes
{
    public static ValueConverter<DateTimeOffset, DateTime> DateTimeToDateTimeOffset =
        new ValueConverter<DateTimeOffset, DateTime>(
            model => model.DateTime, 
            store => DateTime.SpecifyKind(store, DateTimeKind.UTC));
}
  1. Обновите сопоставление модели
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

РЕДАКТИРОВАТЬ: исправленный код DateTimeKind.UTC можно использовать, когда дата и время, хранящиеся в базе данных, находятся в формате UTC, если вы храните в другом часовом поясе, вам нужно установить вид в часовой пояс, который вы используете, но это меняет способ отображения даты и времени в результатах, отображая для часового пояса Великобритании, например, Z (время по Гринвичу) или +01:00 (время по BST.

person Norcino    schedule 16.10.2019