Slow LINQ query

Question

I have a query that runs slowly (in a loop, about 100 takes 5-10 seconds) and has no idea why. It just asks for a list of objects ... your help is much appreciated!

I basically request schedules that have been assigned to specific managers. It must be specified from the specified Shift week or the first 2 days of the next week or the last two days of the previous week.

I tried to calculate .AddDays before, but that didn't help. When I ran the performance test, it highlighted the expression "from" below.

List<Schedule> _schedule = Schedule.GetAll(); List<Shift> _shifts = Shift.GetAll(); // Then later... List<Schedule> filteredSchedule = (from sch in _schedule from s in _shifts where **sch.ShiftID == s.ShiftID & (sch.ManagerID == 1 | sch.ManagerID == 2 | sch.ManagerID == 3) & ((s.ScheduleWeek == shift.ScheduleWeek) | (s.ScheduleWeek == shift.ScheduleWeek.AddDays(7) & (s.DayOfWeek == 1 | s.Code == 2)) | (sch.ScheduleWeek == shift.ScheduleWeek.AddDays(-7) & (s.DayOfWeek == 5 | s.Code == 6)))** select sch) .OrderBy(sch => sch.ScheduleWeek) .ThenBy(sch => sch.DayOfWeek) .ToList(); 

Answer 1

First port of call: use && instead of & and || instead of | . Otherwise, all subexpressions in the where clause will be evaluated, even if the answer is already known.

Second port of call: use the connection instead of the two "from" clauses with where:

 var filteredSchedule = (from sch in _schedule join s in _shifts on s.ShiftID equals sch.ShiftID where ... rest of the condition ... 

Basically, to create a hash of all shift identifiers so that it can quickly find possible matches for each schedule.