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()
                                });

I didnt get any errors, but i cant groupe the third field. Field A an Field B is ok, bu the third field must be a list of lists, i cant do that — Curious, Feb 14 '17 at 12:38

Sergey Berezovskiy · Accepted Answer · 2017-02-14T12:52:44.967

5

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())
 }

edited Feb 14 '17 at 12:52

answered Feb 14 '17 at 12:44

Sergey Berezovskiy

232,247
41
429
459

I'm not the OP, just curious also as a fresh mind to the whole `GroupBy` grouping – ColinM Feb 14 '17 at 12:50
@ColinM what do you mean by ThenBy? It's applicable to ordered sequences – Sergey Berezovskiy Feb 14 '17 at 12:56
1

Works like a charm :) Thank you @Sergey Berezovskiy – Curious Feb 14 '17 at 13:09
What I meant was syntax like `datatable.AsEnumerable().GroupBy().ThenBy()`, but on further inspection that wouldn't work as you have above since the second grouping is on items in the collection. – ColinM Feb 14 '17 at 16:04

score 0 · Answer 2 · answered Feb 14 '17 at 13:14

If you prefere lambdas, your query could look like:

dataSource
    .GroupBy(item => new { item.FieldA, item.FieldB })
    .Select(group => new
    {
        First = group.Key.FieldA,
        Second = group.Key.FieldB,
        Third = group.GroupBy(q => q.Id).Select(q => q.Select(e => e.FieldC).ToArray()).ToArray()
    }).ToArray();

score 0 · Answer 3 · answered Feb 14 '17 at 17:15

Just few small notes. .GroupBy uses Lookup to get the Groupings, so some overhead can be avoided by replacing .GroupBy( with .ToLookup( when deffered execution is not needed.

The elements in each Grouping are stored in array, so I don't see much use in converting them .ToList (but you can save a bit of space if you convert them .ToArray).

DataTable.AsEnumerable uses .Rows.Cast<TRow>(), but also seems to do some extra stuff when there is any DataView sorting or filtering that are usually not needed.

var groups = datatable.Rows.Cast<DataRow>()
    .ToLookup(r => Tuple.Create(
        r["FieldA"] as long?,
        r["FieldB"]?.ToString()
    ))
    .Select(g => Tuple.Create(
        g.Key.Item1, 
        g.Key.Item2,
        g.ToLookup(r => r["ID"] as long?, r => r["FieldC"] as long?)
    )).ToList();

As usual, premature optimization is the root of all evil but I thought the information might be useful.

Group inside group linq

3 Answers3