Filter (reservation) items on start and end DateTime-CodePudding

I'm new to .NET-Core and building API's and I got stuck on a problem. I'm making an (Android) app where people can plan an event/birthday party or something. The user can retrieve a list of available (pre-added) Venues/locations after they filled in the start and end date/time. This is the part where I get stuck.

I want a list of available venues after they filled in the start and end date, showing only available venues for their selected time slots.

I have 2 models in my API, a 'Venue' model and a 'VenueReservation' model.

The Venue model has an Id, location and name. The VenueReservationModel has an Id, venueId, startDateTime, endDateTime.

What can I use or do to retrieve the available venues based of the DateTime input of the user (start and end DateTime)?

I played around with LINQ using the Enumerable.Where method, but I can't seem to find the answer.

Thanks in advance!

CodePudding user response：

You could do it this way.

Get reservations with input date between Start and End

IEnumerable<VenueReservation> reservationsBetweenStartAndEnd = venueReservations
    .Where(x => inputDateTime > x.StartDateTime && inputDateTime < x.EndDateTime);

Get reservations with input date outside of Start and End

IEnumerable<VenueReservation> reservationsOutsideStartAndEnd = venueReservations
    .Where(x => inputDateTime < x.StartDateTime || inputDateTime > x.EndDateTime);

If you want want to strictly compare date and cut hours and minute you can simply use .Date on DateTime.

So as an example you would do this

IEnumerable<VenueReservation> reservationsBetweenStartAndEnd = venueReservations
    .Where(x => inputDateTime.Date > x.StartDateTime.Date && inputDateTime.Date < x.EndDateTime.Date);

CodePudding user response：

So you have classes like:

class Venue
{
    public int Id {get; set;}
    public string Location {get; set;}
    public string Name {get; set;}
}

class VenueReservation
{
    public int Id {get; set;}
    public int VenueId {get; set;}             // foreign key to Venue
    public DateTime StartDateTime {get; set;}
    public DateTime EndDateTime {get; set;}
}

And you have:

IQueryable<Venue> Venues => ...        // Probably DbSet<Venue>
IQueryable<VenueReservation> VenueReservations => ...

I want a list of available venues after they filled in the start and end date, showing only available venues for their selected time slots.

So you need to fetch "Venues with their zero or more VenueReservations". Once you've got them, you could keep only those Venues that have no Reservation at all during the selected TimeSlot.

Get VenuesWithTheirReservations

Normally to get "Venues with their zero or more VenueReservations", I'd use Queryable.GroupJoin. Some people mentioned that .net core doesn't support GroupJoin. In that case, use a left outer join followed by a GroupBy:

var venuesWithTheirReservations = venues.GroupJoin(venueReservations,

    venue => venue.Id,                            // from every Venue take the Id
    venueReservation => venueReservation.VenueId, // from every Reservation take the foreign key

    (venue, reservationsForThisVenue) => new
    {
        Venue = venue,
        Reservations = reservationsForThisVenue,
    });

Or LINQ left outer join followed by GroupBy:

var venuesWithTheirReservations = from venue in venues
    join reservation in venueReservations
    on venue.Id equals reservation.VenueId into g
    from reservationForThisVenu in g.DefaultIfEmpty()
    select new
    {
        Venue = venue,
        Reservation = reservationForThisVenue,
    })
    .GroupBy(joinResult => joinResult.Venue,

    // parameter resultSelector: for every venue, and all reservations for this venue
    // make one new
    (venue, reservationsForThisVenue) => new
    {
        Venue = venue,
        Reservations = reservationsForThisVenue,
    });

Keep only the available Venues

So, now you've got the venues, each with their reservations, you can use Queryable.Where to keep only those Venues that have no reservation at all during the time slot.

In other words: none of the Reservations should overlap with the time slot.

all Reservations should either end before the time slot starts (= the complete Reservation is before the time slot)
OR start after the time slot ends (= the complete Reservation is after the time slot.

We don't want Reservations that are partly during the time slot

So continuing the LINQ:

DateTime timeSlotStart = ...
DateTime timeSlotEnd = ...

var availableVenues = venuesWithTheirReservations.Where(venue =>

    // every Reservation should end before timeSlotStart
    // OR start after the timeSlotEnd:
    venue.Reservations.All(reservation =>

        // end before timeSlotStart OR
        reservation.EndDateTime <= timeSlotStart ||

        // start after the timeSlotEnd:
        reservation.StartDatetime >= timeSlotEnd));

CodePudding user response：

also

List<VenueReservation> reservationsBetweenStartAndEnd = venueReservations
    .Where(x => inputDateTime.Date > x.StartDateTime.Date && inputDateTime.Date < x.EndDateTime.Date).toLis();