The LINQ expression could not be translated

[lenniebriscoe]

I am using Microsoft.EntityFrameworkCore Version 3.1.3:

I have the following object:

public class OrganisationMention
{
    public int Id { get; set; }
    public int OrganisationId { get; set; }
    public Organisation Organisation { get; set; }
    public int PublicationId { get; set; }
    public Publication Publication { get; set; }
    public int LocationId { get; set; }
    public Location Location { get; set; }
    public int PracticeAreaId { get; set; }
    public PracticeArea PracticeArea { get; set; }
    public int Count { get; set; }
    public int MarketAverage { get; set; }
}

I want to aggregate a list of OrganisationMentions by Publication\Location\PracticeArea to get a list of RelatedOrganisationMentions objects with a Sum of the Count like the following:

public class RelatedOrganisationMentions
{
    public int PublicationId { get; set; }
    public Publication Publication { get; set; }
    public int LocationId { get; set; }
    public Location Location { get; set; }
    public int PracticeAreaId { get; set; }
    public PracticeArea PracticeArea { get; set; }
    public int Count { get; set; }
    public int MarketAverage { get; set; }
}

If I use the following LINQ query I get a horrid error (below) relating to the projection of the Publication\Location\PracticeArea Objects. If I omit them then the query returns fine. I do however want them back in the aggregated result and can guarantee that each Publication object will be the same entity if the PublicationId is the same. This seems to me a fairly basic aggregation and projection, am I doing something wrong?

Thanks in advance!

  IQueryable<RelatedOrganisationMentions> relatedOrganisationMentions = collection
            .GroupBy(m => new { m.LocationId, m.PublicationId, m.PracticeAreaId })
            .Select(am => new RelatedOrganisationMentions
            {
                PracticeArea = am.First().PracticeArea, //Causes long and horrid error
                Location = am.First().Location,         //Causes long and horrid error
                Publication = am.First().Publication,   //Causes long and horrid error
                PracticeAreaId = am.Key.PracticeAreaId,
                PublicationId = am.Key.PublicationId,
                LocationId = am.Key.LocationId,
                Count = am.Sum(x => x.Count)
            })
        .OrderBy(r => r.Publication.Description)
        .ThenBy(r => r.PracticeArea.Description);

    return await relatedOrganisationMentions.ToListAsync(token).ConfigureAwait(false);

The error I get is:

System.InvalidOperationException : The LINQ expression '(GroupByShaperExpression:
KeySelector: new { 
    LocationId = EntityMaterializerSource.TryReadValue<int>(grouping.Key, 0, Property: OrganisationMention.LocationId (int) Required FK Index), 
    PublicationId = EntityMaterializerSource.TryReadValue<int>(grouping.Key, 1, Property: OrganisationMention.PublicationId (int) Required FK Index), 
    PracticeAreaId = EntityMaterializerSource.TryReadValue<int>(grouping.Key, 2, Property: OrganisationMention.PracticeAreaId (int) Required FK Index)
 }, 
ElementSelector:(EntityShaperExpression: 
    EntityType: OrganisationMention
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Translate(InMemoryQueryExpression queryExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 Chambers.RankingDashboard.Repositories.OrganisationMentions.OrganisationMentionsRepository.GetByQuery(OrganisationSpecificQuery query, CancellationToken token) in C:\dev\myaccount-ranking-dashboard\app\src\Chambers.RankingDashboard.Repositories\OrganisationMentions\OrganisationMentionsRepository.cs:line 81
   at Chambers.RankingDashboard.Repositories.Tests.MentionsRepositoryTests.GetByQuery_should_filter_by_location() in C:\dev\myaccount-ranking-dashboard\app\tests\Chambers.RankingDashboard.Repositories.Tests\MentionsRepositoryTests.cs:line 33
--- End of stack trace from previous location where exception was thrown ---

Steps to reproduce

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .netstandard2.1
Operating system: Windows 10
IDE: Visual Studio Enterprise 2019 16.6.3

[smitpatel]

Duplicate of #12088