EF Query with OrderBy fails

c# entity-framework entity-framework-core linq

Question

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
1
2
10/18/2019 7:02:32 PM

Accepted Answer

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,
4
10/18/2019 7:37:55 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow