How to filter SQL Server DateTime using .NET Core WebAPI with OData v4

Question

I am running a simple .NET Core WebApi application with OData Query v4 and SQL Server 2012.

This works, but it's extremely slow:

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

SQL Query generated by the URL above:

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

When I try to do this:

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

It generates the following SQL query:

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

Which returns this error:

Conversion failed when converting date and/or time from character string.

What would the OData Query syntax be to generate a SQL query similiar to this?

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

Answer 1

Assuming that the field of MyDateTimeField is datetime instead of datatime2, decorate the MyDateTimeField with a column annotation [Column(TypeName="datetime")] firstly :

public class MyTable
{
    // ...  other props

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

To query with datetime, cast it into DateTimeOffset:

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

The generated sql is something like :

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

Note the datetime above is 2018-01-01T08:00:00.000 instead of 2018-01-01T00:00:00.0000000.

A screenshot of Demo:

Answer 2

After drowning incredulous in my own frustration I finally found a solution which would not force my api consumers to cast the DateTime string in an, ugly, verbose, disturbing expression. I also want my model to transparently look like using DateTimeOffset instead of DateTime, this will allow me in the future to refactor the database and finally use DateTimeOffset even if so far I don't need to handle time zones. My limitation is that I cannot update my legacy database and so here is the solution.

Is this solution for you?

This solution is useful only if:

• You cannot (or want to) update your db column type (if you can you should and solve the problem)
• You use or can change you entities to use DateTimeOffset instead of DateTime (If it is a new api consider doing this to comply with odata standard and allowing you to refactor in the future the underlay database)
• You don't need to handle different time zones (this can be done but you need to work on the solution to do it)
• You use EF Core >= 2.1

Solution

1. Update your entity using DateTimeOffset

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

2. Create a ValueConverter

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

3. Update your model mapping

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);
    [...]          
}

This allow you to filter in the following ways:

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

Special thanks to chris-clark

EDIT: Corrected code DateTimeKind.UTC can be used when the datetime stored in the database is in UTC, if you store in a different timezone you need to set the Kind to the timezone you use, but this changes the way your datetimes will be shown in the results, showing for UK Timezone, for example, Z(GMT time) or +01:00 (BST time.