Incorrect translation of date substraction.

[AndrewBoklashko]

var delays = await db
    .Set<Job>()
    .Select(j => (j.StartJob - j.CreateDate).TotalHours)
    .ToListAsync();

Gets translated into:

SELECT [j].[StartJob] - [j].[CreateDate]
FROM [Job] AS [j]
WHERE [j].[IsDeleted] = 0

Which in turn throws:

System.Data.SqlClient.SqlException (0x80131904): Operand data type datetime2 is invalid for subtract operator.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__108_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
ClientConnectionId:c7a3cc04-ddbf-4aab-9957-7477083c909f
Error Number:8117,State:1,Class:16

This query should either be translated into DateDiff if possible or perform subtraction on client but definitely not throw.

Further technical details

EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SQLServer
Operating system: Windows 10 1703
IDE: Visual Studio 2017

[ralmsdeveloper]

Hello @SSkovboiSS,
This feature is available in version 2.1, using EF.Functions, but if you only need the translation on the server @smitpatel wrote this here:
#10241

[AndrewBoklashko]

Thank you @ralmsdeveloper, I have already found a workaround. Nevertheless this is a bug and has to be fixed, that's why I created this issue.

[ralmsdeveloper]

All Right, but if you use TimeStamp, we'll be limited to Day, Hour, Minutes and Seconds.

So it was implemented in EF.Functions, I do not believe the team wants to do this, for version 2.0.1, since it is close to launching 2.1 with this solution.

[AndrewBoklashko]

@ralmsdeveloper the problem here is not about client or server evaluating, this query does not work at all, because it is getting translated into totally wrong SQL. And I am also ok with fix in 2.1.

[ralmsdeveloper]

Exactly because there is no implementation for this:
(j.StartJob - j.CreateDate)

[AndrewBoklashko]

Alright, but why current implementation tries to perform this subtraction on database? Shouldn't it just client eval instead?

[ajcvickers]

This may already be fixed in the 2.1 code. @SSkovboiSS Can you test with the nightly builds, as listed on the repo homepage: https://github.com/aspnet/EntityFrameworkCore

Assigning to @smitpatel, who will close if this is already fixed.

[AndrewBoklashko]

@ajcvickers sure, I will test it tomorrow and give a feedback here.

[AndrewBoklashko]

Just tested 2.1.0-preview1-28042 and it still fails.

Query log;

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.1.0-preview1-28042 initialized 'KidsCareAppDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (72ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [j].[StartJob] - [j].[CreateDate]
      FROM [Job] AS [j]
      WHERE [j].[IsDeleted] = 0
System.Data.SqlClient.SqlException (0x80131904): Operand data type datetime2 is invalid for subtract operator.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__120_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
ClientConnectionId:2a125bca-d16d-4144-852c-5b4742245d5f
Error Number:8117,State:1,Class:16
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred in the database while iterating the results of a query for context type 'KidsCareApp.Repository.Database.KidsCareAppDbContext'.
      System.Data.SqlClient.SqlException (0x80131904): Operand data type datetime2 is invalid for subtract operator.
         at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__120_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         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()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         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()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
      ClientConnectionId:2a125bca-d16d-4144-852c-5b4742245d5f
      Error Number:8117,State:1,Class:16
System.Data.SqlClient.SqlException (0x80131904): Operand data type datetime2 is invalid for subtract operator.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__120_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   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()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   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()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
ClientConnectionId:2a125bca-d16d-4144-852c-5b4742245d5f
Error Number:8117,State:1,Class:16

[ralmsdeveloper]

@ajcvickers,
I believe this subtraction is only available in EF.Functions.

Implemented in this commit

Hugs!

[smitpatel]

As per exception above & documentation of T-SQL (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/subtract-transact-sql), - is not supported on datetime related types.
Though when running query using current dev, it actually runs the query correctly (also runs in my SSMS), but during materialization it fails since subtraction gives datetime type back while client is expecting TimeSpan.

SQLite just take initial numeric part and do subtraction which is totally incorrect result.

PostGreSql actually translates subtraction and return timespan correctly. cc:@roji

Removing milestone as there is no obvious fix for this.

As a work-around, you can use DateDiff function on SqlServer to translate dates correctly or project out dates on client and do client side subtraction.

[ajcvickers]

@smitpatel to bring together all the various parts of the "datediff" work to a design meeting so we can discuss. This includes:

• Can we translate these operators using datediff in the translation?
• Can SQLite and other providers also translate the operators?
• If so, then do we need datediff functions at all, or maybe just parts of them?
• If we keep parts of the datediff functions, then should they be SQL Server specific? If not, is the naming right?

[roji]

@ajcvickers @smitpatel just a suggestion, but if SQL Server does have a function that does date subtraction and returns a timespan, maybe you should have the C# subtraction operator translated to that function, rather than exposing a special EF.Functions method? The operator is, after all, the natural C# way to do the operation, and it's not really important whether that translates to an operator in the database (like PostgreSQL) or a function call (hopefully possible in SQL Server)...

[smitpatel]

@roji - The issue is, there is no function which returns timespan. DateDiff function on sql server allows extract parts of timespan. Therefore we added DateDiff functions on EF.Functions (also the fact linq2sql had them). SqlServer has direct translation for them but for other providers there is no direct translation. Rather it makes it cumbersome trying to mimic client side behavior.

[roji]

@smitpatel ah I see - makes sense... In that case it seems right to have the DateDiff function as SQL Server-specific, I think.

[divega]

Another idea would be to recognize patterns like (d1 - d2).TotalHours and translate them to the corresponding DATEDIFF calls. Not all of them will work. FWIW, I just remembered we discussed about doing this in EF many years ago. Would it be hard with our current implementation?

I wonder also if we could establish a conversion between an int containing seconds or microseconds and a TimeSpan. Then when we need to return a TimeSpan (e.g. the projection contains d1 - d2) we use DATEDIFF and then apply the conversion, when we need to compare two TimeSpans (e.g. d1 - d2 < ts) we just compare the seconds, etc.

[ralmsdeveloper]

@divega It should also be remembered that we do not have TotalYears and TotalMonths.

So there would be a need to create an extension method, which I believe to be what you do not intend to do!

[divega]

@ralmsdeveloper agreed that there are things that would be missing with that approach. That is what I meant when I said "not all of them will work". That may be a reason the DateDiff extensions methods are still compelling even if only in some databases.

[smitpatel]

Design meeting notes

• SqlServer DateDiff functions calculates boundary crossed for particular modifier. Going from 31st Dec to 1st Jan, for year, it gives result of 1 because year boundary was crossed. Which is significantly different from (date2 - date1) computation. Because the timespan will not have 1 year. Hence we are going to move DateDiff* function extensions to SqlServer provider. Filed Query: Make EF.Functions.DateDiff SqlServer specific #11038

• Addition/subtraction of DateTime or a member access on TimeSpan afterwards can be translated to server in some cases, (e.g. the query like here). There is no easy way to do so for SqlServer. Filed SQL Server Query: DateTime addition/subtraction translation to server #11039

• This issue would be used to avoid server evaluation of DateTime related functions on SqlServer/Sqlite so that we do not generated incorrect SQL. It would be client evaluated since there is no suitable way to do that on server. Providers like npgsql which can translate above to server will continue to work as is.

[ErikEJ]

sql-ce fyi?