CODEWITHSUNDEEP
c#entity-frameworklinqdatetime
miechooy
miechooy

Reputation: 3422

LINQ to Entities DateTime Compare

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

Answers (3)

teo van kot
teo van kot

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

Adil Mammadov
Adil Mammadov

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

Tim Schmelter
Tim Schmelter

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

Related Questions