Overlapping records between two dates using Linq

Question

In my application a user can delegate another user to approve orders.

What I need to validate, is that there are no circular references so if User A is has delegated User B over a certain period, User B can not delegate User A for any time within that period, i.e there is an overlap (dates in dd/mm/yyyy):

Delegator    Delegated To    DateFrom    DateTo
User A       User B          01/11/2012  08/11/2012
User A       User B          16/12/2012  24/12/2012

This is the code I have to check for overlaps:

var overlaps = .Session.QueryOver<DelegateOrderApproval>()
    .Where(x => x.DateFrom > DateTime.Now && delegateOrderApproval.DelegatedUser.UserId == x.DelegatorUser.UserId)
    .And(x => x.DateFrom >= delegateOrderApproval.DateFrom && delegateOrderApproval.DateTo <= x.DateTo
              || (x.DateFrom >= delegateOrderApproval.DateFrom && x.DateTo <= delegateOrderApproval.DateTo)
              || (x.DateFrom <= delegateOrderApproval.DateFrom && x.DateTo >= delegateOrderApproval.DateFrom)
              || (x.DateFrom <= delegateOrderApproval.DateFrom && x.DateTo >= delegateOrderApproval.DateTo))
    .List();

If I try to add the following record:

Delegator: User B
Delegated  To: User A
Date From: 15/11/2012
DateTo:    21/11/2012

there should be no conflict as User B is not a delegate for User A in that time period? It's picking up the record in December.

I have searched for date range overlapping code and tried everything but nothing seems to work.

Any ideas?

Answer 1

Can't you simply do

var doesOverlapExist = Session.QueryOver<DelegateOrderApproval>()
     .Any(x => 
        x.DateFrom > DateTime.Now && 
        delegateOrderApproval.DelegatedUser.UserId == x.DelegatorUser.UserId &&
        x.DateFrom <= delegateOrderApproval.DateTo &&
        x.DateTo >= delegateOrderApproval.DateFrom);

or if you don't have any, replace with

Where(x=> blabla).Count() >0

overlap check logic : Determine Whether Two Date Ranges Overlap