Libin Joseph Libin Joseph - 1 month ago 15
ASP.NET (C#) Question

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;

Answer

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