Linq - by weeks on the list

Question

I want to group by week the following data:

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 called from LINQ in Entities.

What is the problem and how can I fix it?

Answer 1

SqlFunctions.DatePart(and other such functions) cannot be called the usual method. It can only be used as part of a database query (s IQueryable). Therefore, you need to use a different approach, for example:

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

Pay attention to the culture used, as well as the parameters GetWeekOfYear(which is considered the first week of the year and is considered the first day of the week).

Answer 2

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

. :

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;
    }
}

:

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