DateTime property error in LINQ where clause

Question

I have a pretty simple method that uses some LINQ to access data in my db.

I have some Items that are storing a date that they were created. The dates include an offset, so it's stored as a datetimeoffset type in the db. I'm trying to filter the items by date, and to do so I need to subtract the offset hours to compare them:

public async Task<IEnumerable<Item>> GetItems(DateTime? startDate)
{
    var items = _dbContext.Items                                
                        .AsQueryable();

    if (startDate != null)
    {
        items = items.Where(i =>
            i.DateCreated.DateTime.AddHours(i.DateCreated.Offset.Hours) >= startDate.Value);
    }

    return await items
                     .ToListAsync();
}

But when ToListAsync() is called, I get this error:

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where ([e].DateCreated.DateTime.AddHours(Convert([e].DateCreated.Offset.Hours, Double)) >= __startDate_Value_0)' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

The issue seems to be with the AddHours method, if i take that out it works just fine. But I don't know if there's a way to get this working without using that method.

Is there a different strategy I can use here?

Answer 1

Actually translation of DateTime.AddHours method is supported.

The problem is that currently EF Core cannot translate most (if not all) of the members of DateTimeOffset, in this particular case - DateTime property (same for Offset, DateTimeUtc etc).

Fortunately it does support translation of DateTimeOffset comparisons, so the solution is to do it other way around - convert the DateTime parameters to DateTimeOffset and do simple comparisons inside the query, e.g.

if (startDate != null)
{
    // Note: must be a variable outside the query expression tree
    var startDateOffset = new DateTimeOffset(startDate.Value);
    items = items.Where(i => i.DateCreated >= startDateOffset);
}

Answer 2

Updated Answer (EF Core):

According to this link, DbFunctions is not currently supported in EF Core.

If you want to call AddHour using EF Core, one option is to define AddHour as a Scalar function in your DB, and then you can call it in your LINQ query.

This document explains how it can be done:

Declare a static method on your DbContext and annotate it with DbFunctionAttribute:

public class MyDbContext : DbContext
{
    [DbFunction]
    public static int AddHours(DataTime source, int hours)
    {
        // you don't need any implementation 
        throw new Exception(); 
    }
}

Methods like this are automatically registered. Once registered, calls to the method in a LINQ query can be translated to function calls in SQL:

items = items.Where(i =>
    MyDbContext.AddHours(i.DateCreated, i.DateCreated.Offset.Hours) >= startDate.Value);

Disclaimer: Personally I don't like this solution. Writing a new method, just to throw an exception, is far from an elegant design. Unfortunately, you don't have much of a choice, if you want to use EF Core.

---

Original Answer (EF 4/5/6):

AddHour is not a function in your DB. It needs to be translated to the corresponding function in the DB.

If you are using SQL Server then you can use this code, which would translate AddHour to corresponding DB function:

items = items.Where(i =>
    DbFunctions.AddHour(i.DateCreated, i.DateCreated.Offset.Hours) >= startDate.Value);

If you are not using SQL Server, then you need to define AddHour function in your DB, once defined you can use DbFunctions.AddHour, to call it.