I have the following LINQ query written by a former developer and it isn't working when it should.
public bool IsAvailable(Appointment appointment)
{
var appointments = _appointmentRepository.Get;
var shifts = _scheduleRepository.Get;
var city = _customerRepository.Find(appointment.CustomerId).City ?? appointment.Customer.City;
const int durationHour = 1;
DateTime scheduledEndDate = appointment.ScheduledTime.Add(new TimeSpan(durationHour, 0, 0));
var inWorkingHours = shifts
.Where(x =>
//Check if any available working hours
x.Employee.City == city &&
x.ShiftStart <= appointment.ScheduledTime &&
x.ShiftEnd >= scheduledEndDate &&
//check if not booked yet
!appointments
.Where(a =>
(appointment.Id == 0 || a.Id != appointment.Id) &&
a.Employee.Id == x.Employee.Id &&
(
(a.ScheduledTime <= appointment.ScheduledTime &&
appointment.ScheduledTime <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) ||
(a.ScheduledTime <= scheduledEndDate &&
scheduledEndDate <= EntityFunctions.AddHours(a.ScheduledTime, durationHour))
))
.Select(a => a.Employee.Id)
.Contains(x.Employee.Id)
);
if (inWorkingHours.Any())
{
var assignedEmployee = inWorkingHours.FirstOrDefault().Employee;
appointment.EmployeeId = assignedEmployee.Id;
appointment.Employee = assignedEmployee;
return true;
}
return false;
}
The query is suppose to handle the following scenarios
Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time But Does not match any employees in same city - (Return true, Assign as "Unassigned")
Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time AND Employee for that shift is in the same city as the customer (Return True AND Assign to the employee)
If the customer is NOT in the same city as an employee we assign the appointment as "Unassigned" as along as the scheduledTime is within an of the employees shift start/end times
If the customer is in the same city as an employee we assign the appointment to one of the employees (firstOrdefault) and occupy that timeslot.
Appointments CAN NOT overlap (Assigned Ones). Unassigned can't overlap each other.
This query use to work (I've been told). But now it doesn't and I have tried refactoring it and various other paths with no luck. I am now on week two and just don't know where the issue in the query is or how to write it.
Let me know if I need to post anything further. I have verified appointments, shifts, city all populate with valid data so the issue doesn't appear to be with null or missing data.
