Why are there no results from my query?

Question

I want to select available spotId's in my database. I have this method:

public ActionResult ShowAvailableSpots(int Id, DateTime ArrivalDate, DateTime LeaveDate)
{
    var query2 = (from r in db.Reservations
                where (DbFunctions.TruncateTime(r.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                    && DbFunctions.TruncateTime(r.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate))
                  select r.spot);

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

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

    return View();
}

I made sure there's no reservation within the given date range, then why are there no spot id's returned?

The output generated by the query is as follows:

SELECT
   [Extent2].[campingspotid] AS [CampingSpotId],
   [Extent2].[spotname]      AS [SpotName],
   [Extent2].[fieldname]     AS [FieldName],
   [Extent2].[surface]       AS [Surface],
   [Extent2].[wifi]          AS [Wifi],
   [Extent2].[water]         AS [Water],
   [Extent2].[sewer]         AS [Sewer],
   [Extent2].[reserved]      AS [Reserved],
   [Extent2].[booked]        AS [Booked],
   [Extent2].[spotprice]     AS [SpotPrice],
   [Extent2].[type]          AS [Type]   
FROM
   [dbo].[reservations] AS [Extent1]          
INNER JOIN
   [dbo].[campingspots] AS [Extent2]                  
      ON [Extent1].[campingspotid] = [Extent2].[campingspotid]   
WHERE
   (
      (
         CONVERT (DATETIME2, CONVERT(VARCHAR(255), [Extent1].[arrivaldate],                                  102), 102                     )            
      ) >= (
         CONVERT (DATETIME2, CONVERT(VARCHAR(255), @p__linq__0, 102), 102                   ) 
      ) 
   )          
   AND (
      (
         CONVERT (DATETIME2, CONVERT(VARCHAR(255), [Extent1].[leavedate],                                      102), 102)                
      )                    <= (
         CONVERT (DATETIME2, CONVERT(VARCHAR(255), @p__linq__1, 102                                             ), 102)                       
      ) 
   )

PS: I use TruncateTime because of this

EDIT: Here's my Reservation model:

public class Reservation
{
    [Key]
    public int ReservationId { get; set; }

    [DataType(DataType.Date)]
    public DateTime ArrivalDate { get; set; }

    [DataType(DataType.Date)]
    public DateTime LeaveDate { get; set; }
    //Vreemdesleutel van Plek

    public int CampingSpotId { get; set; }

    public virtual CampingSpot spot { get; set; }

}

Here's my campingspot model:

public class CampingSpot
{
    [Key]
    [Required(ErrorMessage = "Please select at least one CampingSpotID")]
    public int CampingSpotId { get; set; }
    public string SpotName { get; set; }  
}

New queryoutput looks like: SELECT CAST(NULL AS int) AS [C1], CAST(NULL AS datetime2) AS [C2], CAST(NULL AS datetime2) AS [C3], CAST(NULL AS int) AS [C4], CAST(NULL AS int) AS [C5], CAST(NULL AS int) AS [C6] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0

The output above was generated by this query:

var res = db.Reservations.Where(c => DbFunctions.TruncateTime(c.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                                       && DbFunctions.TruncateTime(c.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate)
                                       && c.CampingSpotId == null); 

Answer 1

I can see from your generated SQL, it is doing a FROM Reservations with INNER JOIN on CampingSpots, and you've mentioned you made sure there ano reservations for this time date, therefore no results...

EDIT 1 AS per your comment, if you want all camping spots which you have no reservations, you want to do

FROM CampingSpots cs 
LEFT JOIN Reservations r 
WHERE r.ID IS NULL

And in your Linq2Entitis, something like:

    public class Reservation
    {
        public DateTime ArrivalDate { get; set; }
        public DateTime LeaveDate { get; set; }
    }

    public class CampingSpot
    {
        public virtual Reservation Reservation { get; set; }

    }
    public class TestClass
    {
        public void Test()
        {            
            var CampingSpots = new List<CampingSpot>().AsQueryable();

            var ArrivalDate = new DateTime();
            var LeaveDate = new DateTime();

            var res = CampingSpots.Where(c => DbFunctions.TruncateTime(c.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                                           && DbFunctions.TruncateTime(c.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate)
                                           && c.Reservation == null)
                                 ).ToList();                
        }
    }

EDIT 2

Ok you will need to add Reservation into CampingSpot model as a navigation property.

public class CampingSpot
{
    [Key]
    [Required(ErrorMessage = "Please select at least one CampingSpotID")]
    public int CampingSpotId { get; set; }
    public string SpotName { get; set; }  

    public virtual int ReservationId { get; set; }
}

And then modify your Controller Action to:

        public ActionResult ShowAvailableSpots(int Id, DateTime ArrivalDate, DateTime LeaveDate)
        {
            var query2 = db.CampingSpots.Where(c => DbFunctions.TruncateTime(c.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                                           && DbFunctions.TruncateTime(c.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate)
                                           && c.Reservation == null)
                                 ).ToList();

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

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

            return View();
        }

EDIT 3

I just noticed what you are doing. if camping Spots has no ArrivalDate and LeaveDate. You will need to get the reservations between that time, and then in C# figure out what dates are actually free. So yes your original query is correct, but you will need to manually go over each "Day/Period" and calculate if it is free. If you receive no results, it means that camping spot is free for the whole of that period, as it has no reservations. IF it does have reservations, you either need to come back saying taken, or do calculations to figure out which days excactly are taken, and which are free.

EDIT 4

            var query2 = db.CampingSpots
                .Where(c => !db.Reservations.Any(r => 
                               DbFunctions.TruncateTime(r.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                            && DbFunctions.TruncateTime(r.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate)                 
                )).ToList();

EDIT 5

You probably want something like this, in case

1234567 < --- Day
|---|   < --- Reservation
  |---| < --- Query

Which would not match Edit 4, as arrival date AND leave date is not between. So you need to check with below:

            var query2 = db.CampingSpots
                .Where(c => !db.Reservations.Any(r => 
                               (DbFunctions.TruncateTime(r.ArrivalDate) >= DbFunctions.TruncateTime(ArrivalDate)
                            && DbFunctions.TruncateTime(r.ArrivalDate) < DbFunctions.TruncateTime(LeaveDate))    
                              ||
                               (DbFunctions.TruncateTime(r.LeaveDate) <= DbFunctions.TruncateTime(LeaveDate)
                            && DbFunctions.TruncateTime(r.LeaveDate) > DbFunctions.TruncateTime(ArrivalDate))             
                )).ToList();