Add columns to datatable with LINQ

Im using @ppr guide [HowTo] Overview on different options for grouping data and processing the groups

The original looks like this:

(From d In dtData.AsEnumerable
Group d By k=d("RegionCode").toString.Trim Into grp=Group 
Let cs = grp.Sum(Function (rc) CInt( "0" & rc("CaseCount").toString))
Let cn = String.Join(";", grp.Select(Function (rn) rn("CaseName").toString).toArray)
Let ra = New Object(){k, cs, cn}
Select dtReport2.Rows.Add(ra)).CopyToDataTable

Line 3:

Let cs = grp.Sum(Function (rc) CInt( "0" & rc("CaseCount").toString))
The “0” seems not do anything and I removed it.
If it is changed to “1” or “2” it adds the number in some way, dont really know how it works.


Im trying to modify it so that it works for me.

(From d In dt_cases.AsEnumerable
Group d By k=d("column1").toString.Trim Into grp=Group 
Let cs = grp.Sum(Function (rc) CDbl( rc("Column2Num").toString))
Let ut = 
Let ra = New Object(){k, cs, ut}
Select dt_mergedDuplicates2.Rows.Add(ra)).CopyToDataTable

So firstly I have modified line 3 to convert to double and I wonder how can I make it round it to 2 decimals directly?

Secondly my dt_cases contains 5 columns how can I add the columns to dt_mergedDuplicates2

I have tried to experiment at Line 4 without success.

Can someone help me finish or add to this LINQ statement so that it adds Column3, Column4 and Column5 at Line 5?

it a technique to handle empty values, that why it is unobstrusive

just share with us sample input, expected output and some descritpions. Thanks

Okay thank you.

Input:
image

expected output:
image

So add the values in “Column2Num” where the rows in “Column1” are identical and then add the the rows from column2,3 and 4.

we would assume that it is sufficient to group the data by one key col: Column1 and

dt_mergedDuplicates2

is prepared e.g. by dt_cases.clone

So give try at:
dt_mergedDuplicates2 =

(From d In dt_cases.AsEnumerable
Group d By k=d("column1").toString.Trim Into grp=Group 
Let cs = grp.Sum(Function (rc) CDbl( rc("Column2Num").toString))
Let ra = New Object(){k, cs, grp.First()("Column3"),grp.First()("Column4"),grp.First()("Column5")}
Select dt_mergedDuplicates2.Rows.Add(ra)).CopyToDataTable

we just grab the value from the first group member

1 Like

grafik

CDbl("11.5").toString("F2")
 "11.50"
1 Like

Thank you yet again, you are really smart!

What is the difference between grp.First()(“name”) and grp.First()(“name”), what does the “()” do?

it is a call to the First method and () is representing the call without any passed method argument

1 Like

Okay, thank you for the explanation.

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