Overlapping records between two dates using Linq
c#, date, linq
Solution
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
Problem
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?