Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

c# - Date range falling between two dates in a LINQ query

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...