EF Core 3.0 DateTimeOffset.Date comparison inconsistent between client and server side evaluation.

[JesseLambert]

When comparing against the DateTimeOffset.Date property, we're seeing inconsistent results on client side vs server side evaluation.

Steps to reproduce

Given the entity:

    public partial class PreHire
    {
        public int PreHireId { get; set; }
        public DateTimeOffset StartDate { get; set; }
    }

And the following logic:

        DateTime startDate = DateTime.Now;

	//Pulls preHire with a start date of '2019-11-25 14:42:54.0833333 +00:00'
	PreHire clientComparePreHire = context.PreHire.Where(x => x.PreHireId == 22407).FirstOrDefault();
	if(clientComparePreHire.StartDate.Date >= startDate.Date)
	{
		Console.WriteLine("This works.");
	}

	List<PreHire> serverComparePreHires = context.PreHire.Where(q => q.StartDate.Date >= startDate.Date).ToList();
         if(serverComparePreHires.Count == 0)
         {
                 Console.WriteLine("This doesn't. This shouldn't be empty.");
         }

We get the following logs:

info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [p].[PreHireID], [p].[StartDate]
      FROM [TBL].[PreHire] AS [p]
      WHERE [p].[PreHireID] = 22407
This works.
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__startDate_Date_0='2019-11-25T00:00:00' (DbType = DateTimeOffset)], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PreHireID], [p].[StartDate]
      FROM [TBL].[PreHire] AS [p]
      WHERE CONVERT(date, [p].[StartDate]) >= @__startDate_Date_0
This doesn't. This shouldn't be empty.

This shows that serverComparePreHires is empty, but if I run the above query manually I get my expected result:

	SELECT [p].[PreHireID], [p].[StartDate]
	FROM [TBL].[PreHire] AS [p]
	WHERE CONVERT(date, [p].[StartDate]) >= '2019-11-25T00:00:00'

PreHireID	StartDate
22407	2019-11-25 14:42:54.0833333 +00:00

To summarize, When processing this specific linq query server side, EF Core generates a valid SQL Query but fails to return the matching PreHire entry.

Further technical details

EF Core version: 3.0.0 (Same result with 3.0.1)
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0 (Migrating from 2.1)
Operating system: Windows 10
IDE: Visual Studio 2019 16.3.7

[CodeSwimBikeRunner]

We also ran into this issue, in migrating. Removing .Date worked for us. It seems in your case this is a business rule to get the Current Date the Employee or Prehire Started? You would expect .Date to evaluate correctly. That's interesting that EFCore generated a query, that works when you execute it. I suspect you didn't see this before, because client side evaluation is turned off now.

If this isn't a bug, its some weird obscure thing EF does for you. Waiting for the EFCore team to respond :)

[ajcvickers]

@JesseLambert I am not able to reproduce this--see my code below. The second query correctly returns a single entity that matches the criteria. Please post a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

public partial class PreHire
{
    public int PreHireId { get; set; }
    public DateTimeOffset StartDate { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger 
        = LoggerFactory.Create(c => c.AddConsole());//.SetMinimumLevel(LogLevel.Debug));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }
    
    public DbSet<PreHire> PreHire { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            context.AddRange(
                new PreHire
                {
                    StartDate = DateTimeOffset.Now - TimeSpan.FromDays(1)
                },
                new PreHire
                {
                    StartDate = DateTimeOffset.Now + TimeSpan.FromDays(1)
                });

            await context.SaveChangesAsync();
        }

        using (var context = new BloggingContext())
        {
            DateTime startDate = DateTime.Now;

            //Pulls preHire with a start date of '2019-11-25 14:42:54.0833333 +00:00'
            PreHire clientComparePreHire = context.PreHire.Where(x => x.PreHireId == 2).FirstOrDefault();
            if (clientComparePreHire.StartDate.Date >= startDate.Date)
            {
                Console.WriteLine("This works.");
            }

            List<PreHire> serverComparePreHires =
                context.PreHire.Where(q => q.StartDate.Date >= startDate.Date).ToList();
            if (serverComparePreHires.Count == 0)
            {
                Console.WriteLine("This doesn't. This shouldn't be empty.");
            }
        }
    }
}

[CodeSwimBikeRunner]

Our issue cropped up when the date was being stored, not date + time. So to mimic,
2019-11-25 00:00:00.0000000 +00:00
You need to get StartDate = DateTimeOffset.Now.Date.

Note: Our Database Server is hosted in Azure. But I believe the Offset should resolve any oddities with that.

Edit
As Jesse Pointed out it looks like its when the date time are equal. I updated my follow up. Sorry everyone.

[JesseLambert]

@ajcvickers I noticed this issue pops up when the comparison is on the same date. Anything further in the future returns valid results. ie, anything not on 11/25/2019. Its when datetime.date is Equal.

I can start working on providing a full example, but it's going to take some time.

[ajcvickers]

@JesseLambert I have still not been able to reproduce this issue. Could it be a difference in the time zone configured for the database server being different from the one configured in .NET?

[elpikel]

I have similar problem :

var entity = storageContext.Entities .Where(x => x.DateTimeOffset < dateTimeOffset) .FirstOrDefault(); // gives me entity
var result = entity.DateTimeOffset < dateTimeOffset; // result is false

This happens when i update DateTimeOffset and do not save changes. When i save changes and execute query then this works as expected.

It worked fine on 2.x, but it breaks on 3.0

[ajcvickers]

Note for triage: I did some further investigation here against a default install of LocalDb 2019. There certainly seems to be something related to timezones here. I ran the code below using either DateTime.Now.Date, DateTimeOffset.Now.Date, and DateTimeOffset.UtcNow.Date. When UTC is used or when offsets are not used at all, then the results are as expected:

Using DateTime in the model:

StateDate >= 12/6/2019 12:00:00 AM returns 12/6/2019 12:00:00 AM, 12/7/2019 12:00:00 AM
StateDate == 12/6/2019 12:00:00 AM returns 12/6/2019 12:00:00 AM
StateDate <= 12/6/2019 12:00:00 AM returns 12/5/2019 12:00:00 AM, 12/6/2019 12:00:00 AM

Using DateTimeOffset but with UTC time:

StateDate >= 12/6/2019 12:00:00 AM returns 12/6/2019 12:00:00 AM, 12/7/2019 12:00:00 AM
StateDate == 12/6/2019 12:00:00 AM returns 12/6/2019 12:00:00 AM
StateDate <= 12/6/2019 12:00:00 AM returns 12/5/2019 12:00:00 AM, 12/6/2019 12:00:00 AM

When using DateTimeOffset with local time, the server doesn't detect the date as being the same.

StateDate >= 12/6/2019 12:00:00 AM returns 12/7/2019 12:00:00 AM
StateDate == 12/6/2019 12:00:00 AM returns
StateDate <= 12/6/2019 12:00:00 AM returns 12/5/2019 12:00:00 AM, 12/6/2019 12:00:00 AM

It seems likely that this is something to do with how the timezones are handled by SQL Server/SqlClient. I don't think there is anything we can do in EF, but we should discuss.

public class PreHire
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int PreHireId { get; set; }
    public DateTime StartDate { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger 
        = LoggerFactory.Create(c => c.AddConsole());//.SetMinimumLevel(LogLevel.Debug));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            //.UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }
    
    public DbSet<PreHire> PreHire { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            context.AddRange(
                new PreHire
                {
                    PreHireId =  1,
                    StartDate = DateTimeOffset.UtcNow.Date - TimeSpan.FromDays(1)
                },
                new PreHire
                {
                    PreHireId =  2,
                    StartDate = DateTimeOffset.UtcNow.Date
                },
                new PreHire
                {
                    PreHireId =  3,
                    StartDate = DateTimeOffset.UtcNow.Date + TimeSpan.FromDays(1)
                });

            await context.SaveChangesAsync();
        }

        using (var context = new BloggingContext())
        {
            DateTime startDate = DateTime.UtcNow.Date;

            var result = context.PreHire.Where(q => q.StartDate.Date >= startDate.Date).ToList();
            Console.WriteLine($"StateDate >= {startDate.Date} returns {string.Join(", ", result.Select(e => e.StartDate.Date))}");
            
            result = context.PreHire.Where(q => q.StartDate.Date == startDate.Date).ToList();
            Console.WriteLine($"StateDate == {startDate.Date} returns {string.Join(", ", result.Select(e => e.StartDate.Date))}");

            result = context.PreHire.Where(q => q.StartDate.Date <= startDate.Date).ToList();
            Console.WriteLine($"StateDate <= {startDate.Date} returns {string.Join(", ", result.Select(e => e.StartDate.Date))}");
        }
    }
}

[ajcvickers]

Note for triage: I am now not able to get this to fail at all!

[ajcvickers]

@smitpatel to take a look.

[smitpatel]

Repro code:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database
                db.Add(new PreHire
                {
                    // Make sure that following time is more than your current time
                    StartDate = new DateTimeOffset(2019, 12, 23, 14, 42, 54, 83, new TimeSpan())
                });

                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                DateTime startDate = DateTime.Now;

                //Pulls preHire with a start date of '2019-11-25 14:42:54.0833333 +00:00'
                PreHire clientComparePreHire = db.PreHires.Where(x => x.PreHireId == 1).FirstOrDefault();
                if (clientComparePreHire.StartDate.Date >= startDate.Date)
                {
                    Console.WriteLine("This works.");
                }

                var serverComparePreHires = db.PreHires.Where(q => q.StartDate.Date >= startDate.Date).ToList();
                if (serverComparePreHires.Count == 0)
                {
                    Console.WriteLine("This doesn't. This shouldn't be empty.");
                }
            }
            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory ContextLoggerFactory
            => LoggerFactory.Create(b =>
            {
                b
                .AddConsole()
                .AddFilter("", LogLevel.Debug);
            });

        // Declare DBSets
        public DbSet<PreHire> PreHires { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                //.UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                //.UseCosmos("https://localhost:8081", @"C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "_ModelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(ContextLoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public partial class PreHire
    {
        public int PreHireId { get; set; }
        public DateTimeOffset StartDate { get; set; }
    }
}

[smitpatel]

The issue is following
https://github.com/aspnet/EntityFrameworkCore/blob/098785af82c52edeee8ff5c262a8c7e53b9655c5/src/EFCore.SqlServer/Query/Internal/SqlServerDateTimeMemberTranslator.cs#L59-L64

DateTimeOffset.Date returns a datetime but we are incorrectly assigning typeMapping as DateTimeOffset. When we generate the parameter for it, it contains offset but by using date on server side, offset was removed. (SqlServer upcast everything to datetimeoffset(7) when comparing) So we are comparing 2 same date but one has offset and other one does not so they fail comparison based on offset's direction.