Check dates fall within range using Linq

Question

I'm currently working on something which requires me to check availability between two dates. My current methods aren't returning the expected result. For example, say I have the following reservations:

• 13/04/2015 to 18/04/2015
• 15/04/2015 to 20/04/2015
• 10/04/2015 to 16/04/2015

and I wish to view all reservations that are between 15/04/2015 to 16/04/2015. Visually, this might look like:

              15    16
      X________|_____|______X
               |X____|_____________X
X______________|____X|
               |     | 

To get all the reservations that fall between these dates I am using:

public List<ReservationClientModel> GetReservationsByDateRange(int id, DateTime checkin, DateTime checkout)
    {
        var reservation = _repository.FindAllBy(x => 
            (x.StartDate >= checkin && x.EndDate <= checkout) ||
            (x.StartDate >= checkin && x.StartDate <= checkout) ||
            (x.EndDate >= checkin && x.EndDate <= checkout)),
            y => y.RoomTypeNav)
            .ToList();

        return Mapper.Map<List<ReservationClientModel>>(reservation);
    }

Which is using:

public IEnumerable<TEntity> FindAllBy(Expression<Func<TEntity, bool>> predicate, params Expression<Func<TEntity, object>>[] includes)
    {
        var set = _dbSet.AsQueryable();
        set = includes.Aggregate(set, (current, include) => current.Include(include));
        return set.Where(predicate);
    }

This will only return the last two reservations though, and I believe this to be because it is checking for >= and <= on the dates, rather than checking that it falls within the range. I'm aware of using the SQL BETWEEN operator, but I was wondering if there is any way to do this using a Linq expression or from within C#? Ideally I would like to perform this as part of the EF Query as opposed to returning all reservations, then performing the operations on this list.

Thanks.

Answer 1

Two ranges overlap if following rule is true:

(StartDate1 <= EndDate2) and (EndDate1 >= StartDate2)

By applying this rule to your code you get:

var reservation = _repository
    .FindAllBy(x => x.StartDate <= checkout && x.EndDate >= checkin,  
               y => y.RoomTypeNav)
    .ToList();