C# - Lambda - grouping List data containing nested List objects

Question

I am trying to group a List, with inner Lists, to a smaller result using C# lambda but sadly without success.

I got the following query which returns 8 records:

+--------------+----------------+---------+-------------+-----------+-------------+----------+----------------------+----------+--------------+-----------+
| DepartmentId | DepartmentName | RoomId  |  RoomName   | ElementId | ElementName | ActionId |      ActionName      | TimeNorm | ElementCount | ProjectId |
+--------------+----------------+---------+-------------+-----------+-------------+----------+----------------------+----------+--------------+-----------+
|      1378195 | BG             | 1414848 | TestRuimte  |   1279320 | toilet      |  1279319 | kalk verwijderen     |       90 |            2 |   1377945 |
|      1378195 | BG             | 1414848 | TestRuimte  |   1279320 | toilet      |  1281322 | klamvochtig reinigen |       40 |            1 |   1377945 |
|      1378195 | BG             | 1414849 | TestRuimte2 |   1279320 | toilet      |  1279319 | kalk verwijderen     |       90 |            2 |   1377945 |
|      1378195 | BG             | 1414849 | TestRuimte2 |   1279320 | toilet      |  1281322 | klamvochtig reinigen |       40 |            1 |   1377945 |
|      1378195 | BG             | 1414850 | TestRuimte3 |   1279320 | toilet      |  1279319 | kalk verwijderen     |       40 |            1 |   1377945 |
|      1378195 | BG             | 1414850 | TestRuimte3 |   1279320 | toilet      |  1281322 | klamvochtig reinigen |      120 |            2 |   1377945 |
|      1378195 | BG             | 1414851 | TestRuimte4 |   1279320 | toilet      |  1279319 | kalk verwijderen     |       90 |            2 |   1377945 |
|      1378195 | BG             | 1414851 | TestRuimte4 |   1279320 | toilet      |  1281322 | klamvochtig reinigen |       40 |            1 |   1377945 |
+--------------+----------------+---------+-------------+-----------+-------------+----------+----------------------+----------+--------------+-----------+

The result is being stored in a List object:

List<dDepartment> test

The dDepartment class looks as following:

public class dDepartment
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public List<dRoom> Rooms { get; set; }
}

public class dRoom
{
    public int RoomId { get; set; }
    public string RoomName { get; set; }
    public List<dElement> Elements { get; set; }
}

public class dElement
{
    public int ElementId { get; set; }
    public string ElementName { get; set; }
    public List<dAction> Actions { get; set; }
}

public class dAction
{
    public int ActionId { get; set; }
    public string ActionName { get; set; }
    public int TimeNorm { get; set; }
    public int ElementCount { get; set; }

}

The list object returns 8 records with in that the nested class lists, which is good. However, I want to group this result on the DepartmentId, RoomdId, ElementId and ActionId. So the List should return 1 Department, with a list inside it containing 4 Rooms, with a list inside the rooms containing 1 element and a list inside the element containing 2 Actions.

So it should return something like this:

(Count 1) DepartmentId = 1378195, DepartmentName = BG, 
Rooms (count 4) = 
{[
  Room[0] = RoomdId = 1414848, RoomName = TestRuimte, Elements (count 1) = {[Element[0] = ElementId = 1279320, ElementName = toilet, Actions (count 2) = {[]...} ]}
  Room[1] = RoomdId = 1414849, RoomName = TestRuimte2, Elements (count 1) = {[Element[0] = ElementId = 1279320, ElementName = toilet, Actions (count 2) = {[]...} ]}
  Room[2] = RoomdId = 1414850, RoomName = TestRuimte3, Elements (count 1) = {[Element[0] = ElementId = 1279320, ElementName = toilet, Actions (count 2) = {[]...} ]}
  Room[3] = RoomdId = 1414851, RoomName = TestRuimte4, Elements (count 1) = {[Element[0] = ElementId = 1279320, ElementName = toilet, Actions (count 2) = {[]...} ]}
]}

I however can get the correct result after trying multiple Lambda groupings e.g.:

var grouped = test.GroupBy(g => new
{
    DepartmentId = g.DepartmentId,
    //Rooms = g.Rooms.GroupBy(gg => new
    //{
    //    gg.RoomId
    //}).ToList()
}).Select(s => new
{
    s.Key.DepartmentId,
    //Rooms = test.GroupBy(g => new 
    //{
    //    g.
    //))

    Rooms = test.Select(r => new { 
        r.Rooms
    }).GroupBy(g => g.Rooms.Select(t => t.RoomId)).ToList()
}).ToList();

Answer 1

I usually do it like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            List<dDepartment> depts = new List<dDepartment>();

            var results = depts
                .SelectMany(g => g.Rooms
                    .SelectMany(s => s.Elements.Select( e => new { 
                        deptId = g.DepartmentId, 
                        deptName = g.DepartmentName, 
                        roomId = s.RoomId, 
                        roomName = s.RoomName,
                        elementId = e.ElementId,
                        elementName = e.ElementName
                    })
                    )).GroupBy(g => new {g.deptId, g.roomId})
                    .ToList();

        }
    }
    public class dDepartment
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
        public List<dRoom> Rooms { get; set; }
    }

    public class dRoom
    {
        public int RoomId { get; set; }
        public string RoomName { get; set; }
        public List<dElement> Elements { get; set; }
    }

    public class dElement
    {
        public int ElementId { get; set; }
        public string ElementName { get; set; }
        public List<dAction> Actions { get; set; }
    }

    public class dAction
    {
        public int ActionId { get; set; }
        public string ActionName { get; set; }
        public int TimeNorm { get; set; }
        public int ElementCount { get; set; }

    }


}