Remove group by from LINQ QUERY

Question

I want to remove GROUP BY clause from the below code. How could i do this.

var newDt = dt.AsEnumerable()
              .GroupBy(r => r.Field<string>("BATCH NUM"))
              .Select(g =>
              {
                  var row = dt.NewRow();

                  row["BATCH NUM"] = g.Key;
                  row["QTY"] = g.Sum(r => Convert.ToInt32(r.Field<string>("QTY")) + Convert.ToInt32(r.Field<string>("BONUS")));
                  row["PROD ID"] = String.Join(",", g.Select(x => x.Field<string>("PROD ID")).Distinct());
                  // row["PROD ID"] = g.Select(r => r.Field<string>("PROD ID"));
                  return row;
              }).CopyToDataTable();

It creates problem, when we have two Different "PROD ID" but with same "BATCH NUM"then it combines "PROD ID" into a single field it treats both PROD ID with same batch. Here is a Image you can see

Result which i want is like below.

When i remove group by clause it gves me cannot resolve reference error. — mrjahanzaib niazi, Jul 20 '18 at 12:44
Put the marker next to `.GroupBy`, press delete key. Done - removed from code. — Renatas M., Jul 20 '18 at 12:45
Basically i dont want to group rows, and i want to copy values of only those 3 columns which you can see. — mrjahanzaib niazi, Jul 20 '18 at 12:47
The this is different question and the answer can be found here: [Copying data of only few columns to one more data table](https://stackoverflow.com/questions/6183621/copying-data-of-only-few-columns-to-one-more-data-table/6183839#6183839) — Renatas M., Jul 20 '18 at 12:48
i have edited my question and aded more details with image please have a look on it. — mrjahanzaib niazi, Jul 21 '18 at 02:12

Hamlet Leon · Accepted Answer · 2018-07-20T17:32:22.613

1

If you try to delete the Group By it give you an error becouse you do a Sum in the select. The object that you "GROUP" is not longer a Set of TKey, TElement when you delete it.

If you want to do a Sum of "QTY" of each "PROD ID" and collect the "BATCH NUM", that is the best way.

There is another way using a ToLookup but: lookup vs. groupby

Details asked in comments:

With the lookup you can do the same thing...

var newDt = dt.AsEnumerable()
.ToLookup(r => r.Field<string>("PROD ID"))
.Select(g =>
{
     var row = dt.NewRow();
     row["PROD ID"] = g.Key;
     row["QTY"] = g.Sum(r => Convert.ToInt32(r.Field<string>("QTY")) + Convert.ToInt32(r.Field<string>("BONUS")));
     row["BATCH NUM"] = String.Join(",", g.Select(x => x.Field<string>("BATCH NUM")));
     return row;
}).CopyToDataTable();

Is quite the same thing, the difference is in the post that i linked below.

edited Jul 20 '18 at 17:32

answered Jul 20 '18 at 12:50

Hamlet Leon

427
3
9

Yes you are right error is in Sum. So how to apply lookup in it. ? – mrjahanzaib niazi Jul 20 '18 at 12:53
No, it is not working it is also grouping. Lets say when we have two different BATCH NUM but same product id's it sums their QTY and add "BATCH NUM" in a single field. – mrjahanzaib niazi Jul 21 '18 at 01:47
i have edited my question and added more details with image please have a look on it – mrjahanzaib niazi Jul 21 '18 at 02:11
Ahhh. Becouse you need to group the Batch number too. Like -> GroupBy(r => { r.Field("BATCH NUM"), r.Field("BATCH NUM")}) – Hamlet Leon Jul 21 '18 at 13:59

Remove group by from LINQ QUERY

1 Answers1