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?

I guess you are using QueryOver from NHibernate, should your check run at the database level? If yes, I think you should tag the question with some NHibernate-related tag because that might impact the way you could do that. — Wasp, Sep 27 '12 at 13:09

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

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

edited May 23 '17 at 11:56

Community

1
1

answered Sep 27 '12 at 13:15

Raphaël Althaus

59,727
6
96
122

Yep, that did it. Think I was over complicating it!! Thanks a lot Raphaël! – MikeL Sep 27 '12 at 14:05

Overlapping records between two dates using Linq

1 Answers1

Linked

Related