Linq, `Group By` Date, Month-CodePudding

I'm struggling with a LINQ Group By question. I have the following data model:

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

    public IEnumerable<Person> Persons { get; init; } = null!;
}

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

    public string Name { get; set; }

    public DateTime RegistrationDate { get; set; }

    public ulong FriendCount { get; set; }

    public ulong ScoreCount { get; set; }
}

Now, I'm trying to build a query, which returns me, for a single class, of collection of 12 elements (one for each month in the last 12 months), which shows me the sum of the "FriendCount" and "ScoreCount" of all persons in that month.

So, here's what I would like to achieve:

[
    { date: 01/01/2021, friendCount: 0, scoreCount: 50 },
    { date: 01/02/2021, friendCount: 15, scoreCount: 50 },
    ...
]

Is something possible with a LINQ (Group By) expression?

Here's what I achieve to filter the data, based on the date time.

    public async Task<Class?> GetData(int id)
    {
        DateTime currentDateTime = this.dateTimeProvider.Now;
        DateTime startDateTime = currentDateTime.RemoveMonths(12);

        School? result = await this.context.Class.Include(static class => class.Persons)
                                        .Where(class => class.Id == id)
                                        .Select(
                                             class => new Class
                                             {
                                                 Id = class.Id,
                                                 Persons = class.Persons
.Where(person => person.RegistrationDate >= startDateTime && person.RegistrationDate <= currentDateTime)

// Here I believe something like a group by clausule should be added.

CodePudding user response：

I am not quite sure how your desired output will fit into your Class/School classes, so I'll provide an answer that only addresses the Person objects. Hopefully you'll find useful.

For the .GroupBy() operation, you can create a new DateTime using the Year and Month values for the specific Person's RegistrationDate and use that as each grouping's Key.

I will assume that your output class may look as follows:

public class MonthlyStats
{
    public DateTime Date { get; set; }
    public ulong FriendCount { get; set; }
    public ulong ScoreCount { get; set; }
}

Then, the grouping and selection could be done as follows:

Person[] persons = { ... };

MonthlyStats[] monthlyStats = persons
    .GroupBy(p => new DateTime(p.RegistrationDate.Year, p.RegistrationDate.Month, 1),
        ( firstDayOfMonth, personsInMonth ) => new MonthlyStats
            {
                Date = firstDayOfMonth,
                FriendCount = (ulong)personsInMonth.Sum(p => (decimal)p.FriendCount),
                ScoreCount = (ulong)personsInMonth.Sum(p => (decimal)p.ScoreCount)
            })
    .OrderBy(stat => stat.Date)
    .ToArray();

If persons is defined as follows:

Person[] persons =
{
    new(1, "A", new DateTime(2022, 1, 4), 9, 23),
    new(2, "B", new DateTime(2021, 4, 8), 7, 29),
    new(3, "C", new DateTime(2021, 9, 11), 4, 13),
    new(4, "D", new DateTime(2021, 11, 1), 10, 14),
    new(5, "E", new DateTime(2021, 4, 17), 12, 17),
    new(6, "F", new DateTime(2022, 1, 27), 5, 11),
    new(7, "G", new DateTime(2021, 9, 2), 13, 31),
};

, the groupings will be the following:

01.01.2022
    (1, "A",  DateTime(2022, 1, 4), 9, 23)
    (6, "F",  DateTime(2022, 1, 27), 5, 11)
01.04.2021
    (2, "B",  DateTime(2021, 4, 8), 7, 29)
    (5, "E",  DateTime(2021, 4, 17), 12, 17)
01.09.2021
    (3, "C",  DateTime(2021, 9, 11), 4, 13)
    (7, "G",  DateTime(2021, 9, 2), 13, 31)
01.11.2021
    (4, "D",  DateTime(2021, 11, 1), 10, 14)

and the resulting MonthlyStats objects will be:

Date                FC      SC
---------------------------
01/04/2021      19      46
01/09/2021      17      44
01/11/2021      10      14
01/01/2022      14      34

Example fiddle here.

Update -- always return 12 months

To always generate a collection that covers each whole month of the previous year span, you could first generate the collection of "month dates", and then take advantage of Enumerable.GroupJoin():

var startDate = new DateTime(DateTime.Today.Year - 1, DateTime.Today.Month, 1);

DateTime[] firstDayOfMonths = Enumerable.Range(0, 12)
    .Select(i => new DateTime(startDate.AddMonths(i).Ticks))
    .ToArray();

MonthlyStats[] monthlyStats = firstDayOfMonths
    .GroupJoin(persons,
        date => date,
        person => new DateTime(person.RegistrationDate.Year, person.RegistrationDate.Month, 1),
        ( firstDayOfMonth, personsInMonth ) => new MonthlyStats
            {
                Date = firstDayOfMonth,
                FriendCount = (ulong)personsInMonth.Sum(p => (decimal)p.FriendCount),
                ScoreCount = (ulong)personsInMonth.Sum(p => (decimal)p.ScoreCount)
            })
    .ToArray();

Using the same persons input, the output will now look as follows:

01/04/2021      |      19      |      46
01/05/2021      |        0      |        0
01/06/2021      |        0      |        0
01/07/2021      |        0      |        0
01/08/2021      |        0      |        0
01/09/2021      |      17      |      44
01/10/2021      |        0      |        0
01/11/2021      |      10      |      14
01/12/2021      |        0      |        0
01/01/2022      |      14      |      34
01/02/2022      |        0      |        0
01/03/2022      |        0      |        0

(You may need to adjust the month part in the generation of startDate, or the start value in Enumerable.Range() to e.g. include the current month, if that is what you rather prefer to have.)

Example fiddle here.