我有以下由一位前开发人员编写的LINQ查询,它在应该使用的时候不起作用。
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;
}
类
public class Appointment
{
[Key]
public int Id { get; set; }
public int CustomerId { get; set; }
public virtual Customer Customer { get; set; }
public DateTime ScheduledTime { get; set; }
public int? EmployeeId { get; set; }
public virtual Employee Employee { get; set; }
}
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Province { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string Email { get; set; }
public virtual string Fullname { get { return FirstName + " " + LastName; } }
public Customer(){ }
}
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Province { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string Email { get; set; }
public virtual string Fullname { get { return FirstName + " " + LastName; } }
public Employee() { }
}
public class Shift
{
[Key]
public int Id { get; set; }
public DateTime ShiftStart { get; set; }
public DateTime ShiftEnd { get; set; }
public int EmployeeId { get; set; }
public virtual Employee Employee { get; set; }
}
该查询假设可以处理以下情况
如果客户与员工不在同一城市,我们将约会分配为“未分配”,并且ScheduledTime在员工轮班开始/结束时间之内
如果客户与员工在同一城市,则我们将约会分配给其中一位员工(firstOrdefault),并占用该时间段。
约会不能重叠(已分配)。未分配不能互相重叠。
这个查询用来工作(有人告诉我)。但是现在还不行,我已经尝试过重构它以及其他各种方法,但是都没有运气。我现在在第二周,只是不知道查询中的问题在哪里或如何编写。
让我知道是否需要发布更多内容。我已经验证了约会,轮班,城市都使用有效数据填充,因此问题似乎不存在为空或缺少数据。
首先,目前还不清楚100%的含义是“该查询用于工作(有人告诉我)。但是现在不行”是什么意思。您能否提供一些案例来说明它“不起作用”?
从我的角度来看,查询看起来几乎是正确的,但是我猜想有一些奇怪的事情。让我们看一下这段代码:
!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)
在WHERE条件中,除其他所有内容外,您还可以通过进行过滤a.Employee.Id == x.Employee.Id
。这意味着WHERE子句之后的集合将仅包含单个雇员的约会。因此,我想我们可以将这一部分重写为:
!appointments.Any(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))
))
另一个问题可能是:比较开始/结束日期。在上面的代码中,您正在检查以下内容:
.........[----------------]...........
^^^^^^
||||||
start date and end date
not in this interval
因此,您检查:
这意味着您将不会遇到以下情况:
.........[----------------]...........
^ ^
| |
start date end date
这也是不可接受的。但是根据你的代码const int durationHour = 1;
。因此,每次会议都持续一个小时,这种制动情况对您来说应该不是问题。
无论如何,某些示例数据会使代码崩溃。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句