Query: Translate DateTimeOffset.Date

[sergeitemkin]

Steps to reproduce

    public class TestDbContext : DbContext
    {
        public class EntityA
        {
            public Guid Id { get; set; }
            public DateTimeOffset DateTimeOffsetProperty { get; set; }
            public DateTime DateTimeProperty { get; set; }
        }
        public DbSet<EntityA> TestEntities { get; set; }
        public TestDbContext(DbContextOptions options) : base(options) { }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var options = new DbContextOptionsBuilder()
                .UseSqlServer(@"data source=localhost;initial catalog=TestDateTimeOffset;Integrated Security=true;")
                .ConfigureWarnings(warnings =>
                {
                    warnings.Throw(RelationalEventId.QueryClientEvaluationWarning);
                })
                .Options;

            using (var context = new TestDbContext(options))
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.Add(new TestDbContext.EntityA
                {
                    DateTimeOffsetProperty = DateTimeOffset.Now,
                    DateTimeProperty = DateTime.Now
                });

                context.SaveChanges();

                var works = context.Set<TestDbContext.EntityA>()
                    .Where(s => s.DateTimeProperty.Date == DateTime.Today)
                    .First();

                // throws exception because of client side evaluation
                var doesntwork = context.Set<TestDbContext.EntityA>()
                    .Where(s => s.DateTimeOffsetProperty.Date == DateTime.Today)
                    .First();
            }
        }
    }

The issue

We have entities that use DateTimeOffset properties that we're trying to query based on just the date part for reporting reasons. We have client side evaluation turned of for performance reasons, so we're unable to query EF because of the evaluation warning below. DateTime properties seem to work fine.

Exception message: Warning as error exception for warning 'RelationalEventId.QueryClientEvaluationWarning': The LINQ expression '([s].DateTimeOffsetProperty.Date == __Today_0)' could not be translated and will be evaluated locally. To suppress this Exception use the DbContextOptionsBuilder.ConfigureWarnings API. ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Stack trace:
   at Microsoft.EntityFrameworkCore.Internal.InterceptingLogger`1.Log[TState](LogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)
   at Microsoft.EntityFrameworkCore.Infrastructure.SensitiveDataLogger`1.Microsoft.Extensions.Logging.ILogger.Log[TState](LogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)
   at Microsoft.EntityFrameworkCore.Storage.RelationalLoggerExtensions.LogWarning(ILogger logger, RelationalEventId eventId, Func`1 formatter)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.WarnClientEval(Object expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at ConsoleApplication9.Program.Main(String[] args) in c:\Projects\ConsoleApplication9\ConsoleApplication9\Program.cs:line 49
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Further technical details

EF Core version: 1.0.1
Operating system: Windows 10 Pro
Visual Studio version: VS 2015 Update 3 (14.0.25431.01)

[smitpatel]

For DateTime properties we have translator which converts DateTime.Date to SQL like this CONVERT(date, [o].[OrderDate]).

[sergeitemkin]

@smitpatel The translator you're referring to does solve this problem for DateTime.Date. However, we're representing our date time fields as DateTimeOffset, and DateTimeOffset.Date doesn't seem to have a translator.

[maumar]

Fixed in 8b8006e