LINQ for grouping, sum and copy to new DT - skip not working

[Juli (Juliane)]

Hi all,

I have a data table with 38 columns.
I need to group by a key, sum up all values in 2 columns, then add the rows to a new DT.

Basically, the output should be:

columns 1-31 (stay same), column 32 calculated, column 33 calculated, columns 34-38 (stay same)

I get all of it working except adding the columns 34-38.
Below the code, bold part is not working:

(From d In dt_390.AsEnumerable
Group d By k = d(“Line No.”).ToString.Trim Into grp = Group
Let cs1 = grp.Sum(Function(rc) Decimal.Parse(“0” & rc(“Receipt Quantity (Available)”).ToString))
Let cs2 = grp.Sum(Function(rc) Decimal.Parse(“0” & rc(“Receipt Amount (Available)”).ToString))
Let ra = grp.First().ItemArray.Take(31).Append(cs1).Append(cs2).Append(grp.First().ItemArray.Skip(33)).ToArray
Select dt_390Cleaned.Rows.Add(ra)).CopyToDataTable

Take(31) is working, appending calculated columns is working, but…

What am I doing wrong?

Thanks for your help.

[ashokkarale]

@Juli,

Try this LINQ:

(From d In dt_390.AsEnumerable()
                  Group d By k = d("Line No.").ToString.Trim() Into grp = Group
                  Let cs1 = grp.Sum(Function(rc) Decimal.Parse("0" & rc("Receipt Quantity (Available)").ToString))
                  Let cs2 = grp.Sum(Function(rc) Decimal.Parse("0" & rc("Receipt Amount (Available)").ToString))
                  Let ra = grp.First().ItemArray.Take(31).Append(cs1).Append(cs2).Concat(grp.First().ItemArray.Skip(33)).ToArray()
                  Select dt_390Cleaned.Rows.Add(ra)).CopyToDataTable

Thanks,
Ashok

[ppr (Peter Preuss)]

@Juli

grp.First().ItemArray.Take(31).Append(cs1).Append(cs2).Append(grp.First().ItemArray.Skip(33)).ToArray

give a try at

grp.First().ItemArray.Take(31).Append(cs1).Append(cs2).Append(grp.First().ItemArray.Skip(33)).ToArray

change to:

Let ra1 = grp.First().ItemArray.Take(31)
Let ra2 = new Object(){cs1,cs2}
Let ra = ra1.Concat(ra2).Concat(grp.First().ItemArray.Skip(33)).Cast(Of Object).toArray

[Juli (Juliane)]

Thanks a lot, that works perfectly

Could you please also explain why we need to use concat?
We can append stuff to an array, but we cannot append an array to stuff?

[ppr (Peter Preuss)]

@Juli

why we need to use concat?

have a look here:

[HowTo] LINQ - First Start # Concat | Append | Prepend Operator

This HowTo gives an introductory overview of the conjunction operators: Concat, Append, Prepend Introduction The conjunction operators are used to add the item(s) to a particular set of items. Concat, Append, Prepend Operator The Concat Operator combines a set of items with another set of items The Append Operator adds an item to a set of items on the end The Prepend Operator adds an item to a set of items on the begin Overview I – Input P – Processing O – Output {"A","B"} / {"C",…

And

[HowTo] LINQ - First Start # Skip | Take | SkipWhile | TakeWhile Operator

This HowTo gives an introductory overview of the partition Operators: Skip, Take, SkipWhile, TakeWhile Introduction The partition operators are used to fetch a particular subset from a set of items. The returned subset is formed by the contiguous items that are matching the provided condition. Skip / Take Operator The Skip Operator will omit the subsequent items from the given start for a given length and will return the remaining items The Take Operator will return the subsequent items fr…

you had seen System.Linq.Enumerable… in the output because of:

• skip wil return a collection the itemArray items which are grabbed after skipping the first 33 items
• with Append a single item / element will be added to the ra (row array)
• the caused toString() from the complex type will be used as column value

So we changed to:

.Concat(grp.First().ItemArray.Skip(33)

as the intention is about to combine multiple elements (returned from skip) with multiple elements defined by ra1/ra2…

[ashokkarale]

@Juli,

Sure,

Append : The Append method in LINQ is used to add a single element to the end of an existing collection. In the context of arrays, Append can be used to add a single element to the end of an array.

Concat: The Concat method in LINQ is used to concatenate two collections, meaning it combines all the elements of the first collection with all the elements of the second collection. Concat is necessary when you want to join an array (or any collection) with another array (or collection).

Thanks,
Ashok