How to group by week in Entity Framework Core?

Question

In Entity Framework 6 I can use SqlFunctions.DatePart() method:

var byWeek = data.GroupBy(x => SqlFunctions.DatePart("week", x.Date));

But these classes (DbFunctions and SqlFunctions are not available in Entity Framework Core) (reference).

So my question is How can I group by week in Entity Framework core?

Since currently all EF Core queries having `GroupBy` clause are processed in memory, you could safely use `x.Date.DayOfYear / 7` or something like that. — Ivan Stoev, Mar 08 '17 at 17:31
@IvanStoev not anymore. group-by querys are compiled to sql since ef core 2.1. see my answer for an working example. — cyptus, Jun 10 '19 at 16:21
Note: To anyone finding this make sure you understand the difference between week and iso_week and which you want. The year of an iso week is not always the same as the calendar year (eg. in 2020 Week 1 began on 2010-12-30). https://en.wikipedia.org/wiki/ISO_week_date — Simon_Weaver, Mar 15 '21 at 00:29

Nikolay Kostov · Accepted Answer · 2017-03-08T19:41:07.123

My current workaround for the missing functionality is

var firstMondayOfYear = this.GetFirstMondayOfYear(DateTime.Now.Year);
var entries =
    this.entitiesService.FindForLastMonths(this.CurrentUser.Id, 6)
        .GroupBy(x => ((int)(x.Date - firstMondayOfYear).TotalDays / 7))

The function GetFirstMondayOfYear:

private DateTime GetFirstMondayOfYear(int year)
{
    var dt = new DateTime(year, 1, 1);
    while (dt.DayOfWeek != DayOfWeek.Monday)
    {
        dt = dt.AddDays(1);
    }

    return dt;
}

This grouping gives the week number for the current year and negative values for previous years.

Later you can get the week name by using this getter:

public string WeekName
{
    get
    {
        var year = DateTime.Now.AddYears((int)Math.Floor(this.WeekNumber / 52.0)).Year;
        var weekNumber = this.WeekNumber % 52;
        while (weekNumber < 0)
        {
            weekNumber += 52;
        }

        return $"{year}, W{weekNumber}";
    }
}

Note that this entire query is going to be done in memory so will likely be very slow. — DavidG, Jun 12 '19 at 07:38

score 2 · Answer 2 · answered Jun 07 '19 at 08:53

It is possible to make use of the datepart SQL function by wrapping it with the DbFunctionAttribute. Tricky part is to tell ef core not to handle the datepart type parameter as a string. Example:

DbContext:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

Query:

repository.GroupBy(x => dbContext.DatePart("week", x.CreatedAt));

some more info: https://github.com/aspnet/EntityFrameworkCore/issues/10404

score 0 · Answer 3 · answered Dec 29 '19 at 22:18

0

I happened to be already using a view, so I just added extra columns to that view representing the week, month and year - for easy grouping via EF Core.

OrderDateDt,
DATEPART(week, OrderDateDt) OrderDateDt_Week,
DATEPART(month, OrderDateDt) OrderDateDt_Month,
DATEPART(year, OrderDateDt) OrderDateDt_Year,

answered Dec 29 '19 at 22:18

Simon_Weaver

140,023
84
646
689

Note that datepart(week) cannot be used for an indexed view but I think datepart(iso_week) can. – Simon_Weaver Dec 30 '19 at 20:01
also since I answered this I learnt more about ISO_WEEK than I cared to - so be sure to look into which suits your needs. Also ISO_WEEK for today (12/30/19) is actually 1 as it is part of the first week of next year - so you can't use `year` or you'll end up with 12/30/19 being week one of 2019 which is not right! – Simon_Weaver Dec 30 '19 at 20:03

How to group by week in Entity Framework Core?

3 Answers3

Linked