LINQ - Select rows based on whether their sum meets a condition

Hi everyone,

I’ve run into a problem as I cannot get a proper working linq statement her.

Suppose I have a DataTable with x rows and I have to sort based on the sum of the column Quantity. Then I have a condition Requested Quantity = 20. I need to find the rows equal to the exact sum of RequestedQuantity only where the combination of 3 rows is equal to it.

Bin Quantity
1 10
2 5
3 5
4 10
5 15

I can’t seem to figure out the proper linq syntax to get this to work. My starting point is this:
(From row In StorageBins.AsEnumerable.GroupBy( _
Convert.ToDouble(Function (x) x(“Quantity”), cultureInfo)).Sum( _
Function (y) Convert.ToDouble(y(“Quantity”), cultureInfo) = Double.Parse(RequestedQuantity,cultureInfo)))

Initially I am just trying it to do so I get any rows that is equal to my condition, without the exact 3 rows. However that is my end-goal.

I’m not an expert in Linq unfortunately. I hope some of you might be!

Thanks

1 Like

@sonnymeyer

the sum of all quantities will allways the same one sum.
So Grouping will not lead to a set of combinations between rows. I didnt check in detail but I would expect 1 group with an amount of 45 with 5 group members

What I understand from your requirements (I will it elaborate on a set combinations of 2)

  • combine each row with an additona onel to a pair like 1-10 with 1-10, 2-5,3-5… etc
    • So we got a carteasian product n x n rows
  • sum up the Quantity of this pairs
  • filter the sum on 20
  • dump out which groups it will be

In LINQ:
(From a In dtData.AsEnumerable
From b In dtData.AsEnumerable
Where Not a(0).ToString.equals(b(0).toString)
Let s = Convert.ToDouble(a(“Quantity”), cultureInfo) + Convert.ToDouble(b(“Quantity”), cultureInfo)
Where s=dblSum
Select New DataRow(){a,b}).toList

  • do the cartisan product with the two froms
  • filter out where a row is compaired with itself as we do want clean combinations
  • sum up
  • filter on the sum
    return a list of Datarow Array (containg in each element the pairs with the sum of 20

Output:
grafik

you will get a complet set in which also the mirrowed pairs are contained: 3,5-5,15 vs 5,15-3,5

To bring it on a next level we do need more dynamics in the LINQ as we dont want to hardcoded too much.

I hope this helped for start. Find Starter Help here:
sonnymeyer.xaml (9.4 KB)

Have a look on hows I handled the conversion 20 into double. Try to do as less as possible in LINQ when it can be done outside the do it there. This makes Bug analysis easier

5 Likes

Thanks ppr. It looks great, I’ll take a look at it. The main reason for taking the linq approach was actually to a too complex workflow with nested if’s. But I do understand that it is easier to debug outside.

Actually the mirrowed pairs currently does not pose an issue, as we just need one combination of the sums to fullfil the order, we do not need more.

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