A grouping of groups — GroupBy, then GroupBy again, in one statement

<Entity>.GroupBy(k1).Select(g => new A { … }).GroupBy(a => k2).Select(g => new B { … })

Group, then group the groups: each person's jobs per day, then per person how many days they worked and their busiest day. The database does both in one statement (the first grouping is a derived table the second groups), and only the final rows travel. It works over a `Join` too — answers joined to the jobs they are about, then grouped by team.

Summary#

Per person: on how many days they worked, and the most jobs they did on one day.

class PersonDay {
  public string Who;
  public DateTime Day;
  public int Jobs;
}

class PersonLoad {
  public string Who;
  public int Days;
  public int Busiest;
}

entity Job {
  [Required, MaxLength(60)] string Who;
  DateTime At;
}

List<PersonLoad> Loads() {
  var zone = TimeZoneInfo.FindSystemTimeZoneById("Europe/Stockholm");
  return Job.GroupBy(j => new() { Who = j.Who, Day = j.At.Bucket(TimeBucket.Day, zone) })
            .Select(g => new PersonDay { Who = g.Key.Who, Day = g.Key.Day, Jobs = g.Count() })
            .GroupBy(d => d.Who)
            .Select(g => new PersonLoad { Who = g.Key, Days = g.Count(), Busiest = g.Max(d => d.Jobs) })
            .OrderBy(p => p.Who)
            .ToList();
}

That is SELECT … FROM (SELECT who, day, COUNT(*) … GROUP BY who, day) t GROUP BY who: one statement, as many rows as there are people.

Signature#

Source.GroupBy(k1).Select(g => new Inner { … })      // the inner grouping, projected to a class
      .GroupBy(x => k2).Select(g => new Outer { … })  // grouped again: its element is the Inner row
// Source may be an entity, a Where over one, or a Join of two

Description#

Over a join#

A row that points at another by its id, not a reference, is joined and then grouped:

Answer.Join(Job, a => a.About, j => j.Id, (a, j) => new { Team = j.Team, Good = a.Good ? 1 : 0 })
      .GroupBy(x => x.Team)
      .Select(g => new TeamAnswers { Team = g.Key, Answered = g.Count(), Good = g.Sum(x => x.Good) })

As the reader may read#

The inner grouping reads the rows its reader may read, like any query: a mechanic who reads only their own jobs gets their own days and nothing of anybody else's.