Let's say I have Companies which have Customers which have Orders. In one search query I want to set the order in a way that brings the "companies with latest orders first". (Hope this is clear enough).
.OrderByDescending(x => x.Customers.Max(c => c.Orders.Any() ? y.Orders.Max(o => (DateTime?) o.DateCreatedUtc) : DateTime.MinValue) )
Currently I'm getting this warning: The LINQ expression 'Max()' could not be translated and will be evaluated locally
Can we rewrite this to be evaluated on the server?
Initially (my apologies) it looks exactly the same as using Max() in Orderby - make sure you use nullable overloads of outer (essentially every)
Min calls and you get translation.
Using nullable overloads is still a must. However, there is a hidden trap. - currently EF Core can translate aggregate methods only if they use simple member selector with optional cast. Any other expression is causing client evaluation regardless of the type being nullable or not.
Fortunately there is simple solution which works for all standard aggregates except
Average - flatten the aggregate set using
SelectMany (could be more than one and could be combined with reference navigation properties) to the level containing the aggregating entity and apply the aggregate function on that set.
Also make sure not using conditional or similar expressions inside the aggregate selector. The good thing about nullable
Min overloads is that it returns
null for empty set, so there is no need to include
nulls normally come first in order, so normally you don't need special sentinel values. And even if you do need them, make sure to apply them using C#
?? operator after the aggregate call.
With all that being said, the translatable version of the sample snippet is
.OrderByDescending(x => x.Customers.SelectMany(c => c.Orders).Max(o => (DateTime?)o.DateCreatedUtc)) // ^ ^ // drill down convert to nullable