EF Core query only DateTime of DateTimeOffset cannot be translated;

Question

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.

Have you tried precalculating queryDay.AddDays(1) and put it into another variable (queryDayPlusOne)? That should solve it, imho. — TomTom, Oct 27 '21 at 18:05
Why don't use DateTimeOffset for the queryDay variable? You may have to create queryDayFrom & queryDayTo variables with type is DateTimeOffset. — LHA, Oct 27 '21 at 18:07
@TomTom doesn't solve it. It's not the issue. I should have dropped that .AddDays out to avoid confusion — Tamer Rifai, Oct 27 '21 at 18:26
@Loc it definitely wouldn't throw an error if I did that, however, I wouldn't get the results I want, right? I mean, what DateTimeOffset value should I use? — Tamer Rifai, Oct 27 '21 at 18:27

score 5 · Answer 1 · answered Oct 27 '21 at 18:37

5

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)

answered Oct 27 '21 at 18:37

Ivan Stoev

195,425
15
312
343

1

The column cast will preclude a seek if an index in the column exists, resulting in a full scan. – Dan Guzman Oct 27 '21 at 18:58
Wow. Thanks so much. It is almost perfect and I'll probably use it but, if like @DanGuzman said it's going to ignore the index, it will be a performance issue as I do have indexes on the columns. – Tamer Rifai Oct 27 '21 at 20:59
sir can you please help me with this question : https://stackoverflow.com/questions/69745866/problem-in-implementing-order-by-logic-for-multiple-condition-to-order-the-final – I Love Stackoverflow Oct 27 '21 at 21:52
1

Of course the index will be ignored. But that has nothing to do with the OP issue *"EF Core query only DateTime of DateTimeOffset cannot be translated"*. You wanted SQL translation of `DateTimeOffset.DateTime` property and the answer provided it (as if was supported naturally by EF Core). The way you defined it, current question is closed for me. If you want an efficient way of seeking such column in database, that's completely different question, hence needs creating new SO question. @DanGuzman Your comment is correct, but off topic for the current question. – Ivan Stoev Oct 28 '21 at 09:49

score 0 · Answer 2 · answered Oct 27 '21 at 21:10

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.

EF Core query only DateTime of DateTimeOffset cannot be translated;

2 Answers2