C#: Return any item that matches the condition

Question

I have a method like this:

    public ActionResult ShowAvailableSpots(int Id, DateTime ArrivalDate, DateTime LeaveDate)
    {
        var query2 = db.Spots
            .Where(c => db.Reservations.Any(r =>
                           DbFunctions.TruncateTime(ArrivalDate) <= DbFunctions.TruncateTime(r.ArrivalDate) && DbFunctions.TruncateTime(LeaveDate) <= DbFunctions.TruncateTime(r.ArrivalDate)
                        || DbFunctions.TruncateTime(ArrivalDate) >= DbFunctions.TruncateTime(r.LeaveDate)
            )).ToList();

        ViewBag.StartingDate = ArrivalDate;
        ViewBag.EndingDate = LeaveDate;
        ViewBag.AvailableSpots = query2;

        ViewBag.CampingSpotId = new SelectList(query2, "CampingSpotId", "SpotName");

        return View();
    }

It determines wether any of the reservations match the date criteria. If they don't match, then the list with Campingspots is returned.

The problem is, that it is returning ALL spots or NONE spots instead of just the spots that are available. This is due to the .Any method. How can I filter out the campingspots that are not available?

Answer 1

Try something like this:

var query2 = db.Spots.Where(c => db.Reservations
                                   .Where(r => c.CampingSpotId == r.CampingSpotId)
                                   .All(r => DbFunctions.TruncateTime(LeaveDate) <= DbFunctions.TruncateTime(r.ArrivalDate)
                                          || DbFunctions.TruncateTime(ArrivalDate) >= DbFunctions.TruncateTime(r.LeaveDate))
                   )).ToList();

The inner Where statement says we're only checking the reservations that apply to that camping spot, and the All statement checks to make sure that every reservation for that campsite is outside the window we're interested in.