Optimising Lamda Linq to SQL query with OrderBy

Question

I have the following lamda expression:

IEnumerable<Order> query
       = _ordersRepository.GetAllByFilter(
            o => 
                o.OrderStatus.OrderByDescending(os => os.Status.Date).First()
                    .Status.StatusType.DisplayName != "Completed"
                ||   
                o.OrderStatus.OrderByDescending(os => os.Status.Date).First()
                    .Status.Date > sinceDate
         ).OrderBy(o => o.DueDate);

As you can see, I'm having to order the collection twice within the main query (so three times in total) in order to perform my OR query.

1) Is the query optimiser clever enough to deal with this in an efficient way?

2) If not, how can I rewrite this expression to only order by once, but keeping with lambda syntax?

This is linked to this previous question, which explains the query in a bit more detail if the above code isn't clear.

Answer 1

Regarding your first point: You can see the SQL that is generated by subscribing to the output of the DatabaseContext object. This is usually in a property called Log.

As for optimising your query, try the following (I've not tested it so I don't know if it will work)

IEnumerable<Order> query
   = _ordersRepository.GetAllByFilter(
        o =>
            o.OrderStatus.Max(os => os.Status.Date).Any(os =>
                os.Status.StatusType.DisplayName != "Completed"
                || os.Status.Date > sinceDate)
     ).OrderBy(o => o.DueDate);

Hopefully that will only perform the subquery once, and also performs a max rather than an order by with top 1.

Answer 2

1) Is the query optimiser clever enough to deal with this in an efficient way?

You can get the SQL for this query (one way is to use the SQL profiler), and then ask SQL Studio for the execution plan. Unless you do this, there is no way to know what the optimizer thinks. My guess is the answer is "no".

2) If not, how can I rewrite this expression to only order by once, but keeping with lambda syntax?

Like this:

IEnumerable<Order> query = _ordersRepository.GetAllByFilter( o =>
  o.OrderStatus
    .OrderByDescending(os => os.Status.Date)
    .Take(1)
    .Any(os => os.Status.StatusType.DisplayName != "Completed"
      || os.Status.Date > sinceDate)
})
.OrderBy(o => o.DueDate);