Is this request generated by EF Core buggy or is it my code?

Question

Introduction

I'm using EF Core with .NET 5.0 and SQL Server Express. Basically I'm wondering if it generated a buggy SQL query or if my code is buggy (probably :D). I provided a mre at the bottom of the question, but hopefully the problem becomes evident from the data I've collected (I've already asked a similar question, but felt that it needed a complete overhaul)

Setup

I have a record and a DbContext like the following. It's stripped down to the important property Moment, which must be of type DateTimeOffset (company guideline).

private class Foo
{
    public int ID { get; set; }
    public DateTimeOffset Moment { get; set; }
}

private class Context : DbContext
{
    public Context(DbContextOptions<Context> options) : base(options) {}

    public DbSet<Foo> Foos { get; set; }
}

The respective column in the generated database has a datatype of datetimeoffset(7), seems fine.

I initialized the database with this data (5 consecutive days, each at midnight in the respective time zone):

context.Foos.Add(new Foo() { Moment = DateTimeOffset.Parse("2021-04-21 00:00 +02:00"), });
context.Foos.Add(new Foo() { Moment = DateTimeOffset.Parse("2021-04-22 00:00 +02:00"), });
context.Foos.Add(new Foo() { Moment = DateTimeOffset.Parse("2021-04-23 00:00 +02:00"), });
context.Foos.Add(new Foo() { Moment = DateTimeOffset.Parse("2021-04-24 00:00 +02:00"), });
context.Foos.Add(new Foo() { Moment = DateTimeOffset.Parse("2021-04-25 00:00 +02:00"), });

Now I want to query all records Moment >= start && Moment <= end while ignoring the time with these parameters:

var start = DateTimeOffset.Parse("2021-04-22 00:00 +02:00");
var end = DateTimeOffset.Parse("2021-04-24 00:00 +02:00");

I expect to get 3 records and came up with 3 queries that seem identical to me, however, the second yields a different result:

Queries

private static async Task Query1(Context context, DateTimeOffset start, DateTimeOffset end)
{
    var records = await context.Foos
        .Where(foo => foo.Moment.Date >= start.Date && foo.Moment.Date <= end.Date)
        //... Finds 3 records, expected
}

private static async Task Query2(Context context, DateTimeOffset start, DateTimeOffset end)
{
    start = start.Date; // .Date yields DateTime -> implicit conversion to DateTimeOffset?
    end = end.Date;

    var records = await context.Foos
        .Where(foo => foo.Moment.Date >= start && foo.Moment.Date <= end)
        // ... Finds only 2 records, unexpected
}

private static async Task Query3(Context context, DateTimeOffset start, DateTimeOffset end)
{
    var start2 = start.Date; // start2 and end2 are of type DateTime now
    var end2 = end.Date;

    var records = await context.Foos
        .Where(foo => foo.Moment.Date >= start2 && foo.Moment.Date <= end2)
        // ... Finds 3 records, expected
}

Results

I also made a LINQ version of each query where I queried data from a List<Foo>:

private static void Query1(List<Foo> foos, DateTimeOffset start, DateTimeOffset end)
{
    var records = foos
        .Where(foo => foo.Moment.Date >= start.Date && foo.Moment.Date <= end.Date)
        //...
}

Function	Expected number of records	Records from DB	Records when using LINQ
Query1	3	3	3
Query2	3	2	3
Query3	3	3	3

---

Why does the second database query only return 2 records?

I know that the first and third query compare DateTime with DateTime while the second one compares DateTime with DateTimeOffset. Thus I'm wondering why the LINQ-versions behave differently.

Traced Queries

I traced the actual queries that were send to the SQL Server and they are different, however, I don't really understand why they lead to different results (not much SQL experience):

-- From Query1()
exec sp_executesql N'SELECT [f].[ID]
FROM [Foos] AS [f]
WHERE (CONVERT(date, [f].[Moment]) >= @__start_Date_0) AND (CONVERT(date, [f].[Moment]) <= @__end_Date_1)',N'@__start_Date_0 datetime2(7),@__end_Date_1 datetime2(7)',@__start_Date_0='2021-04-22 00:00:00',@__end_Date_1='2021-04-24 00:00:00'

-- From Query2()

exec sp_executesql N'SELECT [f].[ID]
FROM [Foos] AS [f]
WHERE (CAST(CONVERT(date, [f].[Moment]) AS datetimeoffset) >= @__start_0) AND (CAST(CONVERT(date, [f].[Moment]) AS datetimeoffset) <= @__end_1)',N'@__start_0 datetimeoffset(7),@__end_1 datetimeoffset(7)',@__start_0='2021-04-22 00:00:00 +02:00',@__end_1='2021-04-24 00:00:00 +02:00'

-- From Query3()
exec sp_executesql N'SELECT [f].[ID]
FROM [Foos] AS [f]
WHERE (CONVERT(date, [f].[Moment]) >= @__start2_0) AND (CONVERT(date, [f].[Moment]) <= @__end2_1)',N'@__start2_0 datetime2(7),@__end2_1 datetime2(7)',@__start2_0='2021-04-22 00:00:00',@__end2_1='2021-04-24 00:00:00'

MRE

Tested with Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer Version5.0.6.

namespace Playground
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;

    using Microsoft.EntityFrameworkCore;

    public static class Program
    {
        private class Foo
        {
            public int ID { get; set; }
            public DateTimeOffset Moment { get; set; }
        }

        private static Context CreateContext()
        {
            var connectionString = $"Data Source=.\\SQLEXPRESS;Initial Catalog=FOO_DB;Integrated Security=SSPI";
            var optionsBuilder = new DbContextOptionsBuilder<Context>();

            optionsBuilder.UseSqlServer(connectionString).EnableSensitiveDataLogging();

            var context = new Context(optionsBuilder.Options);

            context.Database.EnsureCreated();

            return context;
        }

        private class Context : DbContext
        {
            public Context(DbContextOptions<Context> options) : base(options) { }

            public DbSet<Foo> Foos { get; set; }
        }

        private static async Task Query1(Context context, DateTimeOffset start, DateTimeOffset end)
        {
            var records = await context.Foos
                .Where(foo => foo.Moment.Date >= start.Date && foo.Moment.Date <= end.Date)
                .Select(foo => foo.ID)
                .ToListAsync();

            Console.WriteLine($"Query1 in DB found {records.Count} records");
        }

        private static async Task Query2(Context context, DateTimeOffset start, DateTimeOffset end)
        {
            start = start.Date;
            end = end.Date;

            var records = await context.Foos
                .Where(foo => foo.Moment.Date >= start && foo.Moment.Date <= end)
                .Select(foo => foo.ID)
                .ToListAsync();

            Console.WriteLine($"Query2 in DB found {records.Count} records");
        }

        private static async Task Query3(Context context, DateTimeOffset start, DateTimeOffset end)
        {
            var start2 = start.Date;
            var end2 = end.Date;

            var records = await context.Foos
                .Where(foo => foo.Moment.Date >= start2 && foo.Moment.Date <= end2)
                .Select(foo => foo.ID)
                .ToListAsync();

            Console.WriteLine($"Query3 in DB found {records.Count} records");
        }

        public async static Task Main()
        {
            var context = CreateContext();
            var foos = new List<Foo>() {
                    new Foo() { Moment = DateTimeOffset.Parse("2021-04-21 00:00 +02:00"), },
                    new Foo() { Moment = DateTimeOffset.Parse("2021-04-22 00:00 +02:00"), },
                    new Foo() { Moment = DateTimeOffset.Parse("2021-04-23 00:00 +02:00"), },
                    new Foo() { Moment = DateTimeOffset.Parse("2021-04-24 00:00 +02:00"), },
                    new Foo() { Moment = DateTimeOffset.Parse("2021-04-25 00:00 +02:00"), },
                };

            if (!context.Foos.Any())
            {
                await context.AddRangeAsync(foos);
            }

            await context.SaveChangesAsync();

            var start = DateTimeOffset.Parse("2021-04-22 00:00 +02:00");
            var end = DateTimeOffset.Parse("2021-04-24 00:00 +02:00");

            await Query1(context, start, end);
            await Query2(context, start, end);
            await Query3(context, start, end);

            context.Dispose();
        }
    }
}

Answer 1

The difference between second and other two LINQ queries (hence the different translation - CAST ... as datetimeoffset) is that the others use DateTime comparisons, while this uses DateTimeOffset comparisons. Because when start type is DateTimeOffset, the expression

foo.Moment.Date >= start

by CLR type system rules (which apply to C# generated expression trees) is actually

((DateTimeOffset)foo.Moment.Date) >= start

Same for foo.Moment.Date <= end.

And now comes the difference of DateTime to DateTimeOffset conversion between the two execution contexts - client (CLR) and server (SqlServer in this case):

CLR

This method is equivalent to the DateTimeOffset constructor. The offset of the resulting DateTimeOffset object depends on the value of the DateTime.Kind property of the dateTime parameter:

• If the value of the DateTime.Kind property is DateTimeKind.Utc, the date and time of the DateTimeOffset object is set equal to dateTime, and its Offset property is set equal to 0.

• If the value of the DateTime.Kind property is DateTimeKind.Local or DateTimeKind.Unspecified, the date and time of the DateTimeOffset object is set equal to dateTime, and its Offset property is set equal to the offset of the local system's current time zone.

SqlServer

For conversion to datetimeoffset(n), date is copied, and the time is set to 00:00.0000000 +00:00

Now you should clearly see the difference. CLR Date property returns DateTime with Inspecified kind, which then is converted back to DateTimeOffset using your local system's current time zone offset - most likely +2 according the the way you initialize the variables. While in SQL query the column value is converted with 0 offset. And since DateTimeOffset comparisons use all the members (including offset), you can get different results for some data values.

Of course, when running the same under LINQ to Objects context, it's simply compiled and executed locally using the CLR rules, hence no differences.

Shortly, never rely on conversions in non LINQ to Objects queries. Use the explicit operators/methods as in the first query, or correct data type variables as in the third query to make sure you are comparing same data types with no hidden and nondeterministic conversions.