Reputation: 3422
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.
Upvotes: 6
Views: 19933
Reputation: 12491
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);
Upvotes: 5
Reputation: 8696
You can achieve what you want by using DbFunctions.TruncateTime but just on both sides of equation:
return context.Events.Any(x =>
x.UserId == id
&& DbFunctions.TruncateTime(x.Date) == DbFunctions.TruncateTime(date)
&& x.Type == EventType.StartWork);
Upvotes: 4
Reputation: 460278
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);
Upvotes: 4