Linq - Group by week on the List

Question

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?

Answer 1

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).