0

I have mssql table as the given image. I want to know how can I return rows from the table tblSpace that the sum of "Available" rows is greater than or equal to given value using Linq. Also there is a order of filling from 1 to 5. Thanks

Table capture and data:> tblSpace

What I've tried so far

1). If I pass 9,000 as value it should return only Vienguard row (9,000 < 10,000)

2). If I pass 23,000 as value it should return both Vienguard and Halestorm rows (23,000 < 10,000+15,000)

3). if I pass 35,000 as value it should return Vienguard, Halestorm and Quarts rows (35,000 < 10,000+15,000+20,000)

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Alexander Raymak
  • 49
  • 6
  • 1
    Okay, what have you tried so far? – aca Jul 06 '22 at 12:12
  • 1
    did you tried by yourself something which did not worked ? – CodingMytra Jul 06 '22 at 12:14
  • I do not understand your criteria. You pass 9000 and Vienguard is higher so it return Vienguard. But afterwards you pass 23000 and Vienguard and Halestorm are lower so you return those? And what abouth the 20.000 value "Quartz"? – Mernayi Jul 06 '22 at 12:19
  • check aggregate function https://stackoverflow.com/questions/7105505/linq-aggregate-algorithm-explained – Power Mouse Jul 06 '22 at 12:41
  • also aggregate datarows might be useful for you https://stackoverflow.com/questions/20566540/aggregate-rows-in-a-datatable-using-linq – Power Mouse Jul 06 '22 at 12:52
  • @aca I could only do upto ordering the those 5 rows. – Alexander Raymak Jul 06 '22 at 13:00
  • Can you show us your code. It is hard for people to help, if they don't see what have you tried so far. – aca Jul 06 '22 at 13:00
  • @CodingMytra I have spended so much time for this one and yet don't know how to do that – Alexander Raymak Jul 06 '22 at 13:01
  • @Mernayi I just only need to pass a number to the linq query and It should return the list of tblSpace rows which will have the sum of 'Available' column is equal or greater than the given value. – Alexander Raymak Jul 06 '22 at 13:04
  • @Mernayi There is Fill order column. Rows must be ordered ascending by Fill order column. The value I will apply to the linq should return the ordered rows that makes the sum of 'Available' column greater than or equal to given value. – Alexander Raymak Jul 06 '22 at 13:08
  • 1
    @aca I've updated the question with what I've tried so far. – Alexander Raymak Jul 06 '22 at 13:24

1 Answers1

1

Alright so I think you need something like this?

var sum = 0;
var result = tblSpace.OrderBy(x => x.Available).TakeWhile(x => (sum += x.Available) <= value).ToList();

EDIT In case you want an extra value if it exceeds you add

var result = tblSpace.OrderBy(x => x.Available).TakeWhile(x => (sum += x.Available) - x.Available < value).ToList();
Mernayi
  • 237
  • 3
  • 12