LINQ Where clause inline for loop?

Question

I have database values called start and length.

start is the start time of a booking (1->09:00, 2->10:00 etc) and length is the length in hours.

i then have an array of start times and end times. I want to be able to check whether each start and end pair are already booked. I so far have it figured that if the start times are the same, it is booked, or if the end times are the same, it is also booked. But if the start and end time are inbetween the comparison times, it will return not booked, which is false.

I am trying to write a LINQ query to test whether a booking is already in the database. So far I have

var proposedRequest = db.requests.Include(r => r.rooms);
proposedRequest = proposedRequest.Where(r => r.booked.Equals(1));
proposedRequest = proposedRequest.Where(r => r.roundID.Equals(roundID));
proposedRequest = proposedRequest.Where(r => r.day.Equals(day));

int[] startTimes = new int[length];
int[] endTimes = new int[length];
for(var q=0;q<length;q++)
{
    startTimes[q] = time + q;
    endTimes[q] = time + q + 1;
}
proposedRequest = proposedRequest.Where(s => startTimes.Contains(s.start) || endTimes.Contains(s.start+s.length));

Now, this only works for if the new booking starts at the same time as the booking already in the DB, or if it ends at the same time. This doesn't look at the following case

there is a records in the db where start -> 2 and length ->3.

so this booking runs from 10:00->13:00.

but say I am checking this against an entry that starts at 11:00 and ends at 12. It would not come back as booked already because the start and end times do not match.

What is the best way to solve this?

the only way i could see fit is to loop through my startTime and endTime arrays and have another clause for each pair that would produce something like the following:

.Where((s => s.startTime<startTime[i] && (s.startTime + s.Length) > endTime[i]) || (s => s.startTime<startTime[i+1] && (s.startTime + s.Length) > endTime[i+1]))

but i dont think this is possible.

This should help: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap but you've made it much more difficult on yourself as you didn't store the datetimes in a datetime field. — Robert McKee, Apr 28 '15 at 16:19
@RobertMcKee they booking system im building is strange and works on weeks and rounds. thats why i cannot store date time fields, and why your link cannot apply to my problem. I simple have days represented by integers. — mwild, Apr 28 '15 at 16:22
The link still applies, and david.s did the conversion for you below. — Robert McKee, Apr 28 '15 at 16:27
The start/end times are always in the same day and same round, or do those also change for each start/end? — Robert McKee, Apr 28 '15 at 16:36

score 4 · Accepted Answer · edited May 23 '17 at 12:14

4

Based on this answer, two ranges overlap if (StartA <= EndB) and (EndA >= StartB)

In your case:

StartA = s.start
EndA = s.start + s.length
StartB = time
EndB = time + length

So your last condition should be like this:

proposedRequest = proposedRequest.Where(s => s.start <= time + length &&
                                             s.start + s.length >= time);

edited May 23 '17 at 12:14

Community

1
1

answered Apr 28 '15 at 16:25

david.s

11,283
6
50
82

my question may have been a little bit vague. I have added some more detail above. I have an array of start and end times. – mwild Apr 28 '15 at 16:29
@user2976358 So you have and array of time (like `int[] times`) and an array of length (like `int[] lengths`)? – david.s Apr 28 '15 at 16:32
my for loop in the code above splits the booking up into one hour intervals, so starts at 2 ends at 3. The endTimes array is irrelevant really as it is just the startTime value +1 – mwild Apr 28 '15 at 16:34
@user2976358 But why do you need to split the booking into one hour intervals? To check if the new booking (given by time and length) overlaps with existing ones, the answer above is enough. – david.s Apr 28 '15 at 16:38

Robert McKee · Answer 2 · 2015-04-28T16:58:26.893

1

This will return objects that have your StartTime, EndTime, and a boolean that signifies if it booked already.

var proposedRequest = db.requests
    .Include(r => r.rooms)
    .Where(r => r.booked.Equals(1))
    .Where(r => r.roundID.Equals(roundID))
    .Where(r => r.day.Equals(day))
    .ToList();

//int[] startTimes = new int[length];
//int[] endTimes = new int[length];
//for(var q=0;q<length;q++)
//{
//    startTimes[q] = time + q;
//    endTimes[q] = time + q + 1;
//}
var times=Enumerable
   .Range(time,length)
   .Select(r=>
       new {
           StartTime=r,
           EndTime=r+1,
           Booked=proposedRequest.Any(pr=>pr.StartTime<=r && pr.StartTime+pr.Length>r)
           }).ToList();

edited Apr 28 '15 at 16:58

answered Apr 28 '15 at 16:53

Robert McKee

21,305
1
43
57

Although the names seem odd. `proposedRequest` isn't proposed is it? Those are the ones already in the database. Shouldn't it be `alreadyBooked` or something similar? – Robert McKee Apr 28 '15 at 17:02
proposedRequest is a request for a booking that hasnt already been booked. The purpose of this function is to get all the rooms (associated with each booking) so i can remove them from the selectable rooms. – mwild Apr 28 '15 at 17:07

LINQ Where clause inline for loop?

2 Answers2