Group inside group linq

Question

I have a datatable like this:

I want to group this table For FIELD A and FIELD B, and the third field of my group should be lists of FIELD C, but it must be grouped by ID field.

At the end, the result should be like this:

First Field | Second Field |  Third Field
------------+--------------+----------------
     5      |     XXXX     |(8) (2,6,3) (9)  
     5      |     KKKK     |(8,3)

The third field must be a list of lists.

How can i do this with LINQ?

I tried this so far:

        var trytogroup = (from p in datatable.AsEnumerable()
                                group p by new
                                {
                                    ID = p["Id"].ToLong(),
                                    FieldA = p["FieldA"].ToLong(),
                                    FieldB = p["FieldB"].ToString()
                                } into g
                                select new
                                {
                                    FirstField = g.Key.FieldA,
                                    SecondField = g.Key.FieldB,
                                    ThirdField = datatable.AsEnumerable().Where(p => p["FieldA"].ToLong() == g.Key.FieldA && p["FieldB"].ToString() == g.Key.FieldB).Select(p => p["FieldC"].ToLong()).GroupBy(x => x["Id"].ToLong()).Distinct().ToList()
                                });

Answer 1

What's wrong with your query:

• You don't need to group by three fields on first place. Grouping by ID should be done within group which you have by FieldA and FieldB
• When getting ThirdField you don't need to query datatable again - you already have all required data. You just need to add grouping by ID

Correct query:

 from r in datatable.AsEnumerable()
 group r by new {
   FieldA = r.Field<long>("FieldA"),
   FieldB = r.Field<string>("FieldB")
 } into g
 select new
 {
     First = g.Key.FieldA,
     Second = g.Key.FieldB,
     Third = g.GroupBy(r => r.Field<long>("ID"))
              .Select(idGroup => idGroup.Select(i => i.Field<long>("FieldC")).ToList())
 }