Sum function - cannot perform an aggregate function on an expression containing an aggregate or a subquery

[danghyan]

I transferred my models and code from the old .net to the new .net core. The same code works there and does not work here.

db.Lecture.Where(t => t.CourseId == id && t.isActive == true && t.ChapterGrpoup.Any(cg => cg.Chapter.Any(c => c.PerLevel >= Perlevel && c.isActive == true && c.isInnerPortal) && cg.isActive == true))
  .Select(x => new
  {
	  id = x.LecId,
	  x.Name,
	  x.Description,
	  x.Image,
	  nextlevel = 0,
	  BackCourseId = id,
	  access = true,
	  NotReadCount = (int?)x.ChapterGrpoup.Sum(cg => cg.Chapter.Count(e => e.EmployeeChapters.Where(r => r.EmployeeId == EmployeeId && r.dtEnd != null).Count() == 0 && e.ChapterGrpoup.IsNeed && e.PerLevel >= Perlevel && e.isActive == true && e.isInnerPortal))
  }).ToList(); 

The problem is in NotReadCount param. For verification, I highlighted this part of the code:

 var test = (int?)db.ChapterGrpoup.Sum(cg => cg.Chapter.Count(e => e.EmployeeChapters.Where(r => r.EmployeeId == EmployeeId && r.dtEnd != null).Count() == 0 && e.ChapterGrpoup.IsNeed && e.PerLevel >= Perlevel && e.isActive == true && e.isInnerPortal))

This efcore generate this:

SELECT SUM((
    SELECT COUNT(*)
    FROM [Academy].[CourseChapter] AS [c]
    LEFT JOIN [Academy].[CourseChapterGrpoup] AS [c0] ON [c].[ChaptersGroupId] = [c0].[ChaptersGroupId]
    WHERE ([c1].[ChaptersGroupId] = [c].[ChaptersGroupId]) AND ((((((
        SELECT COUNT(*)
        FROM [Academy].[EmployeeChapters] AS [e]
        WHERE ([c].[ChapId] = [e].[ChapId]) AND (([e].[EmployeeId] = @__EmployeeId_0) AND [e].[dtEnd] IS NOT NULL)) = 0) AND ([c0].[IsNeed] = CAST(1 AS bit))) AND ([c].[PerLevel] >= @__Perlevel_1)) AND ([c].[isActive] = CAST(1 AS bit))) AND ([c].[isInnerPortal] = CAST(1 AS bit)))))
FROM [Academy].[CourseChapterGrpoup] AS [c1]

Technical information
.net Core: 3.1
EfCore: 3.1.3

Eroor(sql):
cannot perform an aggregate function on an expression containing an aggregate or a subquery

[smitpatel]

It is T-Sql limitation. We cannot translate it to server correctly. If you believe that there is alternate translation which we can use, we can improve.

[danghyan]

@smitpatel yes, sum fo count is wrong for sql, but same code

var test = (int?)db.ChapterGrpoup.Sum(cg => cg.Chapter.Count(e => e.EmployeeChapters.Where(r => r.EmployeeId == EmployeeId && r.dtEnd != null).Count() == 0 && e.ChapterGrpoup.IsNeed && e.PerLevel >= Perlevel && e.isActive == true && e.isInnerPortal))

in 6.2.0 in old project make currect sql

SELECT 
    [GroupBy3].[A1] AS [C1]
    FROM ( SELECT 
        SUM([Extent1].[A1]) AS [A1]
        FROM ( SELECT 
            (SELECT 
                COUNT(1) AS [A1]
                FROM ( SELECT 
                    [Project1].[PerLevel] AS [PerLevel], 
                    [Project1].[isActive] AS [isActive], 
                    [Project1].[IsNeed] AS [IsNeed], 
                    (SELECT 
                        COUNT(1) AS [A1]
                        FROM [Academy].[EmployeeChapters] AS [Extent4]
                        WHERE ([Project1].[ChapId] = [Extent4].[ChapId]) AND ([Extent4].[EmployeeId] = 1445) AND ([Extent4].[dtEnd] IS NOT NULL)) AS [C1]
                    FROM ( SELECT 
                        [Extent2].[ChapId] AS [ChapId], 
                        [Extent2].[PerLevel] AS [PerLevel], 
                        [Extent2].[isActive] AS [isActive], 
                        [Extent3].[IsNeed] AS [IsNeed]
                        FROM  [Academy].[CourseChapter] AS [Extent2]
                        LEFT OUTER JOIN [Academy].[CourseChapterGrpoup] AS [Extent3] ON [Extent2].[ChaptersGroupId] = [Extent3].[ChaptersGroupId]
                        WHERE [Extent1].[ChaptersGroupId] = [Extent2].[ChaptersGroupId]
                    )  AS [Project1]
                )  AS [Project2]
                WHERE (0 = [Project2].[C1]) AND ([Project2].[IsNeed] = 1) AND ([Project2].[PerLevel] >= 1) AND (1 = [Project2].[isActive])) AS [A1]
            FROM [Academy].[CourseChapterGrpoup] AS [Extent1]
        )  AS [Extent1]
    )  AS [GroupBy3]

so

Sum(select count()) from table

is wong, but

Sum()
from (select count(*) from talbe)

will work ok

[asbjornu]

This just bit me as well. If we take the student and grade example, the following query should be valid:

var gradesWithStudentCount = await DataContext.Grades
    .Include(grade => grade.Students)
    .Select(grade => new
    {
        grade.GradeId,
        grade.GradeName,
        grade.Section,
        StudentCount = grade.Students.Count()
    })
    .ToListAsync();

Resulting in the following valid SQL:

select  g.GradeId
,       g.GradeName,
,       g.Section
,       (
            select count(*)
            from Students s
            where s.Grade_GradeId = g.GradeId
        ) as StudentCount
from Grades g

Currently, this does not seem to be possible.

[roji]

Duplicate of #34256

[roji]

Am going to use #34256 to track working around the SQL Server limitation in EF.