Any suggestions on how to make this LINQ query more efficient? I recently created the formattedDate variable as previously I was calling the GetDateInFormat multiple times.

    if (customerOrder.OrderLines
          .SelectMany(ol => ol.OrderDates)
          .Where(ol => ol.DateTypeId == "OrderPickUpFrom" && ol.ActualDate != null)
          .Any())
    {
        var date = customerOrder.OrderLines
          .SelectMany(ol => ol.OrderDates)
          .OrderBy(d => d.ActualDate)
          .FirstOrDefault(d => d.DateTypeId == "OrderPickUpFrom" && d.ActualDate != null)
          .ActualDate;

        if (date != null)
        {
            var formattedDate = _dateHelper.GetDateInFormat("DD/MM/YYYY", date);
            order.ArriveDate = formattedDate;
            order.EarliestShipDate = formattedDate;
            order.EarliestDeliveryDate = formattedDate;
            order.EarliestApptTime = _dateHelper.GetDateInFormat("HHMM", date);
        }
    }

The customerOrder is a customer Order class.

public class Order
{
    public Order();

    public List<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
    public OrderLine();

    public List<OrderDate> OrderDates { get; set; }
}

public class OrderDate
{
    public OrderDate();

    public DateTimeOffset? ActualDate { get; set; }
    public string DateTypeId { get; set; }
}

CodePudding user response：

Well,

• First of all let's get rid of two Linq queries in a row: Any() and then FirstOrDefault. We can execute just FirstOrDefault (or its equivalent) and if we get null we'll know that Any returns false (i.e. we have no items).

• Second, with OrderBy we sort the entire enumerable and then we drop all but one item. We are wasting resources. Aggregate is more economic way: we have no need to sort but scan the enumeration and return either null or smallest ActualDate.

Code:

var date = customerOrder
  .OrderLines
  .SelectMany(ol => ol.OrderDates)
  .Where(ol => ol.DateTypeId == "OrderPickUpFrom" && ol.ActualDate != null)
  .Select(ol => ol.ActualDate)
  .Aggregate((DateTimeOffset?) null,
     (s, a) => !s.HasValue || s.Value > a ? a : s);

if (date.HasValue) {
  var formattedDate = _dateHelper.GetDateInFormat("DD/MM/YYYY", date);
  
  order.ArriveDate = formattedDate;
  order.EarliestShipDate = formattedDate;
  order.EarliestDeliveryDate = formattedDate;
  order.EarliestApptTime = _dateHelper.GetDateInFormat("HHMM", date);
}

CodePudding user response：

first of all you can cat your execution time in almost half if you do not call database twice first you call it in the if:

customerOrder.OrderLines
            .SelectMany(ol => ol.OrderDates)
            .Where(ol => ol.DateTypeId == "OrderPickUpFrom" && ol.ActualDate != null).Any())

and than in true branch:

var date = customerOrder.OrderLines
                                .SelectMany(ol => ol.OrderDates)
                                .OrderBy(d => d.ActualDate)
                                .FirstOrDefault(d => d.DateTypeId == "OrderPickUpFrom" && d.ActualDate != null).ActualDate;

if you look at those they are virtually identical you can achieve same result using this:

var date = customerOrder.OrderLines
                                .SelectMany(ol => ol.OrderDates)
                                .OrderBy(d => d.ActualDate)
                                .Where(d => d.DateTypeId == "OrderPickUpFrom" && d.ActualDate != null)
.Select(x => x.ActualDate)
.FirstOrDefault();

now null value in date will serve you as bases for the if statement

if(date != null) {

   // do your stuff
}

Other than this you can create an index on the database that covers your query so containing DataTypeId and ActualDate.

CodePudding user response：

I would suggest using .SelectMany(), .Where() and .Min() as follows:

var date = customerOrder.OrderLines
    .SelectMany(ol => ol.OrderDates)
    .Where(ol =>
        ol.DateTypeId == "OrderPickUpFrom" &&
        ol.ActualDate != null)
    .Min(ol => ol.ActualDate);

.Min() will return null when the result from .Where() is empty.