LINQ to Entities计算工作日/周末工时费用报错的解决方法

错误核心原因是：LINQ to Entities无法将.DayOfWeek属性的访问转换为对应的SQL语句，同时你的代码里还有一处逻辑错误——第二个Where条件中a.Date.DayOfWeek == DayOfWeek.Saturday && a.Date.DayOfWeek == DayOfWeek.Sunday，一个日期不可能同时是周六和周日，需改为||。

以下是几种可行的修复方案：

方法1：使用EF内置日期函数（推荐） #

EF6中使用EntityFunctions.DayOfWeek，EF Core中使用DbFunctions.DayOfWeek，这些方法可被直接转换为SQL原生日期函数执行：

private void SetTotalCharge()
{
    int month = MDate.Month;
    int year = MDate.Year;

    // 计算工作日（周一至周五）工时总和
    var weekdayHours = db.MyTable
        .Where(a => a.Date <= MDate 
            && a.Date.Month == month 
            && a.Date.Year == year 
            && DbFunctions.DayOfWeek(a.Date) >= 1 
            && DbFunctions.DayOfWeek(a.Date) <= 5) // 注意：不同数据库返回值有差异，比如SQL Server中1=周日，需自行调整
        .Sum(a => a.RepairHours);

    // 计算周末（周六、周日）工时总和
    var weekendHours = db.MyTable
        .Where(a => a.Date <= MDate 
            && a.Date.Month == month 
            && a.Date.Year == year 
            && (DbFunctions.DayOfWeek(a.Date) == 6 || DbFunctions.DayOfWeek(a.Date) == 0)) // 对应周六和周日，需匹配数据库规则
        .Sum(a => a.RepairHours);

    TotalCharge = Decimal.Round(Convert.ToDecimal(weekdayHours * RateWeek + weekendHours * RateWeekend), 2, MidpointRounding.AwayFromZero);
}

注意：不同数据库的DayOfWeek返回值规则不同，比如SQL Server中DATEPART(dw, Date)默认1=周日、7=周六；MySQL中DAYOFWEEK(Date)1=周日、7=周六，WEEKDAY(Date)0=周一、6=周日，需根据实际使用的数据库调整判断数值。

方法2：先筛选数据再拉到内存处理 #

如果当月数据量不大，可先将符合日期范围的数据加载到内存，再用.DayOfWeek判断：

private void SetTotalCharge()
{
    int month = MDate.Month;
    int year = MDate.Year;

    // 先将符合日期条件的数据加载到内存，切换为LINQ to Objects
    var filteredData = db.MyTable
        .Where(a => a.Date <= MDate 
            && a.Date.Month == month 
            && a.Date.Year == year)
        .AsEnumerable();

    var weekdayHours = filteredData
        .Where(a => a.Date.DayOfWeek != DayOfWeek.Saturday && a.Date.DayOfWeek != DayOfWeek.Sunday)
        .Sum(a => a.RepairHours);

    var weekendHours = filteredData
        .Where(a => a.Date.DayOfWeek == DayOfWeek.Saturday || a.Date.DayOfWeek == DayOfWeek.Sunday)
        .Sum(a => a.RepairHours);

    TotalCharge = Decimal.Round(Convert.ToDecimal(weekdayHours * RateWeek + weekendHours * RateWeekend), 2, MidpointRounding.AwayFromZero);
}

此方法适合数据量小的场景，避免大量数据加载到内存影响性能。

方法3：直接调用SQL原生函数（EF6适用） #

通过SqlFunctions.DatePart直接调用SQL的DATEPART函数：

private void SetTotalCharge()
{
    int month = MDate.Month;
    int year = MDate.Year;

    var weekdayHours = db.MyTable
        .Where(a => a.Date <= MDate 
            && a.Date.Month == month 
            && a.Date.Year == year 
            && SqlFunctions.DatePart("weekday", a.Date) != 1 
            && SqlFunctions.DatePart("weekday", a.Date) != 7) // SQL Server中1=周日、7=周六
        .Sum(a => a.RepairHours);

    var weekendHours = db.MyTable
        .Where(a => a.Date <= MDate 
            && a.Date.Month == month 
            && a.Date.Year == year 
            && (SqlFunctions.DatePart("weekday", a.Date) == 1 || SqlFunctions.DatePart("weekday", a.Date) == 7))
        .Sum(a => a.RepairHours);

    TotalCharge = Decimal.Round(Convert.ToDecimal(weekdayHours * RateWeek + weekendHours * RateWeekend), 2, MidpointRounding.AwayFromZero);
}

另外，原代码中int.TryParse(MDate.ToString("MM"), out int month)属于冗余操作，直接使用MDate.Month和MDate.Year即可获取月份和年份，既简洁又避免转换错误。

内容的提问来源于stack exchange，提问作者Cosmin