CODEWITHSUNDEEP
c#asp.net.netsql-serverlinq
Libin Joseph
Libin Joseph

Reputation: 7342

Compare days in Linq

There is a set of records with a created date field in a table. How do I select all the records that were created on a given day of the week. e.g. all records that were created on a Monday, it could be this Monday, last Monday or the Monday before.

This is what I have written so far:

from ob in obList
where SqlFunctions.DateDiff("days", today, ob.StartDate) % 7 == 0
select ob.CustomerOnboardingId

But I get an error on this:

{"This function can only be invoked from LINQ to Entities."}

Update 1:

obList is a collection in memory. And today is variable that holds DateTime.Today;

Upvotes: 2

Views: 872

Answers (2)

Gilad Green
Gilad Green

Reputation: 37299

As the error specifies you are trying to execute a method of Linq to Entities in-memory which can't be done. When a linq query, in the context of a linq2Sql or EF is executed it is translated into an sql and that is when functions such as the one you used get their meaning - They implement how to do it in sql. In the in-memory collections they have no meaning.

(It is just like you will try to execute a method defined in your code on a linq-to-entities query and will get that it is not supported by linq-to-entities)

Instead of calculating (day1 - day2) % 7 you can use the DayOfWeek property of DateTime:

DateTime day = new DateTime(2016, 1, 15); // Friday

List<dynamic> collection = new List<dynamic>
{
    new { CustomerOnboardingId = "Item1", StartDate = new DateTime(2016,1,1) }, // Friday
    new { CustomerOnboardingId = "Item2", StartDate = new DateTime(2016,1,2) },
    new { CustomerOnboardingId = "Item3", StartDate = new DateTime(2016,1,8) }, // Friday
};

var result = (from item in collection
              where item.StartDate.DayOfWeek == day.DayOfWeek
              select item.CustomerOnboardingId).ToList();

// result = Item1, Item3

Upvotes: 1

ocuenca
ocuenca

Reputation: 39326

You don't need to call that function to get the difference of days, which, as the exception said, it can be only used in Linq to Entities. In Linq to Objects you can do the following:

from ob in obList
where (today - ob.StartDate).TotalDays  % 7 == 0
select ob.CustomerOnboardingId

Upvotes: 2

Related Questions