Check schedule overlap using LINQ

Question

I Have a table like below

SL  Classroom  Weekday  StartTime  EndTime
--  ---------  -------  ---------  --------  
1   ADMN-100   Friday   01:00:00   02:00:00
2   ADMN-100   Friday   02:00:00   03:00:00
3   ADMN-100   Monday   01:00:00   02:00:00
4   LAB-501    Monday   01:00:00   02:00:00

Using LINQ, how can I check if a time overlap is occurring?

I failed doing below,

bool check = true;
check = db.Classrooms.Any(x => x.Classroom.Equals(roomNo)
                                   && x.Weekday.Equals(weekDay)
                                   && x.StartTime < startsAt
                                   && x.EndTime > startsAt);

You're kind of missing some detail here, but do you have the sign flipped? Shouldn't you be checking is the start time is *earlier* than startsAt? — Casey, May 06 '16 at 13:49
I think should be `startsAt > x.StartTime && x.EndTime > startsAt` — Arturo Menchaca, May 06 '16 at 13:53
Are StartTime/EndTime string objects or some other object type? If they are DateTime objects, what are their year/month/day components? — Frank Bryce, May 06 '16 at 13:56
The way to check for overlap is `start1 < end2 && start2 < end1` — juharr, May 06 '16 at 13:59
Casey, Arturo Menchaca and juharr, you guys are right. Changed the question as well as in my code. But not solved yet. — Tareq, May 06 '16 at 14:21
@JohnCarpenter, Time properties are TimeSpan type and in Data Annotation, [DataType(DataType.Time)] is used. So, there is no other components except time. — Tareq, May 06 '16 at 14:23
If that hasn't fixed it then you need to post more of the code because this part doesn't seem to be the issue. — Casey, May 06 '16 at 14:56
Are you trying to get compare a single datetime range again db Classroom to prevent overlap inserts? Or every classrom to each other to make sure not overlap are on db? — Juan Carlos Oropeza, May 06 '16 at 18:14

Frank Bryce · Answer 1 · 2016-05-06T14:42:07.403

3

Your code is almost correct, but change x.StartTime < startsAt to x.StartTime < endsAt. Schedule class is below for completeness.

class Schedule
{
    public string Classroom { get; set; }
    public string Weekday { get; set; }
    public TimeSpan StartTime { get; set; }
    public TimeSpan EndTime { get; set; }

    public bool OverlapsWith(Schedule schedule)
    {
        return schedule.StartTime < EndTime && 
               schedule.EndTime > StartTime && 
               schedule.Weekday == Weekday && 
               schedule.Classroom == Classroom;
    }
}

A quick note - use TimeSpan so that a DateTime object will not mess up you logic if the month, year, or day is off. What you really want is a time of day, and TimeSpan offers "Hours", "Minutes" and "Seconds" which is what you really want. The comparisons will work with TimeSpan objects. I'm not sure how this will work with EF, though.

If you need a string to TimeSpan conversion, try TimeSpan.Parse. It should work for the format that you presented in your question.

The OverlapsWith method probably won't be inside of Schedule, but it's there just to show you that your code is basically the same.

edited May 06 '16 at 14:42

answered May 06 '16 at 14:11

Frank Bryce

8,076
4
38
56

The problem with `TimeSpan` is that it gives an elapsed time, not a time of day. – Matt Rowland May 06 '16 at 14:19
1

"Elapsed time since the day started" is the same thing as the "time of day". Interpreting a `TimeSpan` this way is the closest way of doing this that I know of. Check out the [link in the question](https://stackoverflow.com/questions/3689027/whats-best-practice-to-represent-a-time-object-in-c) for another SO post on the topic. – Frank Bryce May 06 '16 at 14:20
As a note, this would force the user to use 24 hour time. Just make sure you remember to do conversions if needed. However, I think this is the route to take. Upvoting. – Matt Rowland May 06 '16 at 14:22
Yes my Class properties are declared like you have shown. – Tareq May 06 '16 at 14:43
@Tareq are you still having problems? Which case is not working? In your example of 4 schedules, I don't see any overlaps. Is there more data that you are testing? – Frank Bryce May 06 '16 at 14:45
@JohnCarpenter, it works partially. Like, considering Classroom = ADMN-100, Weekday = Friday , if I enter 1:30 then it works fine and shows time is overlapped, which is true for SL - 1. But, considering same Classroom and Weekday if I enter 2:30, then it supposed to say time overlapped because SL-2 has the schedule, but it does not work. – Tareq May 06 '16 at 15:30
If different classrooms are used at the same time, there is no overlap. The same classroom needs to be used on the same day at the same time for there to be an overlap, correct? It seems you are expecting to see an overlap if two different classrooms are used at the same time. If you want this, remove `x.Classroom.Equals(roomNo)` from your query. – Frank Bryce May 06 '16 at 15:35

score 0 · Answer 2 · answered May 06 '16 at 13:52

0

Why don't you try using Datetime.Compare() method? Like so:

.Any(
Datetime.Compare(x.StartTime, startsAt)>0 
&&
Datetime.Compare(x.EndTime, startsAt)<0
)

answered May 06 '16 at 13:52

Hugo Nava Kopp

2,906
2
23
41

1

I don't think `DateTime.Compare` will translate to SQL in EF. But really it's not any different from just using the operators instead. – juharr May 06 '16 at 13:56

score 0 · Answer 3 · answered May 06 '16 at 14:07

0

The other answers are not capturing all the possible overlap cases. This is what you want.

check = classes.Any(x=>x.Classroom.Equals(roonNo)
                       && x.Weekday.Equals(weekDay)
                       && x.StartTime <= endsAt
                       && x.EndTime >= startsAt);

answered May 06 '16 at 14:07

Callback Kid

708
5
22

If you don't have an `endsAt` time, you can just use `startsAt` in both places and the result is the same. Although this then degrades to Matt Rowlands answer. – Callback Kid May 06 '16 at 14:09

user786 · Answer 4 · 2016-05-07T06:47:06.217

To select elements with matching results

      var v=  c.ones.Select(x=>x).Where(x =>  x.weekday.Equals("mon")
                               && x.starttime == TimeSpan.Parse("08:00")
                               && x.endtime == TimeSpan.Parse("09:00"));

To find out if overlapping exist

     IQueryable<bool> v=  c.ones.Select(x =>  x.weekday.Equals("mon")
                               && x.starttime == TimeSpan.Parse("08:00")
                               && x.endtime == TimeSpan.Parse("09:00"));

Tareq · Accepted Answer · 2016-05-11T19:48:25.210

SOLVED

I wanted to do something like this, because I had to iterate through each row to find the combination,

foreach (var val in DataSource)
        {
            if (roomNo.Equals(val.Classroom))
            {
                if (weekDay.Equals(val.ClassRoomWeekDay))
                {
                    if (startsAt>= val.StartTime && startsAt< val.EndTime )
                    {
                        check = false;
                    }

                }
            }

        }

Check schedule overlap using LINQ

5 Answers5