Inconsistent Date Handling with Dynamic Expressions

[avinash-phaniraj-readify]

Using the SQL Provider, passing in a DateTime set to DateTimeKind.Local into a dynamic expression throws exception:

System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

Exception message: System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

Stack trace:
System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Conversion failed when converting date and/or time from character string.
  Source=.Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at TestHost.Program.Main(String[] args) in C:\EFCore\EFCoreTestBed\Program.cs:line 35

Steps to reproduce

Our SQL database uses datetime columns and we have therefore set the Column Attribute's TypeName property to "datetime".

A query such as the one below works as expected. The timezone/offset info appears to be ignored.

private static DateTime _theLocalDate = DateTime.SpecifyKind(new DateTime(2010, 10, 10), DateTimeKind.Local);

var results = context.Set<Employee>()
              .Where(w => w.Created > _theLocalDate)
              .ToList();

When dynamic expressions are employed, this falls over .

      //System.Data.SqlClient.SqlException: 
     //'Conversion failed when converting date and/or time from character string.'
      var results = context.Set<Employee>()
               .Where(GetFilterWithLocalDateTime<Employee>(_theLocalDate))
               .ToList();

        private static Expression<Func<T, bool>> GetFilterWithLocalDateTime<T>(DateTime value)
        {
            var itemParam = Expression.Parameter(typeof(T), "i");

            var filter = Expression.MakeBinary(
                    ExpressionType.GreaterThan,
                    Expression.PropertyOrField(itemParam, "Created"),
                    Expression.Constant(value, typeof(DateTime)));

            return Expression.Lambda<Func<T, bool>>(filter, new[] { itemParam });
        }

Complete repro here --> https://github.com/avinash-phaniraj-readify/EFCoreTestBed/tree/InconsistentDateTimeHandling.

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 2.1.4
Operating system: Win 10
IDE: (e.g. Visual Studio 2017 15.8.6)

[smitpatel]

Duplicate of #12211

[smitpatel]

@avinash-phaniraj-readify - Can you try using 2.2.0-preview3 from NuGet and see if you still see error?

[avinash-phaniraj-readify]

@smitpatel
I have tested this on 2.2.0-preview3-35497 and can confirm that it works.