How to group by week in Entity Framework Core?

.net-core entity-framework-core group-by linq week-number

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?

1
5
3/8/2017 3:20:08 PM

Accepted Answer

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}";
    }
}
3
3/8/2017 7:41:07 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow