Add a Date in Linq to Entities

Question

With Linq to Entities, I am trying to query a Log table to find rows near a matching row. I am having trouble with adding a date inside the query. This is what I have so far.

from
    l in objectSet.Logs
let
    match = objectSet.Logs.Where(whatever).FirstOrDefault()
where
       l.Timestamp > (match.Timestamp - twoHours)
    && l.Timestamp < (match.Timestamp + twoHours)
select
    l

Leaving out the "whatever" condition that finds the row I'm interested in, "twoHours" has variably been a time span, a .AddHours() function and so forth. I haven't found the right way that EF can generate SQL that adds the value from a field (match.Timestamp) to a constant.

The obvious solution is to do the "match" query first and then use the literal value in a second query, but I have simplified the code example here to the main problem (adding dates in the query) and in actual fact my query is more complex and this would not be ideal.

Cheers

Answer 1

You can generate an AddHours using the EntityFunctions class.

from
    l in objectSet.Logs
let
    match = objectSet.Logs.Where(whatever).FirstOrDefault()
where
       (l.Timestamp > EntityFunctions.AddHours(match.Timestamp, -1 * twoHours))
    && // ...
select
    l

However, don't expect this WHERE to be optimized with an index unless you have an expression index on the column.

Answer 2

EntityFunctions is deprecated in favor of DbFunctions

public int GetNumUsersByDay(DateTime Date)
        {
            using (var context = db)
            {
                var DateDay = new DateTime(Date.Year, Date.Month, Date.Day);
                var DateDayTomorrow = DateDay.AddDays(1);
                return context.Users.Where(m => DbFunctions.AddHours(m.DateCreated,-5) >= DateDay && m.DateCreated < DateDayTomorrow).Count();
            }
        }