Check if new starttime and endtime overlaps with starttime and endtime records in db using linq

Question

I went through few answers and few match the solution I already have.

In my job scheduling app I want to check if a new added task overlaps with a task that is already in the db. The solution below gives me results for the basic scenarios but does not quite work for few. Like in case where endtime for a new task is the same as starttime of another.

e.g:

//this returns false as it should - no overlaps
New Task      : 14:00 - 15:00
Existing Task : 16:00 - 17:00

//this returns true, as existing taskstart is < new takend - 17:00 overlap
New Task      : 14:00 - 17:00
Existing Task : 16:00 - 17:00

//this scenario should return false
/*udpated*/the tasks do not overlap here but touch each other
New Task      : 14:00 - 16:00
Existing Task : 16:00 - 17:00

private bool CheckForOtherTasks(int UserId, DateTime _dtTaskStart, DateTime _dtTaskEnd)
{
    bool _bTasksExist = false;
    using (var _dbContext = new task_Entities())
    {
        _bTasksExist = (from t in _dbContext.Tasks
                        where t.UserId == UserId
                        where t.dtTaskStart < _dtTaskEnd && _dtTaskStart < t.dtTaskEnd
                        select t).Any();
    }

    return _bTasksExist;
}

The above solution works fine until there new endtime == existing starttime or vice versa.

This solution seems to solve it but I am unsure how to implement it along with LINQ without iterating through all the records. How check intersection of DateTime periods

Would using LINQ for this task be a good approach or using a Stored Procedure to returns results be a better option?

Also, would a library such as https://www.codeproject.com/Articles/168662/Time-Period-Library-for-NET be ideal for a small project.

Cheers

Have you tried `... where t.dtTaskStart <= _dtTaskEnd && _dtTaskStart <= t.dtTaskEnd ...` ? — vonludi, Nov 16 '17 at 11:48
Well, that would return me the result as true, which means it is overlapping. I think i should rephrase my question. I want the method to return false in the condition when 16:00 == 16:00 or 16:00 < 16:00 — Amyth, Nov 16 '17 at 11:52
`//this scenario should return false` Isn't that what your code already does? — mjwills, Nov 16 '17 at 12:16
Apparently it wasn't and hence I posted the question. Now that I debug again it is returning false. — Amyth, Nov 16 '17 at 12:17
Dont change the title if you have resolved the issue or not. [Mark the answer that helped you to mark it as resolved.](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) If no answer provided the solution, add your own. — default, Nov 16 '17 at 12:23

score 0 · Answer 1 · answered Nov 16 '17 at 11:49

0

The above solution works fine until there new endtime == existing starttime or vice versa.

Then simply change the conditions from < to <= and from > to >=:

    _bTasksExist = (from t in _dbContext.Tasks
                    where t.UserId == UserId
                    where t.dtTaskStart <= _dtTaskEnd && _dtTaskStart <= t.dtTaskEnd
                    select t).Any();

answered Nov 16 '17 at 11:49

ATC

907
1
9
14

I actually want to return false went 16:00 == 16:00 as when this happens the time do not overlap but as touching each other. I should be able to add a task that are next to each other. Say 15:00 - 16:00 , 16:00 - 17:00 – Amyth Nov 16 '17 at 11:55
So your initial condition seems to be correct. Does your original code returns true with times like this? – ATC Nov 16 '17 at 12:00
It was returning true and hence it stumped me, but now that I debug it again, it seemed to work fine. – Amyth Nov 16 '17 at 12:15

score 0 · Answer 2 · answered Nov 16 '17 at 12:33

With the above test scenario, Initially the returned value was "True". After posting the question, I took a break and got back to start again and it gave me the results as expected.

It's 1:30 AM here is NZ and what I require before writing any more code is some sleep. The test that returned "False" had the endtime "17:00" which was from a previous debug session and the breakpoint was never cleared.

So the answer to my question is. Rest and then Test.

Check if new starttime and endtime overlaps with starttime and endtime records in db using linq

2 Answers2