Using Group by for more than two columns using Linq

Hi all,

Can anyone please help me how to use group by function in Linq query for more than two columns.
@ppri1
o1

I require output as above

@Gattu_Monika

Adding by , syntax within the LINQ Query Syntax

up to three columns we do add it to the group by clause like:

(From d in dtData.AsEnumerable
Group d by k1=d(0)… , k2=d(3)… , d(5)… into grp=Group
[the other part of the implementation]

we can also use more, but the readability goes down

in case of more columns we can check for different options

The string Concat trick:

arrColSet = {"C1", "C5", "C7"}
(From d in dtData.AsEnumerable
Let x = String.Join("_",arrColSet.Select(Function (x) d(x).toString))
Group d by k=x into grp=Group
[the other part of the implementation]

The string Concat trick with take/skip:

(From d in dtData.AsEnumerable
Let x = String.Join("_", d.itemArray.Skip(2)Take(5).Select(Function (x) x.toString)
Group d by k=x into grp=Group
[the other part of the implementation]

As a variation of this, we can also set up a helper data column and can shift back to one column for the grouping referring to the helper column

the main focus is to set on the key k=XXX, because k has to be of a datatype that will support comparisation by default

1 Like

@ppr Thanks for your reply…I will try like mentioned above

@Gattu_Monika
with the updated samples from above give a try on

prepare target datatable with build datatable - dtTarget
Cols: Id, Name, City, Count, Sum

Assign activity:
LHS: dtTarget
RHS:

(From d in dtData.asEnumerable
Group d by k1=d(0).toString, k2=d(1).toString, k3=d(2).toString
let sm = grp.Sum(Function (x) CInt(x("Value").toString.Trim))
let ra = new Object({k1,k2,k3, grp.Count, sm}
Select r = dtTarget.Rows.add(ra)).CopyToDataTable

feel free to shift to col names instead of index and to trim the keys

1 Like

@ppr Thanks It worked well…as I need to group only three columns I used first method…Thanks for your help

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.