Date range falling between two dates in a LINQ query

Question

I'm trying to write a select query which returns records where the input date range falls between two date fields in a LINQ query.

My inputs are:

• date1 - start date
• date2 - end date

My database fields are

• AppointmentStart
• AppointmentEnd

Additionally, I'd also like to ensure that an input of 14:00 - 15:00 doesn't return a value for 15:00-16:00.

return (from t1 in db.Appointments where (t1.AppointmentStart <= date2 && (t1.AppointmentEnd) >= date1)

If anybody can assist me with this, I'd appreciate it.

Solution 1

It looks backwards to me.

if the following is true:

Date1 = start

Date2 = end

then i would think startdate after or equal to appointmentstart and enddate before or equal to appointmentend or:

return (from t1 in db.Appointments where (date1 >= t1.AppointmentStart && date2 <= t1.AppointmentEnd))

i also changed the parens because they didn't make sense to me (seemed like one was missing)

Solution 2

I'm not 100% clear on your requirements. In your opening line you asked for records "where the input date range falls between two date fields", but in the "Additionally" line you imply that you don't want to return records where the start date of the appointment doesn't equal the end date of your input. I take these to be two different requirements, so I'll give you two different queries.

The first query is:

    from t1 in db.Appointments
    where date1 >= t1.AppointmentStart
    where date2 <= t1.AppointmentEnd
    select t1;

The second query is:

    from t1 in db.Appointments
    where date2 > t1.AppointmentStart
    where date1 < t1.AppointmentEnd
    select t1;

The first query returns records that "contain" the input dates.

The second query returns records that "overlap" the input dates.

I think it makes more sense that you want the overlap query and this one will meet your "14:00 - 15:00 doesn't return a value for 15:00-16:00" requirement.

Let me know if I made a mistake understanding your requirements and need to make any changes.

Comments

[Mark Sizer]

Agree, second query is superior. First query will omit multi-day appointments that start / end outside the specified date range. Perhaps not likely for "appointments" which are often time-based not day-based but essential for other calendar entry types that span multi-days.