Comparison between DateTime (SQL Server column) and DateTimeOffset (parameter passed to EF) not consistent with in-memory behavior

[ascott18]

There's an issue when performing a query against SQL server where a .NET DateTimeOffset is being compared against a SQL Server datetime2 column. SQL Server does weird things for implicit vs explicit conversions between datetimeoffset and datetime depending on the context - see https://dba.stackexchange.com/questions/71150/sql-datetime-datetimeoffset2-comparison-implicit-cast-wierdness. This appears to be a manifestation of that weirdness.

In this scenario, I'd expect EF to either explicitly convert my DateTimeOffset query parameter to a DateTime before handing it off to SQL Server, or alternatively, I'd expect EF to throw an error. In any case, I'd expect that the .NET runtime behavior would be the same here as the SQL Server behavior. Unfortunately, it isn't.

This seems pretty similar to #19052, but I don't think its quite the same thing.

Repro:

https://github.com/ascott18/EfDateCompareBug - just dotnet run.

namespace EfDateCompareBug
{
    public class Context : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=EfDateCompareBug;Trusted_Connection=True;");
        }

        public DbSet<Person> People { get; set; }
    }

    public class Person
    {
        public int PersonId { get; set; }
        public DateTimeOffset DateTimeOffset { get; set; }
        public DateTime DateTime { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var db = new Context();
            db.Database.EnsureCreated();

            var dateTimeOffset = new DateTimeOffset(2021, 3, 1, 12, 43, 28, TimeSpan.FromHours(-8));

            var person = new Person 
            { 
                DateTime = dateTimeOffset.DateTime,
                DateTimeOffset = dateTimeOffset,
            };
            db.People.Add(person);
            db.SaveChanges();

            Console.WriteLine("         LHS/Db Column     RHS/Parameter");
            Console.WriteLine("----------------------------------------");
            Console.WriteLine("SqlSvr  DateTime       == DateTime:       {0}", db.People.Any(p => p.DateTime == dateTimeOffset.DateTime));
            Console.WriteLine("SqlSvr  DateTime       == DateTimeOffset: {0}", db.People.Any(p => p.DateTime == dateTimeOffset));
            Console.WriteLine("SqlSvr  DateTimeOffset == DateTime:       {0}", db.People.Any(p => p.DateTimeOffset == dateTimeOffset.DateTime));
            Console.WriteLine("SqlSvr  DateTimeOffset == DateTimeOffset: {0}", db.People.Any(p => p.DateTimeOffset == dateTimeOffset));
            Console.WriteLine("----------------------------------------");
            Console.WriteLine(".NET    DateTime       == DateTime:       {0}", person.DateTime == dateTimeOffset.DateTime);
            Console.WriteLine(".NET    DateTime       == DateTimeOffset: {0}", person.DateTime == dateTimeOffset);
            Console.WriteLine(".NET    DateTimeOffset == DateTime:       {0}", person.DateTimeOffset == dateTimeOffset.DateTime);
            Console.WriteLine(".NET    DateTimeOffset == DateTimeOffset: {0}", person.DateTimeOffset == dateTimeOffset);
        }
    }
}

Repro output:

         LHS/Db Column     RHS/Parameter
----------------------------------------
SqlSvr  DateTime       == DateTime:       True
SqlSvr  DateTime       == DateTimeOffset: False
SqlSvr  DateTimeOffset == DateTime:       True
SqlSvr  DateTimeOffset == DateTimeOffset: True
----------------------------------------
.NET    DateTime       == DateTime:       True
.NET    DateTime       == DateTimeOffset: True
.NET    DateTimeOffset == DateTime:       True
.NET    DateTimeOffset == DateTimeOffset: True

As you can see, the only case here that behaves inconsistently is when comparing a DateTimeOffset query parameter against a DateTime database column. This happens for all comparisons, not just equality.

Include provider and version information

EF Core version: 5.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5
Operating system: Win10 2004
IDE: VS 2019 16.8.5

[maumar]

The way db.People.Any(p => p.DateTime == dateTimeOffset)) gets to us is in the form of:

DbSet<Person>().Any(p => (DateTimeOffset)p.DateTime == __dateTimeOffset_0) and we naively translate it to:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [People] AS [p]
        WHERE CAST([p].[DateTime] AS datetimeoffset) = @__dateTimeOffset_0) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

The best we could do here is issue a warning when we recognize the pattern of comparing DateTimeOffset to a DateTime with explicit cast to DateTimeOffset. Note that in the repro, if the dateTimeOffset is constructed with a different offset value, the c# comparison of person.DateTime == dateTimeOffset results in false. So, we can't simply rewrite the query to p.DateTime == dateTimeOffset.DateTime (which still returns true, regardless of the offset value). We also can't throw because it would be a breaking change in cases when current behavior is expected and consistent with c#