CODEWITHSUNDEEP
c#asp.netlinqentity-frameworkdatetime
moikey
moikey

Reputation: 353

Check dates fall within range using Linq

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:

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.

Upvotes: 4

Views: 6290

Answers (1)

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

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();

Upvotes: 11

Related Questions