How could I modify this query to get what I want - Calendar dates

Question

Hi

I have this query

return plannerDb.IndividualCalendars.Where(u => 
    u.UserId == userId && 
    u.StartDate.Date >= startDate && 
    u.EndDate.Date <= endDate)

so basically what this query does is that is looks for all calendar events that range from the start of that month to the end of that month.

However this does not account for events such as this

Start Date: 12/26/2009 12:00:00 AM
End Date: 1/8/2010 12:00:00 AM

So today is December 30th. My calendar displays from Nov 29th to Jan 8th.

So this event will be shown this month. However next month when January comes around it will show from December 27th to Feb 6th.

Now since the start date is not in this range it will ignore this event and not display it.

So I need a way to show these kinds of events but at the same time only gather the ones that are needed.

I could always just get every event from the user they had but I think that is such a waste since I am going to have to loop through that and sort it.

So I want only records that will be shown.

So as you can see I am using linq.

Thanks

Answer 1

If I understand your requirements correctly, you only need events that overlap the interval [startdate, endDate]. If that's correct, this will do it for you:

plannerDb.IndividualCalendars.Where(
    u => u.UserId == userId &&
        (u.StartDate.Date < startDate && (
            (u.StartDate.Date >= startDate && u.StartDate.Date <= startDate) || 
            (u.EndDate.Date >= endDate && u.EndDate.Date <= endDate) ||
            (u.StartDate.Date <= startDate && u.EndDate.Date >= endDate)
        )
);

Basically, you only need to know if u.StartDate.Date is in [startDate, endDate] or u.EndDate.Date is in [startDate, endDate] or if the interval [u.StartDate.Date, u.EndDate.Date] contains [startDate, endDate]. Perhaps a simpler way to express this is:

plannerDb.IndividualCalendars.Where(
    u => u.UserId == userId &&
        !(u.StartDate.Date > endDate || u.EndDate.Date < startDate)
);

That is, the event can not start after the endDate or end before the startDate. Equivalently:

plannerDb.IndividualCalendars.Where(
    u => u.UserId == userId &&
        (u.StartDate.Date <= endDate && u.EndDate.Date >= startDate)
);

This last version is the clearest. It says that the event has to start before the endDate and end after the startDate.

Answer 2

Sounds like you just want to change the query a bit

return plannerDb.IndividualCalendars.Where(u => u.UserId == userId
   && (
        (startDate <= u.StartDate.Date && endDate >= u.StartDate)  // Is start date in range
     || (startDate <= u.EndDate.Date && endDate >= u.EndDate)  // Is end date in range
     || (startDate > u.StartDate.Date && endDate < u.EndDate)  // Is range within start/end
      )
   )

I think that will solve it, unless I've made a trivial mistake.

Answer 3

return plannerDb.IndividualCalendars.Where(u => u.UserId == userId && u.StartDate.Date <= endDate && (u.StartDate.Date >= startDate || u.EndDate.Date >= startDate)