Search code examples
c#sqlasp.net-mvclinqquerying

Find available rooms between dates LINQ

I am trying to make a system where a user will input an Arrival Date and a Departure Date and a Room Type they wish to have then the program will check if it any rooms of that type are available then if so it will return the room numbers.

View

This is the view model I am using to retrieve information from the View

    public class AvailabilityDTO
{
    [Key]
    public string Id { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy-MM-dd}")]
    public DateTime Arrival { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy-MM-dd}")]
    public DateTime Departure { get; set; }

    [Required]
    public string RoomType { get; set; }
}

Here Are My Database Tables

Reservations

Rooms

The problem is that I am unsure how to use an LINQ expression to obtain the Room Numbers of the rooms with the same RoomTypes as stated but that are not part of any reservation during the selected dates.

The closest I have gotten is to using the same LINQ expression I use when creating a Reservation to check if it is occupied during those dates in the admin CRUD. That Expression is:

        private bool OccupiedRoom(Reservation CurrentReservation)
    {
        var currentBooking = db.Reservations
            .Where(b => b.RoomId == CurrentReservation.RoomId)
            .Select(b => (b.Arrival <= CurrentReservation.Arrival && b.Depature >= CurrentReservation.Arrival) ||
                         (b.Arrival < CurrentReservation.Depature && b.Depature >= CurrentReservation.Depature) ||
                         (CurrentReservation.Arrival <= b.Arrival && CurrentReservation.Depature >= b.Arrival)

            )
            .FirstOrDefault();

        return currentBooking;
    }

Yet attempting to modify this to fit my situation has left me absolutely stumped and any help is appriciated.

Solution

  • First, you should always start with the entity you're after, i.e. db.Rooms rather than db.Reservations. Then, if you don't already have a Reservations navigation property on Room, you should add one, as it greatly simplifies the query. With that, it becomes a simple matter of ensuring that all reservations associated with the room either end before the request arrival date or begin after the requested departure date. Therefore:

    var availableRooms = db.Rooms.Where(m => m.Reservations.All(r => r.Departure <= model.Arrival || r.Arrival >= model.Departure)

    I used <= and >= here, because in a normal situation you would have check out time of like 11am and then a check in time of 3pm. As a result, the same room can actually be booked by two people for the same day, as long as one is checking out and the other is checking in. If that's not your situation, adjust the query accordingly.