How to do group by query using Linq

Help Studio
, ,
1

Hello every one,

please check my excel sheetgroupby.xlsx (9.1 KB)

here I want to create a group by query against account no , net
after the group by I should have output of only three rows. please check the required output sheet2 of the excel

groupby.xlsx (10.2 KB)

please help me with your valueable suggestion.

2

Hi Supriya,

Not a super expert on Linq. Pretty sure there must be better ways to do it:

> dtExample.AsEnumerable().GroupBy(function(r) r("account no")).ToList().Select(function(i) i.First).cast(of DataRow).CopyToDataTable

Let me know if you find a better way to do it. I don’t know what’s your criteria to when it comes to the rest of the columns. As you can see on my version I’m picking the First row of each grouping. If you just want to know the unique account no there are better ways (although will not return the full table (only a column with the distinct account no):

dtExample.DefaultView.ToTable(true,{"account no"})

3

thanks for your reply. can you please check there is net column . I want to sum value of the net column against account no . after grouping it should be zero if sum is done .

4

for an initial introduction have a look here:

it seems to us that you want to reach something different:

  • groupby to account no
  • calculate the net values and have the result in output

May we ask you to resharpen the requirements and share the more detailed desctription with us. Especially a rule for regulating which other col values are to take (e.g. group member first …) would be recommended as well. Thanks

1 Like
5

Hi,

How about the following expression?

dt = dt.AsEnumerable.GroupBy(Function(r) r("account no").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.First().Item("Date"),g.First().Item("Voucher"),g.First().Item("Posting type"),g.First().Item("Ledger account"),g.First().Item("Description"),g.First().Item("Currency"),g.First().Item("Document Number"),g.First().Item("Debit"),g.First().Item("Credit"),g.Key,g.First().Item("b"),g.First().Item("c"),g.First().Item("d"),g.First().Item("e"),g.Sum(Function(r) Int32.Parse(r("net").ToString))},False)).CopyToDataTable()

Regards,

1 Like
6

Hi Supriya,

Please, check the guide made by @ppr → Really, Really good! You might find an answer to your problem there.