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

When you call aggregate methods (Average, Min, Max) in a LINQ query using a non-nullable type, it has no choice but to evaluate it locally.

To avoid this, cast your Max value to a nullable type and it will be evaluated in the database.

Assuming CreateDate is of type DateTime, casting it to DateTime? (nullable) should work.

Here's what your query should look like:

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

This answer referenced from official Github Repo

Answer 2

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.