CODEWITHSUNDEEP
linqentity-frameworkdatetimelinq-to-entitiesdate-range
Steven Grigoleit
Steven Grigoleit

Reputation: 23

Entity Framework: Best way to query for a set of dates using Linq

I have a set of unique DateTimes (without time portion) that the user can select from the user interface. This is not only a range like "LastWeek" or "LastMonth". The user can selcet every single day he wants. What might be the best way to Linq-query in EntityFramework for matching results? I have a table Foo with an Attribute CreatedAt, which stores information with time portion. Of course I dont want to check the CreatedAt-Property on client side, SqlServer should do the job for me.

I think it should be someting like:

var query = _entities.Foo.Where(x => x.UserID == user.ID);

if (selectedDates.IsNullOrEmpty() == false)
    query = query.Where(x => x.CreatedAt 'IsIn' selectedDates);

or:
foreach (var date in selectedDates)
    query = query.Where(x => x.CreatedAt.Year == date.Year && x.Month == date.Month && x.Day == date.Month) //but of course here I have the problem that I have to use the 'Or'-Operator, not 'And'.

How can I accomplish this?

Upvotes: 2

Views: 1818

Answers (3)

Martin
Martin

Reputation: 181

You can use EntityFunctions.TruncateTime() to perform a truncation of the date on the server. Then something along the lines of this should work.

query = query.Where(x => selectedDates.Contains(EntityFunctions.TruncateTime(x));

Upvotes: 4

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

Try query = query.Where(x => selectedDates.Contains(x.CreatedAt));

Upvotes: 1

Steven
Steven

Reputation: 172875

You can call the Contains extension method on selectedDates. Entity Framework 4.0 will understand this and will translate it to an IN operator in SQL:

if (selectedDates.IsNullOrEmpty() == false)
    query = query.Where(x => selectedDates.Contains(x.CreatedAt));

Upvotes: 1

Related Questions