c# - 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.

Answer 1

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.