c# - LINQ to Entities does not recognize the method in query

Question

I have the following code that gives me an error saying that LINQ to Entities does not recognize the method GetShippingHour:

private IEnumerable<OrderDTO> GetAllFixedOrders(DateTime date)
    {
        var clientSet = _context.Set<Client>();
        var orderSubstitutes = _context.Set<OrderSubstitution>()
                                .Include(o=>o.Order.Client)
                                .ForCurrentDay(date)
                                .Select(os => new { os.OrderId, os.FixedOrderId });

        var orderList = _context.Set<FixedOrder>()
            .Include(fo => fo.Client)
           .ForCurrentDay(date)
           .Where(fo => !fo.Client.IsInactive)
           .ConsideringOrderSubstitution(orderSubstitutes.Select(os => os.FixedOrderId).ToList())
           .Join(clientSet
               , o => o.ClientId
               , c => c.Id
               , (o, c) =>
                   new OrderDTO
                   {
                       OrderId = orderSubstitutes.Where(os => os.FixedOrderId == o.Id).Select(os => os.OrderId).FirstOrDefault() ?? 0,
                       ClientId = c.Id,
                       ClientName = c.Name,
                       HasFixedOrders = true,
                       FixedOrderId   = o.Id,
                       ShippingHour = GetShippingHour(c,date),
                       HasSpecialProducts = false
                   }
           ).ToList();

        return orderList;
    }

    private string GetShippingHour(Client c, DateTime date)
    {
        var dayOfWeek = date.DayOfWeek.ToString();
        string result;
        switch (dayOfWeek)
        {
            case "Sunday":
                result = c.ShippingHourSunday ?? c.ShippingHour;
                break;
            case "Monday":
                result = c.ShippingHourMonday ?? c.ShippingHour;
                break;
            case "Tuesday":
                result = c.ShippingHourTuesday ?? c.ShippingHour;
                break;
            case "Wednesday":
                result = c.ShippingHourWednesday ?? c.ShippingHour;
                break;
            case "Thursday":
                result = c.ShippingHourThursday ?? c.ShippingHour;
                break;
            case "Friday":
                result = c.ShippingHourFriday ?? c.ShippingHour;
                break;
            case "Saturday":
                result = c.ShippingHourSaturday ?? c.ShippingHour;
                break;
            default:
                result = c.ShippingHour;
                break;
        }
        return result;
    }

The problem is that, depending on the day of the week, I need to get the value for ShippingHour from a different field of the Clients table. The problem is that LINQ to Entities is trying to translate my method. I don't know how to rewrite this query to do the required results.

Answer 1

    var orderList = _context.Set<FixedOrder>()
        .Include(fo => fo.Client)
       .ForCurrentDay(date)
       .Where(fo => !fo.Client.IsInactive)
       .ConsideringOrderSubstitution(orderSubstitutes.Select(os => os.FixedOrderId).ToList())
       .Join(clientSet
           , o => o.ClientId
           , c => c.Id
           , (o, c) =>
               new 
               {
                   OrderId = orderSubstitutes.Where(os => os.FixedOrderId == o.Id).Select(os => os.OrderId).FirstOrDefault() ?? 0,
                   Client = c,
                   HasFixedOrders = true,
                   FixedOrderId   = o.Id,
                   HasSpecialProducts = false
               }
       ).ToList()
       .Select(t=>new OrderDTO {
                   OrderId = t.OrderId,
                   ClientId = t.Client.Id,
                   ClientName = t.Client.Name,
                   HasFixedOrders = t.HasFixedOrders,
                   FixedOrderId   = t.FixedOrderId,
                   ShippingHour = GetShippingHour(t.Client,date),
                   HasSpecialProducts = t.HasSpecialProducts
       }).ToList();