EF Core query only DateTime of DateTimeOffset cannot be translated;-CodePudding

I'm having an issue where I want to query based on the DateTime only of a DateTimeOffset SQL Server column and I'm wondering if it's possible to do with EF Core.

If I have appointments all over the world and my business logic is recording them in their local DateTimeOffset accurately, I want to be able to get the appointments of a specific day regardless of time zone, however, I get an exception that the query cannot be translated when I try the following:

public class Appointment
{
   public int Id {get;set;}
   public DateTimeOffset BeginTime {get;set;}
}

DateTime queryDay = new DateTime(2021, 1, 1);

var results = dbContext.Appointments.Where(a => a.BeginTime.DateTime >= queryDate && a.BeginTime.DateTime < queryDay.AddDays(1)).ToList();

Is there anyway to do this with EF Core? I mean, in the example, I'm just trying to get a specific date, but, in reality, I want to be able to do it for any datetime with any time values, etc.

In other words, I'm not looking to filter by a universal time range, but, rather, by a time ranges without the offset considered.

Doesn't seem to work even if I create a [NotMapped] property that returns BeginTime.DateTime.

Edit:

Exact error:

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(t => True && t.BeginTime.DateTime >= __fakeStartDate_1 && t.BeginTime.DateTime < __fakeEndDate_2)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

CodePudding user response：

SqlServer provider supports the so called "double cast" construct (first to object, then to other type, e.g. (DateTime)(object)) which tricks the C# compiler to accept a conversion which normally fails (and will fail if executed in LINQ to Objects context), and EF Core translator, forcing the latter to emit conversion from datetimeoffset to datetime2 using CASToperator.

e.g.

var query = dbContext.Appointments
    .Where(a => ((DateTime)(object)a.BeginTime) >= queryDate 
        && ((DateTime)(object)a.BeginTime) < queryDate.AddDays(1));

succesfully translates to

DECLARE @__queryDate_0 datetime2 = '2021-01-01T00:00:00.0000000';
DECLARE @__AddDays_1 datetime2 = '2021-01-02T00:00:00.0000000';

SELECT [a].[Id], [a].[BeginTime]
FROM [Appointments] AS [a]
WHERE (CAST([a].[BeginTime] AS datetime2) >= @__queryDate_0) AND (CAST([a].[BeginTime] AS datetime2) < @__AddDays_1)

CodePudding user response：

There is a round about way that I don't like so much but it doesn't avoid the index on the column and it should work. It would be something like this:

public IEnumerable<Appointments> GetAppointments(DateTime start, DateTime end)
{
  var bufferStart = start.AddDays(-1);
  var bufferEnd = end.AddDays(1);

  return dbContext.Appointments
   .Where(a => a.BeginTime >= bufferStart && a.BeginTime < bufferEnd)
   .AsEnumerable()
   .Where(a => a.BeginTime.DateTime >= start && a.BeginTime.DateTime < end);
}

Obviously, there's a bunch of reasons I don't like this so still looking for a better answer. Would be grateful if anyone has one.