CODEWITHSUNDEEP
c#asp.netlinqentity-frameworklinq-to-entities
Gordon Copestake
Gordon Copestake

Reputation: 1647

Linq to Entities group by week number

I am using ASP.NET v4.5 and linq to entities I am trying to group my data by week using the below code

var groupedByWeek = salesOrdersList
    .GroupBy(i => i.DueDate.AddDays(-(int)i.DueDate.DayOfWeek));

However I am getting a "yellow screen of death" with the error:

LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

Ideally I would like to put 

var groupedByWeek = salesOrdersList.GroupBy(i => i.DueDate.WeekNumber);

But life isn't that easy!

Does anyone know a way of using week numbers with Linq to Entities?

Upvotes: 9

Views: 8333

Answers (3)

Enver Yumrutas
Enver Yumrutas

Reputation: 21

For daily:

var daily = salesOrdersList.Where(x => (long)(x.DueDate - new DateTime(1970, 1, 1, 0, 0, 0)).TotalDays == (long)(Datetime.Now.ToLocalTime()- new DateTime(1970, 1, 1, 0, 0, 0)).TotalDays ).ToList();

OR

var daily = salesOrdersList.Where(x => x.DueDate.Day == Datetime.Now.ToLocalTime().Day).ToList();

For Weekly:

var weekly = salesOrdersList.Where(x => (long)(x.DueDate - new DateTime(1970, 1, 1, 0, 0, 0)).TotalDays / 7 == (long)(Datetime.Now.ToLocalTime()- new DateTime(1970, 1, 1, 0, 0, 0)).TotalDays / 7).ToList();

For Monthly

var monthly= salesOrdersList.Where(x => x.DueDate.Month== Datetime.Now.ToLocalTime().Month).ToList();

Upvotes: 2

hoss77
hoss77

Reputation: 385

Or you can get the date of the first day in the week then group by that date.

To get the date of the first day in the week. you can use this code:

public static class DateTimeExtensions
{
    public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek)
    {
        int diff = dt.DayOfWeek - startOfWeek;
        if (diff < 0)
        {
            diff += 7;
        }
        return dt.AddDays(-1 * diff).Date;
    }
}

then you can group by the first date of the week like this:

var groupedByWeek = salesOrdersList.GroupBy(i => i.DueDate.StartOfWeek(DayOfWeek.Monday));

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125660

Use SqlFunctions.DatePart() method:

var groupedByWeek = salesOrdersList.GroupBy(i => SqlFunctions.DatePart("week", i.DueDate));

It will add DATEPART sql function call into generated SQL query.

Upvotes: 19

Related Questions