The issue of SQL statements following Sum after Groupby on EF6.0.5

[pastespider]

File a bug

I need to use grouping and statistical queries, and there are the following cases

            var _query_frontid = _dbContext.FrontTable.Where(x => x.Age != 0).Select(x => x.Id);
            var _query = _dbContext.DateTable.Where(x => _query_frontid.Contains(x.FrontId))
                .GroupBy(x => x.FrontId)
                .Select(x => new
                {
                    FrontId = x.Key,
                    SunNum1 = x.Sum(y => y.Num1),
                    SunNum2 = x.Sum(y => y.Num2)
                });
            var sqlstring = _query.ToQueryString();

The values of sqlstring are as follows

SELECT p."FrontId", (
                SELECT COALESCE(SUM(p1."Num1"), 0)::INT
    FROM "PTDateTable" AS p1
    WHERE EXISTS(
        SELECT 1
        FROM "PTFrontTable" AS p2
        WHERE(p2."Age" <> 0) AND(p2."Id" = p1."FrontId")) AND(p."FrontId" = p1."FrontId")) AS "SunNum1", (
    SELECT COALESCE(SUM(p4."Num2"), 0)::INT
    FROM "PTDateTable" AS p4
    WHERE EXISTS(
        SELECT 1
        FROM "PTFrontTable" AS p5
        WHERE(p5."Age" <> 0) AND(p5."Id" = p4."FrontId")) AND(p."FrontId" = p4."FrontId")) AS "SunNum2"
FROM "PTDateTable" AS p
WHERE EXISTS(
    SELECT 1
    FROM "PTFrontTable" AS p0
    WHERE(p0."Age" <> 0) AND(p0."Id" = p."FrontId"))
GROUP BY p."FrontId"

if _query_frontid is not a IQueryable ,is a int[]

            var _query_frontid = new int[] {1,3,5,4,6,8,9,12,11,26 };
            var _query = _dbContext.DateTable.Where(x => _query_frontid.Contains(x.FrontId))
                .GroupBy(x => x.FrontId)
                .Select(x => new
                {
                    FrontId = x.Key,
                    SunNum1 = x.Sum(y => y.Num1),
                    SunNum2 = x.Sum(y => y.Num2)
                });
            var sqlstring = _query.ToQueryString();

The values of sqlstring are as follows

            --@___query_frontid_0 ={ '1', '3', '5', '4', '6', ... } (DbType = Object)
            SELECT p."FrontId", COALESCE(SUM(p."Num1"), 0)::INT AS "SunNum1", COALESCE(SUM(p."Num2"), 0)::INT AS "SunNum2"
            FROM "PTDateTable" AS p
            WHERE p."FrontId" = ANY(@___query_frontid_0)
            GROUP BY p."FrontId"

Question: How to optimize the previous query?

Or has the new EF version optimized this issue?

Thank you！

Include provider and version information

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.PostgreSql)
Target framework: (e.g. .NET 6.0.5)
Operating system:
IDE: (e.g. Visual Studio 2022 17.4)

[roji]

@autoinsidesoft if you want, you can execute the first query separately by adding ToListAsync as follows:

var _query_frontid = await _dbContext.FrontTable.Where(x => x.Age != 0).Select(x => x.Id).ToListAsync();

At this point _query_frontid will be a simple list that's passed to the second query, and you should see the same results. Note that this does result in two separate queries (and therefore two separate database roundtrips).

Otherwise, when running both as a single query, EF's query pipeline still has some inefficiencies when handling complex GroupBy and duplicates parts of the expression. I don't think there's anything you can do in that particular query to improve the situation (/cc @maumar).

[maumar]

@autoinsidesoft you can try the (somewhat hacky) workaround shown here:

#29593 (comment)

it seems to be working for several people.

[maumar]

dupe of #29593