How to compare dates in Entity Framework with Repository Pattern

Question

I'm trying to do a Query in Entity Framework using Repository pattern like this:

Namespace: Services

List<Data.Item> itemToday = repositoryUser.Get(i => i.UserId.Equals(user.Id) &&
                                               i.Created.Date < DateTime.Now.Date)
                                          .ToList();

Namespace: Data (my repository)

public IEnumerable<T> Get(System.Linq.Expressions.Expression<Func<T, bool>> expressionFilter)
{
    return this.dbContext.Set<T>().Where(expressionFilter);
}

This will throw this error:

The specified type member 'Date' is not supported in LINQ to Entities

I found these questions to solve this with an Entity Framework method to convert date types and compare

1. Compare Date values in Entity Framework
2. Compare Dates using LINQ to Entities (Entity Framework)

But these solutions use a EF method that's stored in Data namespace, and I'm querying at Service namespace and I shouldn't call a repository function in Service layer, right? So how can I do this?

Answer 1

Since you pass the where down to EF your will need to do it the EF way (look below). Otherwise you will have to parse the query itself and send the correct one onwards to EF, and that is not very easy. I would probably write a method with the parameters needed for the specific scenario:

public List<Data.Item> Get<T>(int id, DateTime createdBeforeDate)
{
     return this.dbContext.Set<T>().Where(i => i.UserId.Equals(id) &&
                                               SqlFunctions.DateDiff("DAY", i.Created, createdBeforeDate) > 0))
                                   .ToList();
}

You can use the SqlFunctions class for these kinds of scenarios. You can use the SqlFunctions.DateDiff function with a parameter of "DAY" (I found the answer here: https://stackoverflow.com/a/14778874/455904).

In your case I believe SqlFunctions.DateDiff("DAY", i.Created, DateTime.Now) > 0) should work

List<Data.Item> itemToday = repositoryUser.Get(i => i.UserId.Equals(user.Id) &&
                                               SqlFunctions.DateDiff("DAY", i.Created, DateTime.Now) > 0))
                                          .ToList();

As a side note DateTime.Today can be used instead of DateTime.Now.Date