Help Needed: LINQ Query Not perform as expected

Help Studio
, , ,
1

Hello everyone,

Could anyone help me figure out the problem with my LINQ query? Here is my current query:

(From row In dt1.AsEnumerable()
Group row By colA = row(“columnA”).ToString, colB = row(“columnB”).ToString Into grp = Group
Let SumColC = grp.Sum(Function(r) Convert.ToDouble(r(“columnC”).ToString))
Let SumColD = grp.Sum(Function(r) Convert.ToInt32(r(“columnD”).ToString))
From g In grp
Select dt2.Rows.Add({g(“columnE”), colA, SumColD, SumColC, colB})
).CopyToDataTable()

The goal is to group the data by columnA and columnB, then sum the values in columnC and columnD for each group. However, the result is not as expected. The query is returning a random row from each group without performing the sum operation, and I can’t figure out why.

I’d be grateful if someone could correct this query or provide guidance on how to achieve the desired result.

Thank you!

2

unfortunately we cannot refer to sample data and have to guess.
We assume that:

  • shop_code is the same within the group
  • an aggregated row is expected

Give try at:

( From row In dt1.AsEnumerable()
Group row By shopName = row("shop_name").ToString.Trim, Seg = row("Seg").ToString.Trim Into grp = Group
Let NetAmountSum = grp.Sum(Function(r) Convert.ToDouble(r("net_amount").ToString))
Let NetQuantitySum = grp.Sum(Function(r) Convert.ToInt32(r("net_quantity").ToString))
Let ra = new Object(){grp.First()("shop_code"), shopName, NetQuantitySum, NetAmountSum, Seg}
Select r = dt2.Rows.Add(ra)).CopyToDataTable()
3

Hi @Islam_ISmail

Give a try with the below expression,

- Assign -> dt2 = (From row In dt1.AsEnumerable()
           Group row By shopName = row("shop_name").ToString, Seg = row("Seg").ToString Into grp = Group
           Let NetAmountSum = grp.Sum(Function(r) Convert.ToDouble(r("net_amount").ToString))
           Let NetQuantitySum = grp.Sum(Function(r) Convert.ToInt32(r("net_quantity").ToString))
           Select dt2.Rows.Add(grp.First()("shop_code"), shopName, NetQuantitySum, NetAmountSum, Seg)
          ).CopyToDataTable()

Hope it helps!!