Group list of objects based on timestamp

user_1027620 0
Viewed 62

Having the following interface:

public class foo
{
    [Key]
    public int Id { get; set; }}
    public string Notes { get; set; }
    public DateTime? Timestamp { get; set; }
}

Using data context such as: await context.foos.ToListAsync() returns a full list of entries. The goal I want to achieve is a restructured JSON object from the same response as below:

[
    {
        year: YYYY,
        count: 0, // <- total `foos` in year
        months: [
            {
                month: 'January',
                count: 0, // <- total `foos` in month
                days: [
                    {
                        day: 0,
                        count: 0, // <- total `foos` in day
                        foos: [
                            {
                                Id: 0,
                                Notes: '...',
                                Timestamp: '...'
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

I was able to achieve this with multiple nested for loops. However, this seems like a very bad approach since nested loops increase process time exponentially.

Is there a possible way to achieve this with LINQ? Or maybe a more efficient solution?

asp.net-core-webapi linq c#
2 Answers

Algorithmically, and this is irrelevant to the language you're using, you can do something along the lines of this pseudo-code:

for (item in listOfItems) {
    year = getYearFromItem(item)
    month = getMonthFromItem(item)
    day = gteDayFromItem(item)

    updateYears(data, year)
    updateMonth(data, month)
    updateDay(data, day)
}

You'd be going over the data exactly once and creating year/month/day sub ojects as you go.

user_30451710

Is there a possible way to achieve this with LINQ?

The records can be grouped using the appropriate key

//entries => List<foo>

var result = entries
    .GroupBy(_ => _.Timestamp?.Year) //Group all foo by year
    .Select(years => new { 
        year = years.Key, //YYYY
        count = years.Count(), // total `foos` in year
        months = years
            .GroupBy(_ => _.Timestamp?.Month) //Group all foo in this group by month
            .Select(months => new {
                month = months.First().Timestamp?.ToString("MMMM"), //January
                count = months.Count(),  // total `foos` in month
                days = months
                    .GroupBy(_ => _.Timestamp?.Day) //group all foo in this group by day
                    .Select(days => new {
                        day = days.Key,
                        count = days.Count(), // total `foos` in day
                        foos = days.ToArray()
                    }).ToArray()
            }).ToArray()
    });

Reference Linq: GroupBy

Reference Group query results

user_52334100
Related