Counting category items for each date in dataset (C# with linq)-CodePudding

I am new to C# and linq and are trying to create a new dataset based on the old one. I have made a dummy dataset, however the real dataset contains a lot more fields which are not relevant to this case.

This is the similar the dataset with a few items to showcase what i want to accomplish:

entries : [
     {
       timestamp: "2021-06-01",
       category: "banana"
     },
     {
       timestamp: "2021-06-01",
       category: "apple"
     },
     {
       timestamp: "2021-08-14",
       category: "apple"
     },
    ]

This is how i want it:

entries : [
     {
       timestamp: "2021-06-01",
       fruitCount: [
          { category: "banana", count: 1 },
          { category: "apple", count: 1 }
       ]
     },
     {
       timestamp: "2021-08-14",
       fruitCount: [
          { category: "apple", count: 1 }
       ]
     }
    ]

How do i create a GroupBy statement which gets me to the desired list? As I said, I am very new to this and would appreciate some help in the right direction.

CodePudding user response：

You'll need to use GroupBy twice as you want to group by timestamp and within those timestamps again for the fruits. Here's an example:

//test data
var entries = new[] { new
{
    timestamp = "2021-06-01",
    category = "banana"
},
    new {
        timestamp = "2021-06-01",
        category = "apple"
    },
    new {
        timestamp = "2021-08-14",
        category = "apple"
    }
};

var result = entries
    //first we group by timestamps
    .GroupBy(x => x.timestamp) 
    //transform the data to the new format
    .Select(x => new
    {
        //the timestamp is the key we grouped by
        timestamp = x.Key,
        //build up the fruitCount object
        fruitCount = x
            //create subgroups for the fruits
            .GroupBy(y => y.category)
            //transform the fruit groups to the new format
            .Select(y => new
            {
                category = y.Key,
                //count together all fruits within the subgroup
                count = y.Count(),
            })
            .ToList(),
    })
    .ToList();