LINQ to Entities DateTime Compare

Question

I have problem with comparing dates in LINQ to entities expression. I would like to check if DateTime == DateTime - whole day.

I wanted to do it like that:

 return context.Events.Any(x => 
                            x.UserId == id 
                            && x.Date >= date 
                            && x.Date < date.AddDays(1) 
                            && x.Type == EventType.StartWork);

The problem is that above query is not correct with LINQ because of AddDays() method.

I`ve tried to use DbFunctions like below:

return context.Events.Any(x => 
                            x.UserId == id 
                            && x.Date >= date 
                            && x.Date < DbFunctions.AddDays(date, 1) 
                            && x.Type == EventType.StartWork);

Also this one:

  return context.Events.Any(x => 
                                x.UserId == id 
                                && DbFunctions.TruncateTime(date.Date) == date.Date 
                                && x.Type == EventType.StartWork);

None of these queries are not giving expected results.

Answer 1

Just create 2 dates:

var datePlusOneDay = date.AddDays(1);

return context.Events.Any(x => x.UserId == id 
                         && x.Date >= date 
                         && x.Date < datePlusOneDay 
                         && x.Type == EventType.StartWork);

Also I'm not sure but problem could be that your date can have not only date part but also time part.

So to be sure that you select only date part of your DateTime variable you can do like this:

date = date.Date;
var datePlusOneDay = date.AddDays(1);

Answer 2

Another way that should work in Linq-To-Entities even if you can't initialize the date before the query is to use System.Data.Entity.DbFunctions.DiffDays:

return context.Events
    .Any(x => x.UserId == id && x.Date > date 
        && System.Data.Entity.DbFunctions.DiffDays(x.Date, date) < 1
        && x.Type == EventType.StartWork);