不包括周末的列值的LINQ总和

Question

我正试图计算一周中与周末工作时间不同的工作时间。这就是我希望实现的目标：

private void SetTotalCharge()
{
int.TryParse(MDate.ToString("MM"), out int month);
int.TryParse(MDate.ToString("yyyy"), out int year);
TotalCharge = 
Decimal.Round(Convert.ToDecimal(db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & (a.Date.DayOfWeek != DayOfWeek.Saturday && a.Date.DayOfWeek != DayOfWeek.Sunday))
.Select(a => a.RepairHours).Sum() * RateWeek
+ db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & (a.Date.DayOfWeek == DayOfWeek.Saturday && a.Date.DayOfWeek == DayOfWeek.Sunday))
.Select(a => a.RepairHours).Sum() * RateWeekend), 2, MidpointRounding.AwayFromZero);
}

我得到的错误：

System.NotSupportedException: "The specified type member 'DayOfWeek' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

我该怎么绕过这个？

Answer 1

DayOfWeek枚举在服务器端不存在，这是异常的根本原因。以下是解决该问题的两种解决方法。第一种方法使用工作日序数(0和6(的列表，第二种方法直接在条件中检查序数(类似于原始方法(。

以下示例适用于实体框架。对于EFCore支持，请查找并替换为DbFunctions.DiffDays和EF.Functions.DateDiffDay作为相应的标准。

private void SetTotalCharge()
{
var dateTime = new DateTime(1899, 12, 31);
var weekends = new List<int>{ 0, 6 };
int.TryParse(MDate.ToString("MM"), out int month);
int.TryParse(MDate.ToString("yyyy"), out int year);
TotalCharge =
Decimal.Round(Convert.ToDecimal(db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & !weekends.Any(r => r == DbFunctions.DiffDays(dateTime, a.Updated) % 7))
.Select(a => a.RepairHours).Sum() * RateWeek
+ db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & weekends.Any(r => r == DbFunctions.DiffDays(dateTime, a.Updated) % 7))
.Select(a => a.RepairHours).Sum() * RateWeekend), 2, MidpointRounding.AwayFromZero);
}

或

private void SetTotalCharge()
{
var dateTime = new DateTime(1899, 12, 31);
int.TryParse(MDate.ToString("MM"), out int month);
int.TryParse(MDate.ToString("yyyy"), out int year);
TotalCharge =
Decimal.Round(Convert.ToDecimal(db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & ((DbFunctions.DiffDays(dateTime, a.Date) % 7) != 0 && (DbFunctions.DiffDays(dateTime, a.Date) % 7) != 6))
.Select(a => a.RepairHours).Sum() * RateWeek
+ db.MyTable
.Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & ((DbFunctions.DiffDays(dateTime, a.Date) % 7) == 0 || (DbFunctions.DiffDays(dateTime, a.Date) % 7) == 6))
.Select(a => a.RepairHours).Sum() * RateWeekend), 2, MidpointRounding.AwayFromZero);
}