How do I get EntityFrameworkCore generated SQL to use the right format for DateTime objects?

Question

I'm using Microsoft.EntityFrameworkCore.SqlServer v2.1.2 (and have also tried v2.2.0-preview1-35029) and am using LINQ to fetch a collection of entities from an Azure SqlServer database, filtering on a DateTime field.

However, the SQL generated by the LINQ statement uses a string-based DateTime value that SqlServer rejects with this error:

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

I can modify the SQL statement to change the datetime format so that the query works without error (see below for details), but I don't know how to get the framework to generate that same datetime format.

While EntityFrameworkCore is still kinda new, it seems like this is a pretty straightforward use case, so I'm assuming I'm doing something wrong and that it's not a framework issue.

How do I prevent EF from generating an invalid datetime value in the SQL?

and/or

How do I get the generated SQL to use another format for DateTime objects?

---

The EntityFramework model I'm using looks like this:

public class DeskReading
{
    public int DeskReadingId { get; set; }
    //... some other fields ...
    public DateTime Timestamp { get; set; }
}

And my LINQ to query the values looks like this:

IQueryable<DeskReading> readings = 
    _dbContext.DeskReadings
            .OrderBy(gr => gr.Timestamp)
            .Where(gr => gr.Timestamp > new DateTime(2017, 05, 01));

readings.ToList();

And the SQL that's generated by that looks like this:

SELECT [gr].[DeskReadingId] --...some other fields ...
FROM [DeskReadings] AS [gr]
WHERE [gr].[Timestamp] > '2017-05-01T00:00:00.0000000'
ORDER BY [gr].[Timestamp]

Note that the value for the filter is '2017-05-01T00:00:00.0000000'

If I run that SQL directly on the SqlServer via SSMS, I get the same error:

But if I change the filter to use '2017-05-01 00:00:00', it works fine:

SELECT [gr].[DeskReadingId] --...some other fields ...
FROM [DeskReadings] AS [gr]
WHERE [gr].[Timestamp] > '2017-05-01 00:00:00'
ORDER BY [gr].[Timestamp]

As requested, here's the create script for the table:

CREATE TABLE [dbo].[DeskReadings](
    [DeskReadingId] [int] IDENTITY(1,1) NOT NULL,
    [SoilMoistureSensor1] [int] NOT NULL,
    [SoilMoistureSensor2] [int] NOT NULL,
    [LightLevel] [int] NOT NULL,
    [TemperatureF] [real] NOT NULL,
    [HumidityPercent] [real] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.DeskReadings] PRIMARY KEY CLUSTERED 
(
    [DeskReadingId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

In looking at the source code on GitHub, there is a conditional formatting that EntityFrameworkCore uses based on the StoreType it believes the column in the expression to be. For example, the format you're seeing is clearly for datetime2. The error that you're experiencing can occur when comparing a datetime column to a datetime2 formatted string.

Here is the source I'm referring to, there are three string consts that represent the format for the C# DateTime value:

    private const string DateFormatConst = "{0:yyyy-MM-dd}";
    private const string DateTimeFormatConst = "{0:yyyy-MM-ddTHH:mm:ss.fffK}";
    private const string DateTime2FormatConst = "{0:yyyy-MM-ddTHH:mm:ss.fffffffK}";

Formats https://github.com/aspnet/EntityFrameworkCore/blob/release/2.2/src/EFCore.SqlServer/Storage/Internal/SqlServerDateTimeTypeMapping.cs#L18-L20

Conditional Logic https://github.com/aspnet/EntityFrameworkCore/blob/release/2.2/src/EFCore.SqlServer/Storage/Internal/SqlServerDateTimeTypeMapping.cs#L70-L74

To fix this specific issue, you can attribute your model as such:

public class DeskReading
{
    public int DeskReadingId { get; set; }

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

This will force comparisons to treat it as a StoreType of datetime and correctly format it.