linqgroup-by.net-coreentity-framework-coreweek-number

How to group by week in Entity Framework Core?


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?


Solution

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