Help Aggregating Data on an IGrouping

[jacobhjustice]

Ask a question

I am having trouble selecting aggregate data after a group by.

For reference, here is SQL that emulates what I am trying to do:

select   
ut.teamid as TeamId, sum(p.value) as Value
from userteams ut
join users u on u.id=ut.UserId
join points p on p.UserId = u.id
group by ut.teamid

Here is my code currently:

var rankedTeamForMonth = query // Query is a query on userteams (same model as from the SQL above)
   .GroupBy(x => x.TeamId)
   .Select(x =>
          new 
           {
                    Value = x.Sum(ut => ut.User.Points.Sum(y => y.Value)),
                    TeamId = x.Key,
           }
   );

I get an error that Query could not be translated

Stack Trace:

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

EF Core version: 5.0.8 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 5.0 Operating system: Windows 10 IDE: Rider

[jacobhjustice]

Note that the same happens if I try to use SelectMany on Points for Value: Value = x.SelectMany(y => y.User.Points).Sum(z => (int?)z.Value) ?? 0,

Gives the following on ToList:

System.InvalidOperationException: The LINQ expression 'DbSet<UserTeam>()
    .Join(
        inner: DbSet<User>(), 
        outerKeySelector: u => EF.Property<Nullable<int>>(u, "UserId"), 
        innerKeySelector: u0 => EF.Property<Nullable<int>>(u0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<UserTeam, User>(
            Outer = o, 
            Inner = i
        ))
    .Where(u => u.Inner.OrganizationId == __organizationId_3)
    .Where(u => u.Outer.BeginAt <= __activeAt_4 && u.Outer.InactiveAt == null || u.Outer.InactiveAt > __activeAt_5)
    .Where(u => MaterializeCollectionNavigation(
        Navigation: User.AssignedModules,
        subquery: DbSet<UserModule>()
            .Where(u1 => EF.Property<Nullable<int>>(u.Inner, "Id") != null && object.Equals(
                objA: (object)EF.Property<Nullable<int>>(u.Inner, "Id"), 
                objB: (object)EF.Property<Nullable<int>>(u1, "UserId"))).Exists(y => y.IsGamified && y.Assigned && y.OpenAt <= __at_6 && y.CloseAt >= __at_6))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Wildsparq.Models.Repositories.UserTeamRepository.GetQueryUserPointsByTeamForMonth(Int32 orgId, DateTime month) in C:\Users\Jacob Justice\Documents\dev\wildsparq\Wildsparq.Models\Repositories\UserTeamRepository.cs:line 295
   at Wildsparq.Models.Repositories.UserTeamRepository.GetUserPointsByTeamByMonth(Int32 orgId, List`1 monthSet) in C:\Users\Jacob Justice\Documents\dev\wildsparq\Wildsparq.Models\Repositories\UserTeamRepository.cs:line 307
   at Wildsparq.Api.Logic.Handlers.TeamHandler.RankedTeams(Int32 organizationId, DateTime startDate, DateTime endDate) in C:\Users\Jacob Justice\Documents\dev\wildsparq\Wildsparq.Api.Logic\Handlers\TeamHandler.cs:line 85
   at Wildsparq.Api.Logic.Handlers.TeamHandler.LoadedRankedTeams(Int32 organizationId, DateTime startDate, DateTime endDate) in C:\Users\Jacob Justice\Documents\dev\wildsparq\Wildsparq.Api.Logic\Handlers\TeamHandler.cs:line 132
   at Wildsparq.Models.GraphQL.WildsparqQuery.<SetupTeamQueries>b__14_3(ResolveConnectionContext`1 context) in C:\Users\Jacob Justice\Documents\dev\wildsparq\Wildsparq.Models.GraphQL\WildsparqQuery.cs:line 793
   at GraphQL.Builders.ConnectionBuilder`2.<>c__DisplayClass19_0.<Resolve>b__0(ResolveFieldContext context)
   at GraphQL.Resolvers.FuncFieldResolver`1.Resolve(ResolveFieldContext context)
   at GraphQL.Resolvers.FuncFieldResolver`1.GraphQL.Resolvers.IFieldResolver.Resolve(ResolveFieldContext context)
   at GraphQL.Instrumentation.MiddlewareResolver.Resolve(ResolveFieldContext context)

If I just hardcode 1 for Value or a non-nested sum like Value = x.Sum(ut => ut.User.Id), everything executes properly.

[roji]

@jacobhjustice can you please submit a runnable code sample producing the translation exception? It's difficult to know exactly what you're doing from a the query fragment above.

[jacobhjustice]

         var rankedTeamForMonth = this.DbSet // UserTeams
                .Include(x => x.User) // UserTeams.User
                .ThenInclude(x => x.Points) // UserTeam.User.Points
                .GroupBy(x => x.TeamId) // UserTeam.TeamId
                .Select(x =>
                new
                {
                    Value = x.Sum(ut => ut.User.Points.Sum(y => y.Value)), // Sum of user's point values, summed for all users
                    Id = x.Key,
                }
            );

            return rankedTeamForMonth.ToList();

@roji Here is the query that throws exception when materialized to list. Is this what you mean?

[jacobhjustice]

It there any update on this? Really I just want to know how to do nested sums after a groupBy

[roji]

@jacobhjustice the above still isn't a runnable code sample - the model is missing. I've tried to recreate a runnable sample (see below) based on the info above, hopefully this matches your configuration.

With 5.0.10, this indeed generates a translation failure. With 6.0.0-rc.1, EF does translate the query:

SELECT COALESCE(SUM((
    SELECT COALESCE(SUM([p].[Value]), 0)
    FROM [Point] AS [p]
    WHERE [u0].[Id] IS NOT NULL AND ([u0].[Id] = [p].[UserId]))), 0) AS [Value], [u].[TeamId] AS [Id]
FROM [UserTeams] AS [u]
LEFT JOIN [User] AS [u0] ON [u].[UserId] = [u0].[Id]
GROUP BY [u].[TeamId]

However, SQL Server errors on this query with:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Both PostgreSQL and Sqlite successfully execute this query, so this seems to be a SQL Server limitation.

Repro

```c# await using var ctx = new BlogContext(); await ctx.Database.EnsureDeletedAsync(); await ctx.Database.EnsureCreatedAsync(); var rankedTeamForMonth = ctx.UserTeams // UserTeams .Include(x => x.User) // UserTeams.User .ThenInclude(x => x.Points) // UserTeam.User.Points .GroupBy(x => x.TeamId) // UserTeam.TeamId .Select(x => new { Value = x.Sum(ut => ut.User.Points.Sum(y => y.Value)), // Sum of user's point values, summed for all users Id = x.Key, }); Console.WriteLine(rankedTeamForMonth.ToQueryString()); var results = rankedTeamForMonth.ToList(); public class BlogContext : DbContext { public DbSet UserTeams { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0") //.UseNpgsql(@"Host=localhost;Username=test;Password=test") .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging(); } public class UserTeam { public int Id { get; set; } public string Name { get; set; } public int TeamId { get; set; } public User User { get; set; } } public class User { public int Id { get; set; } public List Points { get; set; } } public class Point { public int Id { get; set; } public int Value { get; set; } public User User { get; set; } } ```

[jacobhjustice]

@roji Thank you for the response. I apologize, I did not fully understand what you expected by a runnable code sample, but appreciate you taking the time to work through that.

It seems odd that SqlServer would have any issue with translating the query, especially since I can write a select on sum that is directly translated. I suppose this may mean my best approach would be to just write a SQL query directly to be executed to do what I want then.

[roji]

@jacobhjustice with EF Core 6.0, you can achieve what you want with the following query:

var rankedTeamForMonth = ctx.UserTeams
    .GroupBy(x => x.TeamId)
    .Select(x =>
        new
        {
            Value = x.SelectMany(ut => ut.User.Points).Sum(p => p.Value),
            Id = x.Key,
        });

In other words, instead of a nested Sum within a sum, simply flatten everything first and then perform a single Sum. I'd advise using EF Core 6.0.0 rc1 (which is production-ready version with a go-live license).