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!
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:
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
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.