EF Query with OrderBy fails

user_8953356 0
Viewed 673

I'm sure there is a simple answer to this, but I can't seem to nail it down.

I have the following code,

internal override async Task FetchDataModel()
{
    var dateCutoff = DateTime.Today.AddDays(-PeriodInDays);

     var query = this.DB.Jobs
         .IsDelivered()
         .Where(x => x.DeliveredTime.Value.Date >= dateCutoff)
         .GroupBy(x => new { x.Pet.OwnerId, x.Pet.Owner.First, x.Pet.Owner.Last })
         .Select(x => new MVPCustomerDTO
         {
             OwnerId = x.Key.OwnerId,
             OwnerName = $"{x.Key.First} {x.Key.Last}",
             JobCount = x.Count(),
             TotalSpend = x.Sum(a => (a.Price ?? 0M) - a.Discount) 
                      + this.DB.JobExtras
                               .Where(a => a.Job.Pet.OwnerId == x.Key.OwnerId)
                               .Where(a => a.Job.DeliveredTime.Value.Date >= dateCutoff)
                               .Sum(a => a.Price),
         })
/*==>Problem Line*/ .OrderByDescending(x => x.TotalSpend)
         .Take(25);

          var result = await query
                      .ToListAsync()
                      .ConfigureAwait(false);

          this.Model = result;
}

I cannot figure out how to get around this exception. Its EF core 3.0 code. The OrderByDescending is causing the issue. If I remove it, the query works. I thought maybe it had to do with the TotalSpend, so trying to wrap my head around this, I removed the TotalSpend field, and changed the OrderByDescending to orderby JobCount, and got the same results. I'm sure this is related to server side translation, but I can't seem to get my head wrapped around this to get around the issue.

Help me see the light please!?!

The exception being throw is:

 System.ArgumentNullException: Value cannot be null. (Parameter 'key')
    at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
    at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
    at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.GetMappedProjection(ProjectionMember projectionMember)
    at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitExtension(Expression extensionExpression)
    at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
    at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
    at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
    at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateOrderBy(ShapedQueryExpression source, LambdaExpression keySelector, Boolean ascending)
    at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
    at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
    at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
    at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
    at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
    at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
    at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
    at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
    at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
    at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
    at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    at GroomShop.Web.ReportGenerators.MVPCustomerReport.FetchDataModel() in C:\Workspace\GroomShop\GroomShop.Web\ReportGenerators\MVPCustomerReport.cs:line 203
entity-framework-core entity-framework linq c#
3 Answers

The exception is misleading. The problem is the interpolated string inside the projection (Select) which of course cannot be translated to SQL and should generate client evaluation exception telling you that. Instead (most likely EF Core implementation bug) you get the aforementioned misleading exception.

The solution is of course to avoid non translatable constructs/methods like interpolated strings, Format / ToString methods with format strings / culture info arguments. Instead, use string concatenation (and parameterless ToString methods if needed), e.g. replace

OwnerName = $"{x.Key.First} {x.Key.Last}",

with

OwnerName = x.Key.First + " " + x.Key.Last,
user_52025630

The reason why your query fails will probably be that Jobs.Pet or Jobs.Pet.Owner is being null within certain database entries.

When you then access x.Key.OwnerId or x.Key.First or x.Key.Last inside your LINQ Select() it will throw an exception

user_104309440

When the query uses OrderByDescending(), the query is looking at more rows than simply taking the Top(25). OrderBy has to hit all the rows in question in order to order them. One possibility is that you were getting lucky that the Top(25) rows didn't hit any nulls.

If this is the case, then nulls will either have to be handled or excluded from the query, depending on the requirements. Otherwise, something else is going on.

user_107727330
Related