Using + or - operators between DateTimeOffset.Now and a TimeSpan in a query fail

[oleneveu]

Using + or - operators between DateTimeOffset.Now and a TimeSpan in a query fail

These 3 lines of code should be equivalent:
Flag = (field.Date + timeSpan) > DateTimeOffset.Now
Flag = field.Date > DateTimeOffset.Now.Subtract(timeSpan)
Flag = field.Date > DateTimeOffset.Now - timeSpan
However the last one fails when the query is executed

Here is a full query that triggers the issue:

var failureQuery =
    from t in context.TestEntities
    select new
    {
        t.Id,
        Flag = t.Date > DateTimeOffset.Now - timeSpan // Cause of the failure (same behavior when using 'DateTime.Now')
    };
failureQuery.ToArray();  // System.InvalidCastException: 'Failed to convert parameter value from a TimeSpan to a DateTimeOffset.'

This SQL is generated:

SELECT [t].[Id], CASE
    WHEN [t].[Date] > (SYSDATETIMEOFFSET() - @__timeSpan_0) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Flag]
FROM [TestEntities] AS [t]

When using the 'Substract' method, it seems that the computation is done on the C# side.

Here is a complete project that reproduces the issue :
EFCoreBug.zip

Stack trace

System.InvalidCastException
  HResult=0x80004002
  Message=Failed to convert parameter value from a TimeSpan to a DateTimeOffset.
  Source=Microsoft.Data.SqlClient
  StackTrace:
   at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   at Microsoft.Data.SqlClient.SqlParameter.GetCoercedValue()
   at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue)
   at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at EFCoreBug.Program.ReproduceBug.DoSomething() in C:\Dev\Local\EFCoreBug\Program.cs:line 57
   at EFCoreBug.Program.Main(String[] args) in C:\Dev\Local\EFCoreBug\Program.cs:line 65

  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
InvalidCastException: Object must implement IConvertible.

Provider and version information

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.0.2

[ajcvickers]

@oleneveu What SQL should this translate to?

[oleneveu]

@ajcvickers My original message was not clear enough, sorry about that.

To be more specific, I will compare two of the samples that I provided in my original message:

Flag = field.Date + timeSpan > DateTimeOffset.Now
Flag = field.Date > DateTimeOffset.Now - timeSpan

I think that there are two issues:

• The behavior is inconsistent: Both cases seem very similar to me but behaves differently.
• An invalid query is issued

var successQuery =
    from t in context.TestEntities
    select new
    {
        t.Id,
        Flag = t.Date + timeSpan > DateTimeOffset.Now
    };

translates to:

SELECT [t].[Id], [t].[Date], SYSDATETIMEOFFSET()
FROM [TestEntities] AS [t]

I suppose that the date calculation is made on the C# side. In any case, the result is correct.

But

var failureQuery =
    from t in context.TestEntities
    select new
    {
        t.Id,
        Flag = t.Date > DateTimeOffset.Now - timeSpan
    };

translates to:

SELECT [t].[Id], CASE
    WHEN [t].[Date] > (SYSDATETIMEOFFSET() - @__timeSpan_0) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Flag]
FROM [TestEntities] AS [t]

and fails.

Just a guess: @__timeSpan_0 seem to be declared as a DateTimeOffset. I believe the exception is thrown before the query is executed, when trying to send the parameters (System.InvalidCastException: Failed to convert parameter value from a TimeSpan to a DateTimeOffset.).

As an additional information, the third case from my original message:

Flag = field.Date > DateTimeOffset.Now.Subtract(timeSpan)

behaves like

Flag = field.Date + timeSpan > DateTimeOffset.Now

and translates to the exact same SQL:

SELECT [t].[Id], [t].[Date], SYSDATETIMEOFFSET()
FROM [TestEntities] AS [t]

To go further, when the comparison is made from within the 'where' clause, the execution always ends up with an exception. But once again the behavior is not consistent as the exception is not always the same.

Anyway, I understand that my approach was not appropriate as there is not equivalent for TimeSpan on SQL Server.

[smitpatel]

Root cause here, there is no type mapping assigned to both side so it escapes our check for throwing exception.