How to do group by in C# list on two properties-CodePudding

how to write group by on two properties in c# list. I am having the list like this.

in the attached image, I am having data on one list and expect the result as shown in the image.

I tried the code something like this.

var result = EmpList
     .GroupBy(x => x.Dept && x.Area)
     .Select(c => new { Key = c.Key, total = c.Count() });

but not able to retrieve the data properly.

CodePudding user response：

You can group by an anonymous type. For example:

var result = EmpList.GroupBy(x => new { x.Dept, x.Area })
                    .Select(g => new { Key = g.Key, Total = g.Count() });

Within each result, the Key property will be the anonymous type, so you can use x.Key.Dept and x.Key.Area.

Note that you can do this with value tuples too, as suggested by snr. The choice of using query expressions or "method syntax" is entirely orthogonal to the choice of using anonymous types or value tuples. For example, using value tuples the above would be:

var result = EmpList.GroupBy(x => (x.Dept, x.Area))
                    .Select(g => (g.Key, Total: g.Count()));

CodePudding user response：

You can do it alternatively if you are using equal or higher C# 7.1

var result =
    from x in EmpList
    group x by (
        x.Area, x.Dept
    ) into c
    select (
    
       Total : c.Count(), Key : c.Key
    );

CodePudding user response：

If you want to list all possible combinations and get the count also for the combinations that don't have any match in the data list (as your screenshot suggests), you could extract all departments and areas first, then create all possible combinations (department, area), and then count the associated matches in your data list.

One way to go about it is:

// Extracting departments and areas from list
var departments = EmpList
    .Select(data => data.Department)
    .Distinct();

var areas = EmpList
    .Select(data => data.Area)
    .Distinct();

// Creating all possible combinations of department and area
IEnumerable<( Department Department, Area Area )> combinations = departments
    .Aggregate(Enumerable.Empty<( Department, Area )>(),
        ( combos, department ) => combos.Concat(areas.Select(area => ( department, area ))));

// Counting all matches found in the data list for each combination
IEnumerable<( string Combination, int Count )> combinationCount = combinations
    .GroupJoin(EmpList,
        combination => combination,
        data => ( data.Department, data.Area ),
        ( combination, dataMatches ) => ( $"{combination.Department}-{combination.Area}", dataMatches.Count() ));

Console.WriteLine(string.Join(Environment.NewLine, combinationCount));

Here, I am using named tuple variables to store data along the way. ( string Combination, int Count ) is a tuple with two data members: Combination (of type string) and Count (of type int).

.Aggregate() and .GroupJoin() are also used, to generate all possible department/area combinations, and to count the matches for each combination, respectively.

I am also assuming Department and Area are both enum types:

public enum Department
{
    Undefined,
    Audit,
    DEV,
    Finance,
    Marketing
}

public enum Area
{
    Undefined,
    ATNR,
    DSNR,
    NTGR
}

, but they could be plain strings as well. (If they are in fact strings, let me know, and I will adjust the code accordingly.)

If Department and Area really are enums, and you want all possible department/area combinations, even for departments/areas that are not referred to at all in your EmpList, you could benefit from populating departments and areas directly from the respective enum types, rather than traversing EmpList several times:

// Getting all existing departments and areas
var departments = Enum.GetValues<Department>().Where(d => d != default);
var areas = Enum.GetValues<Area>().Where(a => a != default);

(This way, though, the order will not reflect the order of appearance in your EmpList).

Example fiddle here.