2

I have a table which has column DateTime. And I want to group this by week. There are no problem to group by date or by month. But how to group by week?

I need to get something like this. And in should work with IQueryable.

var peoplePerWeeks = await context.People
                                  .GroupBy(x => x.Date.StartOfWeek)
                                  .Select(x => new { Date = x.key, Number = x.Count()})
                                  .ToListAsync();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel
  • 61
  • 6

2 Answers2

0

I didn't try if this works in EF 6 as well, but this has been working for me using EF Core:

Define a new method in your DataContext class like this:

public DateTimeOffset DatePart(string datePart, DateOnly date) => throw new Exception();

Please note that the type of the date parameter to that method should match the type of the date column you are trying to group on. Don't worry about the Exception throwing - Entity Framework overrides that method for you, the actual implementation of the method you provide doesn't matter.

Then go ahead and override the OnModelCreating method in your DataContext as follows (if you already override it to define your schema anyway, only add this code to it):

protected override void OnModelCreating(ModelBuilder modelBuilder)
{            
    modelBuilder
        .HasDbFunction(typeof(DataContext).GetMethod(nameof(DatePart)))
        .HasName("date_trunc");

}

And now you can start writing queries grouping on week, month, year...:

var peoplePerWeeks = await context.People
                                  .GroupBy(x => context.DatePart("week", x.Date))
                                  .Select(x => new { Date = x.key, Number = x.Count()})
                                  .ToListAsync();

Please note that the grouping key will not be the week number, but the date of the monday of each week. If you want to get the calendar week number for that date, you can either compute that in memory (e.g. using the ISOWeek class, which should not create a big overhead, since you still only load aggregated data per week from the database), or you could add a second database function to your DataContext similar to how you added the date_trunc function, but use the date_part function and add that call to your select:

.Select(x => new { MondayOfWeek = x.Key, CalendarWeek = context.DatePart("week", x.Key), Number = x.Count()})
Schweder
  • 1,464
  • 2
  • 13
  • 19
-1

tried anything like? (this is just my example data, no postgres or entity framework, see last note, but you want to group on week start )

var people = new List<(DateTime Date, string fullName)> {
    ( new DateTime(2022, 3, 1), "fred"),
    ( new DateTime(2022, 3, 2), "b"),
    ( new DateTime(2022, 3, 3), "c"),
    ( new DateTime(2022, 3, 4), "d"),
    ( new DateTime(2022, 3, 5), "e"),
    ( new DateTime(2022, 3, 9), "f")
  };

  var groupWeek = people.GroupBy(x => {
    var td = x.Date;
    while (td.DayOfWeek != System.DayOfWeek.Monday) td = td.AddDays(-1);
    return td;
  }).Select(x => new { WeekStart = x.Key, Count = x.Count(), dataList = x.ToList() });

  var myList = groupWeek.ToList();

the part that does what you mention, I think, is

 people.GroupBy(x => {
    var td = x.Date;
    while (td.DayOfWeek != System.DayOfWeek.Monday) td = td.AddDays(-1);
    return td;
  })

with your start day of week in there as appropriate to you

Cato
  • 3,652
  • 9
  • 12