Combined facility date request

Question

I have a list Apartmentswhere everyone Apartmenthas it ApartmentRooms, where everyone ApartmentRoomhas it DateBatches. Each DateBatchhas a list Datesthat represents busy dates (one record for an accepted date).

Apartment 1
 Room 1
    DateBatch
      1.5.2015
      2.5.2015
    DateBatch 2
      8.5.2015
      9.5.2015
 Room 2
    DateBatch
      5.5.2015
      6.5.2015

Thus, you can see that this apartment has 2 rooms, of which number 1 is occupied on the following days: 1,5, 2,5, 8,5 and 9,5, and room 2 is occupied by 5,5 and 6, 5.

The user can enter the desired period of N days and X days in a row, during which he wants to stay.

So, for example, the user enters a period from 1.5 to 15.5, and he wants to sleep 10 nights, I need to list all the apartments where at least one of the living rooms is available for any of the possible combinations of the date that will be next in in this case:

1.5-10.5
2.5-11.5
3.5-12.5
4.5-13.5
5.5-14.5

foreach, foreach , OR, , .

 public static IQueryable<Apartment> QueryByPeriod(this IQueryable<Apartment> apartments, DateTime PeriodStart, DateTime PeriodEnd, int StayDuration)
 {
        var possibleDateRanges = new List<List<DateTime>>();

        //set all possible start dates for the desired period
        for (var i = PeriodStart; i <= PeriodEnd.AddDays(-StayDuration); i = i.AddDays(1))
        {
            List<DateTime> dates = new List<DateTime>();

            foreach(var date in i.DateRange(i.AddDays(StayDuration-1)))
            {
                dates.Add(date);
            }

            possibleDateRanges.Add(dates);
        }

        //filter by date range
        //select apartment rooms where one of possible combinations is suitable for selected period
        foreach (var possibleDates in possibleDateRanges)
        {
            apartments = apartments.Where(m => m.ApartmentRooms
            .Any(g => g.OccupiedDatesBatches.Select(ob => ob.OccupiedDates).Any(od => od.Any(f => possibleDates.Contains(f.Date)))
            ) == false);
        }

        return apartments;
    }

?

Answer 1

( ) LINQ to Entities.

:

var startDates = Enumerable.Range(0, PeriodEnd.Subtract(PeriodStart).Days - StayDuration + 1)
    .Select(offset => PeriodStart.AddDays(offset))
    .ToList();

:

var availableApartments = apartments.Where(a => a.ApartmentRooms.Any(ar =>
    startDates.Any(startDate => !ar.OccupiedDatesBatches.Any(odb => 
        odb.OccupiedDates.Any(od => 
            od.Date >= startDate && od.Date < DbFunctions.AddDays(startDate, StayDuration))))));

, . , , - -, . , ​​, :

public class AvailableApartmentInfo
{
    public Apartment Apartment { get; set; }
    public Room Room { get; set; }
    public DateTime StartDate { get; set; }
}

var availableApartmentInfo =
    from a in apartments
    from ar in a.ApartmentRooms
    from startDate in startDates
    where !ar.OccupiedDatesBatches.Any(odb => 
        odb.OccupiedDates.Any(od => 
            od.Date >= startDate && od.Date < DbFunctions.AddDays(startDate, StayDuration)))
    select new AvailableApartmentInfo { Apartment = a, Room = ar, StartDate = startDate };

Answer 2

OR, () LinqKit. nuget, using LinqKit;, :

    public static IQueryable<Apartment> QueryByPeriod(this IQueryable<Apartment> apartments, DateTime PeriodStart, DateTime PeriodEnd, int StayDuration) {
        var possibleDateRanges = new List<Tuple<DateTime, DateTime>>();
        // list all ranges, so for your example that would be:
        //1.5-10.5
        //2.5-11.5
        //3.5-12.5
        //4.5-13.5
        //5.5-14.5            
        var startDate = PeriodStart;
        while (startDate.AddDays(StayDuration - 1) < PeriodEnd) {
            possibleDateRanges.Add(new Tuple<DateTime, DateTime>(startDate, startDate.AddDays(StayDuration - 1)));
            startDate = startDate.AddDays(1);
        }
        Expression<Func<Apartment, bool>> condition = null;
        foreach (var range in possibleDateRanges) {                
            Expression<Func<Apartment, bool>> rangeCondition = m => m.ApartmentRooms       
                // find rooms where ALL occupied dates are outside target interval             
                .Any(g => g.OccupiedDatesBatches.SelectMany(ob => ob.OccupiedDates).All(f => f.Date < range.Item1 || f.Date > range.Item2)
                );
            // concatenate with OR if necessary
            if (condition == null)
                condition = rangeCondition;
            else
                condition = condition.Or(rangeCondition);
        }
        if (condition == null)
            return apartments;
        // note AsExpandable here
        return apartments.AsExpandable().Where(condition);
    }

, . , , , EF- ( ) .