LINQ - Split results in months-CodePudding

I'm using Entity Framework, and I have the following classes:

public sealed class Class
{
    [Key]
    public int Id { get; init; }

    public IEnumerable<Student> Students { get; init; } = null!;
}

public sealed class Student
{
    [Key]
    public int Id { get; init; }

    public IEnumerable<Point> Points { get; init; } = null!;

public sealed class Point
{
    [Key]
    public int Id { get; init; }

    public DateTime DateTime { get; set; }

    public int MathPoints { get; set; }

    public int LanguagePoints { get; set; }

Now, I need to write a query, which selects a single class, but for that class, create a collection of 12 elements (one for each month), where each collection contains a limited set of persons (for example 2), and their scores.

The output would be something like:

[
  {
    "date": "2022/01/01",
    "totalPoints": 195, // This is the sum of ALL points of ALL students in this month.
    "students": [
      {
        "name": "student 1",
        "points": 100 // This is a sum of the `MathPoints` and the `LanguagePoints`.
      },
      {
        "name": "student 2",
        "points": 75 // This is a sum of the `MathPoints` and the `LanguagePoints`.
      },
      {
        "name": "others",
        "points": 20 // This is a sum of the `MathPoints` and the `LanguagePoints`.
      }
    ]
  },
  // .. Repeated here for all the other months in the year.
]

I know that this could be achieved doing quite some linq queries, but I want it to be as optimal as possible.

Who can provide me some information on how to tackle this problem?

Edit:

An IEnumerable should be returned, which contains the following fields:

public sealed class Report
{
    public DateTime Date { get; set; }

    public int TotalPoints { get; set; }

    public IEnumerable<StudentReport> StudentReports { get; set; }
}

public sealed class StudentReport
{
    public string Name { get; set; }

    public int Points { get; set; }
}

CodePudding user response：

You have to split such query by two parts - loading minimal required data and grouping on the client side. Grouping on the client side is needed because your result contains grouped details.

// input parameters
var classId = ...;
var year = 2022;

// parameters for range filter 
var startDate = new DateTime(year, 1, 1);
var endDate = startDate.AddYear(1);

var query = 
    from c in context.Class
    from s in c.Students
    from p in s.Points
    where c.Id == classId &&
        p.DateTime >= startDate && p.DateTime < endDate
    group p by new { p.DateTime.Year, p.DateTime.Month, s.Id, s.Name } into g
    select new 
    {
        Year = g.Key.Year,
        Month = g.Key.Month,
        Points = g.Sum(x => x.MathPoints   x.LanguagePoints),
        Name = g.Key.Name
    };

// materialize items, grouping should be provided on the client side
var rawItems = await query.ToListAsync();

var resultQuery = 
    from r in rawItems
    group r by new { r.Year, r.Month } into g
    select new Report
    {
        Date = new DateTime(g.Key.Year, g.Key.Month, 1),
        TotalPoints = g.Sum(x => x.Points),
        StudentReports = g.Select(x => new StudentReport
        {
            Name = x.Name,
            Points = x.Points
        })
        .ToList()
    }

var result = resultQuery.ToList();