CODEWITHSUNDEEP
vb.netlinq
Sonny M
Sonny M

Reputation: 53

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

I’ve run into a problem, as I cannot get a proper working LINQ statement here.

Suppose I have a DataTable with x rows and I have to sort based on the sum of the Quantity column. Then I have a condition Requested Quantity = 20. I need to find the rows equal to the exact sum of RequestedQuantity, but 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 to get any rows that are equal to my condition. My end-goal, however, is getting any three rows that exactly sum up to my Requested quantity.

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

Upvotes: 0

Views: 213

Answers (1)

Jonathan
Jonathan

Reputation: 5018

Maybe I'm missing something, but this actually seems like a pretty complicated problem. Pick any 3 records, but only 3, that add up to exactly 20. How many rows are there in the database? Because this could get to be quite a few potential combinations pretty quickly. And what do you do after you get the 3? Do you have to go back through recursively and group up the other records as well? Or you just need the first set of 3 that add up to 20?

Assuming you just need the first 3, I would do something like this:

  • Get the first record that is less that 20. Remove it from your input list and put it into your target set.
  • Then get the first record that is less than 20 minus the first value. ie if the first value was a '5', get records that are less than 15 (20 minus 5). This ensures you 'leave room' for the third value. Remove it from the original list and into your target set.
  • Then get the first record that is exactly 20 minus number one minus number two. Remove it from the input list and into the target set.

Now you would have to do this in iterators. If there is no value that meets the third criterion, release the third value from your target set and put it back in your input list. Then go back to step 2 and pick the next record that matches step 2 (and ideally that is not equal to the previous value). And if you exhaust all of the iterations through step 2, go back to step one and pick the next value there, and start the whole thing over again...

Unless I'm misunderstanding your requirement...

Upvotes: 5

Related Questions