c# - 将检查开始时间和结束时间是否可用的 LINQ 查询

Question

所以基本上，用户会在特定日期预订时间。例如，2016 年 3 月 21 日上午 10:00 至 2016 年 3 月 21 日下午 1 点。如果要进行另一个预订，系统不应接受 2016 年 3 月 21 日上午 10 点到下午 1 点之间的时间，因为它已经被另一个用户预订了。请帮助我不知所措，并且对编程有点陌生。我坚持这个查询

public Boolean CheckExistingTime(string reason, string coursegrade, string section, DateTime start, DateTime end)
{
    try
    {
        using (IHSEntities model = new IHSEntities())
        {
            var list = from sched in model.Schedules
                       where sched.Reason == reason && sched.CourseGrade == coursegrade && sched.Section == section
                       select new ScheduleList
                       {
                           scheduleid = sched.ScheduleID,
                       };
            if (list.ToList().Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }

    catch (Exception ex)
    {
        throw ex;
    }
}

这是调度表的设计

I am using Entity Framework, so here is the complete Schedule Class



  using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Data;
    namespace Business
    {
        public class Schedule
        {
            public class ScheduleList
            {
                public int scheduleid { get; set; }
                public string coursegrade { get; set; }
                public string section { get; set; }
                public string reason { get; set; }
                public string date { get; set; }
                public string level { get; set; }
                public string adviser { get; set; }
                public string starttime { get; set; }
                public string endtime { get; set; }
                public string inputtedby { get; set; }
            }
            public List<ScheduleList> GetAllSchedule(DateTime date)
            {

                try
                {
                    using (IHSEntities model = new IHSEntities())
                    {
                        var list = from schedule in model.Schedules
                                   where schedule.Date >= date
                                   select new ScheduleList
                                   {
                                      scheduleid = schedule.ScheduleID,
                                      coursegrade = schedule.CourseGrade,
                                      section = schedule.Section,
                                      reason = schedule.Reason,
                                      level = schedule.Levels,
                                      date = schedule.Date.ToString(),
                                      inputtedby = schedule.InputedBy,
                                   };
                        return list.ToList();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
            public List<ScheduleList> GetAllToday(DateTime date)
            {

                try
                {
                    using (IHSEntities model = new IHSEntities())
                    {
                        var list = from schedule in model.Schedules
                                   where schedule.Date == date orderby schedule.starttime
                                   select new ScheduleList
                                   {
                                       scheduleid = schedule.ScheduleID,
                                       coursegrade = schedule.CourseGrade,
                                       section = schedule.Section,
                                       reason = schedule.Reason,
                                       level = schedule.Levels,
                                       starttime = schedule.starttime.ToString(),
                                       endtime = schedule.endtime.ToString(),
                                       date = schedule.Date.ToString(),
                                       inputtedby = schedule.InputedBy,
                                   };
                        return list.ToList();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
public Boolean CheckExistingTime(string reason, string coursegrade, string section, DateTime start, DateTime end)
        {
            try
            {
                using (IHSEntities model = new IHSEntities())
                {
                    var list = from sched in model.Schedules
                               where sched.Reason == reason && sched.CourseGrade == coursegrade && sched.Section == section && sched.starttime <= start && sched.endtime >= end
                               select new ScheduleList
                               {
                                   scheduleid = sched.ScheduleID,
                               };
                    if (list.ToList().Count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
            public ScheduleList GetSchedule(int id)
            {

                try
                {
                    using (IHSEntities model = new IHSEntities())
                    {
                        var list = (from schedule in model.Schedules
                                   where schedule.ScheduleID == id
                                   select new ScheduleList
                                   {
                                       coursegrade = schedule.CourseGrade,
                                       section = schedule.Section,
                                       reason = schedule.Reason,
                                       date = schedule.Date.ToString(),
                                       level = schedule.Levels,
                                       adviser = schedule.Senttoadviser,
                                       inputtedby = schedule.InputedBy,
                                   }).First();
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
            public Boolean Delete(int id)
            {
                try
                {
                    using (IHSEntities model = new IHSEntities())
                    {
                        foreach (Data.Schedule sched in model.Schedules.Where(x => x.ScheduleID == id))
                            model.Schedules.Remove(sched);
                        model.SaveChanges();

                        return true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            public Boolean Update(int id, DateTime newdate)
            {
                try
                {
                    using (IHSEntities model = new IHSEntities())
                    {
                        var list = (from sched in model.Schedules
                                    where sched.ScheduleID == id
                                    select sched).First();

                        list.Date = newdate;
                        model.SaveChanges();
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }

Answer 1

这应该可以解决问题:

using (var model = new IHSEntities())
{
    bool result = 
     model.Schedules.Where(s => s.Section == section && s.CourseGrade == coursegrade
                                && s.Reason == reason)
                    .Any(s => (s.starttime >= start && s.starttime <= end) ||
                              (s.endtime <= end && s.endtime >= start));
    return result;
}

如果有任何记录的开始时间位于开始和停止之间或结束时间位于开始和停止之间，这将返回 true。

编辑:这仅在您没有保存结束时间和开始时间且日期部分当然被截断的情况下才有效，否则您将需要对日期进行额外检查。 (但请不要从中删除日期部分，那么您的日期字段甚至不是必需的)