Efcore: Using Contains: Conversion failed when converting date and/or time from literal

[andresdsep]

Describe what is not working as expected.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: Conversion failed when converting date and/or time from character string.
Stack trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.<>c__DisplayClass189_0.<ReadAsync>b__0(Task t)
   at System.Data.SqlClient.SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDispose)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__12.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__11.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

Steps to reproduce

Using conventions based mapping with EF Core, I've found that querying the database thru equality using a C# date with a database datetime directly would work:
```c#
dbContext.Entities.Where(x => x.Created == DateTime.Now)

Using Contains would not:
```c#
dbContext.Entities.Where(x => datesArray.Contains(x.Created))

The first expression produces something like:

exec sp_executesql N'SELECT [x].[AllTheColumnsHere] FROM [table] AS [x] WHERE ([x].[RollDate] = @__today)',N'@__rollDate_Date_1 datetime2(7)',@__today='2018-09-13 00:00:00'

whereas the second produces:

SELECT [x].[AllTheColumnsHere] FROM [table] AS [x] WHERE [x].[RollDate] IN ('2018-09-13T00:00:00.0000000')

Further technical details

EF Core version: 2.2.0 (stable)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10 x64
IDE: Visual Studio 2017 15.8.7

[glienard]

Same problem here.
A date mapped to datetime2(2) will result in:
correct insert,
wrong in where: datetime2(2) => '2018-12-27T14:11:34'
wrong in contains => '2018-12-27T14:11:34.8666032+01:00'

[Josh-TX]

I had this problem using query = query.Where(z => z.PurchaseDate > new DateTime(2018, 12, 31)), since the generated SQL had hardcoded the WHERE clause with a date string '2018-12-31T00:00:00.0000000'.

I fixed it using var date = new DateTime(2018, 12, 31); query = query.Where(z => z.PurchaseDate > date). This caused the generated SQL to add a parameter for the date instead of hardcoding it, and this parameter had a date string of the correct format`.

[zulander1]

I am having the same issue when executing

context.Database.ExecuteSqlCommand($"UPDATE TransactionLines SET InvoiceDate= CAST('{DateTime.Now}' AS DATETIME) WHERE id={item.Id}");
Should be: UPDATE TransactionLines SET InvoiceDate= CAST('2/14/2019 1:25:44 PM' AS DATETIME) WHERE id=123

[nmiller-svp]

Having the same issue when comparing dates, because the only resolution is changing all the DB tables datetime to datetime2 we couldn't use the framework.

[andez2000]

I ran into the same issue. My issue was using datetime column type in my SQL table.

I guess the default for EF Core is datetime2(7) and it is also the recommendation by Microsoft for SQL datetime types.

But you can specify the column type using:

modelBuilder.Entity<TestEntity>().Property(x => x.LastModified).HasColumnType("datetime");

Thus:

public class ExampleDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestEntity>()
            .ToTable("TestEntity")
            .HasKey(x => x.Id);

        modelBuilder.Entity<TestEntity>().Property(x => x.LastModified).HasColumnType("datetime");
        base.OnModelCreating(modelBuilder);
    }
}

You should be able to specify if using datetime2 the actual precision here for example datetime2(5).

[HaoK]

@smitpatel I'm not sure exactly what to try reproduce here, I asked Arthur and he wasn't sure either and to ask you :)

[smitpatel]

Mainly contains with type inference is issue.

Create a model which has DateTime property mapped to something other than datetime2(7) in SqlServer.
Test out
dbContext.Entities.Where(x => x.Created == DateTime.Now)
dbContext.Entities.Where(x => x.Created == new DateTime(..))
dbContext.Entities.Where(x => x.Created == p) where p = new DateTime(...)
dbContext.Entities.Where(x => datesArray.Contains(x.Created)) where datesArray is a client side list of DateTime.
The generated constants in SQL should have same typeMapping as column.