CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitieswhere-clause
mwild
mwild

Reputation: 1655

LINQ Where clause inline for loop?

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.

Upvotes: 0

Views: 882

Answers (2)

Robert McKee
Robert McKee

Reputation: 21487

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();

Upvotes: 1

david.s
david.s

Reputation: 11403

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);

Upvotes: 4

Related Questions