CODEWITHSUNDEEP
c#linq
mohammad
mohammad

Reputation: 1026

Linq - Group by week on the List

I want to group by week my data like this:

var result = stats.GroupBy(i => SqlFunctions.DatePart("week", i.date))
            .Select(g => new ReportModel
            {
                clicks = g.Select(x => x.clicks).Sum(),
                impressions = g.Select(x => x.impressions).Sum(),
                ...
            });

But I get this error:

This function can only be invoked from LINQ to Entities.

What's the problem and how can I fixed it?

Upvotes: 9

Views: 10619

Answers (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:

stats.GroupBy(i => i.date.StartOfWeek(DayOfWeek.Monday));

Upvotes: 11

Evk
Evk

Reputation: 101623

SqlFunctions.DatePart (and other such functions) cannot be called as a regular method. It can only be used as a part of database query (with IQueryable). So you have to use another approach, for example:

stats.GroupBy(i => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(
    i.date, CalendarWeekRule.FirstDay, DayOfWeek.Monday));

Pay attention to the culture used and also parameters of GetWeekOfYear (what counts as first week of year and what counts as first day of week).

Upvotes: 22

Related Questions