A LINQ query that will check if a start time and end time is available

Viewed 2292

So basically, the user will reserve a time on a specific day. for example March 21, 2016 10:00 AM to March 21, 2016 1 PM. If another reservation is to be made, the system should not accept a time between march 21, 2016 10 AM to 1 PM since it's already reserved to another user. please help im at loss and kinda new on programming. I'm stuck with this query

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;
    }
}

here is the design of the schedule table

enter image description here

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;
                }
            }
        }
    }
3 Answers
Related