How to group by list of values and then count the amount of entries per value-CodePudding

I have a list of objects. Each object contains a list of categories as a comma delimited string. I want to know how many objects i have for each category. For this i think i need to group by the categories and then count the entries - however i can't wrap my head around grouping by a list.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

class MyDto
{
    public string Name { get; set; }

    public List<string> Categories => CategoriesString
        .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries).ToList();
    public string CategoriesString { get; set; }

    public override string ToString()
    {
        return Name   ": "   CategoriesString;
    }
}

public class Program
{
    public static void Main()
    {
        var dtos = new MyDto[]
            {
                new MyDto() { Name = "Dto 1", CategoriesString = "DELIVERY"},
                new MyDto() { Name = "Dto 2", CategoriesString = "DELIVERY , DAMAGE"},
                new MyDto() { Name = "Dto 3", CategoriesString = "DAMAGE"},
                new MyDto() { Name = "Dto 4", CategoriesString = "DAMAGE , DELIVERY"},
                new MyDto() { Name = "Dto 5", CategoriesString = "DELIVERY"},
            };

        var res = dtos.GroupBy(c => c.Categories).Select(c => new { c.Key, amt = c.Count() });


        foreach (var c in res)
        {
            Console.WriteLine(c.Key   " - "   c.amt);
        }

        // Should return:
        //  DELIVERY - 4
        //  DAMAGE - 3
    }
}

https://dotnetfiddle.net/bowYb4

The sample above is just to demonstrate the issue and does not actually give the desired result(s). I'm using data objects coming from a database with EF core. I'm aware that what im trying to do won't translate to SQL - I'm doing this client-side and that is fine.

CodePudding user response：

One option is to use SelectMany to flatten categories and transform the dtos in key-value pairs (I use valu tuples to store them):

var res = dtos
    .SelectMany(dto => dto.Categories.Select(c => (Cat: c, dto.Name)))
    .GroupBy(c => c.Cat)
    .Select(c => new { c.Key, amt = c.Count() });

CodePudding user response：

An alternative solution could be the following:

var lookup = dtos
    .Select(c => c.Categories) //retrieve the already split values
    .SelectMany(c => c) //flatten the IEnumerable<List<string> to IEnumerable<string>
    .ToLookup(c => c, c => c); //group the same values

foreach (var item in lookup)
{
    Console.WriteLine($"{item.Key} - {item.Count()}");
}

The difference between GroupBy and ToLookup is that the former is executed in a deferred way, while the latter is executed immediately.