LINQ sum of column values excluding weekends-CodePudding

I'm trying to calculate the hours that fall during the week at a different rate than the ones worked during the weekend. This is how I was hoping to accomplish it:

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);
        }

The error I'm getting:

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

How can I get around this?

CodePudding user response：

The DayOfWeek enum does not exist on the server side, which is the root cause of the exception. The following are two workarounds to solving the problem. The first uses a list of weekday ordinals (0 and 6), and the second checks the ordinals directly in the conditional (analogous to your original approach).

The examples below work for Entity Framework. For EFCore support, do a find and replace with DbFunctions.DiffDays and EF.Functions.DateDiffDay as the respective criteria.


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);
}