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