using Max() in Orderby

Question

I have this line which runs till I enabled RelationalEventId.QueryClientEvaluationWarning.

Here let's say what I want to do is to sort the results (customers) based on their latest order date.

.OrderByDescending(x=>x.orders.Max(y=>y.CreateDate))

After I configure the context as follow I realised the Max() is not converted to TSql.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    optionsBuilder.ConfigureWarnings(warning =>
    {
        warning.Throw(RelationalEventId.QueryClientEvaluationWarning);
    });
}

The error:

InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'Max()' could not be translated and will be evaluated locally.

I assume calculating the max locally is against having a better performance. Is there any way to calculate the max on SQL Server?

Answer 1

If you have EF Core Logging enabled, you would see the following warning:

=> Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor

Possible unintended use of a potentially throwing aggregate method (Min, Max, Average) in a subquery. Client evaluation will be used and operator will throw if no data exists. Changing the subquery result type to a nullable type will allow full translation.

Basically they are trying to preserve the throwing behavior of the aforementioned aggregate methods in LINQ to Objects.

And the solution is in the last sentence. e.g. if the type of CreateDate is DateTime, then

.OrderByDescending(x => x.orders.Max(y => (DateTime?)y.CreateDate))

will be translated to SQL.