Query range of data linq c#

Question

I have a calendar in my website and users can create events of one or more days.
So, in my Calendar table I set two fields Start(Datetime) and End(Datetime).

I want to do a query with a specific date range like from 08/01/2017 to 08/31/2017.
Now, how can I take all the events that are in this range?

I have to take:

• Events that start in this range and end before or after the range end,
• Events that start before the range and end in this range,
• Events that start before this range but end after this range

Is it possible to do a single query or i have to do three different query?

I hope I explained myself, otherwise ask me...

Answer 1

I would say it should be enough to test for event.End >= range.Start && event.Start <= range.End

       0 1 2 3 4 5 6 7 8 9
Range        |-----|
Evt1   |-|                   evt.End < range.Start => false
Evt2                 |-|     evt.Start > range.End => false
Evt3     |-----------|       => true
Evt4     |-------|           => true
Evt5             |-----|     => true
Evt6           |-|           => true

Answer 2

Your requirements can be boiled down to the facts that:

• the event starts before the end of the range and
• the event ends after the start of the range

(the "and" is important, otherwise events totally outside the range will match, too).

So, this should work:

DateTime startRange = new DateTime(2017, 08, 01);
DateTime endRange   = new DateTime(2017, 08, 32);

var events = db.Events.Where(e => e.Start <= rangeEnd && e.End >= rangeStart);

Answer 3

DateTime startRange = new DateTime(2017, 08, 01);
DateTime endRange   = new DateTime(2017, 08, 32);

var events = db.Events
   .Where(e => e.Start >= startRange && startRange <= e.End && e.End >= endRange
            || e.Start <= startRange && e.End >= endRange
            || e.Start <= startRange && endRange >= e.Start && e.End <= endRange);

Is this sufficient?