使用Linq检查日期在范围内 [英] 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:

• 2015年13月13日至2015年4月18日
• 2015年15月15日至2015年4月20日
• 2015年10月4日至2015年4月16日

，我希望查看所有在2015年4月15日至2015年4月16日之间的预订.从视觉上看，这看起来像:

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

正在使用:

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

这只会返回最后两个保留，我相信这是因为它正在检查日期上的> = 和< = 而不是检查它是否在范围内.我知道使用SQL BETWEEN 运算符，但是我想知道是否有任何方法可以使用Linq表达式或在C#中执行此操作?理想情况下，我想将此操作作为EF查询的一部分，而不是返回所有保留，然后在此列表上执行操作.

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.

谢谢.

推荐答案

如果以下规则为真，则两个范围重叠:

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

这篇关于使用Linq检查日期在范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！