Improve GroupBy translation for JSON-owned properties to avoid N scalar subqueries

[mohsen-mars]

What problem are you trying to solve?

Problem

When grouping and aggregating owned JSON properties, EF Core translates each aggregate into a correlated scalar subquery instead of a single GROUP BY aggregation.

This results in multiple scans over the same dataset and significantly degrades performance for large datasets.

---

Example Model

public class InvoiceDb
{
    public int Id { get; set; }
    public SummaryDb Summary { get; set; }
}

[NotMapped]
public class SummaryDb
{
    public long TotalAmount { get; set; }
    public long TotalDiscount { get; set; }
}

Mapping (JSON column)

builder.OwnsOne(p => p.Summary, b =>
{
    b.ToJson();
    b.Property(x => x.TotalAmount);
    b.Property(x => x.TotalDiscount);
});

LINQ Query

var result = await dbContext.Invoices
    .GroupBy(x => x.CreatedAt.Date)
    .Select(g => new
    {
        Date = g.Key,
        TotalAmount = g.Sum(x => x.Summary.TotalAmount),
        TotalDiscount = g.Sum(x => x.Summary.TotalDiscount),
    })
    .ToListAsync();

Generated SQL (simplified)

SELECT [s].[Key],
    (SELECT SUM(JSON_VALUE(...TotalAmount...)) ...) AS TotalAmount,
    (SELECT SUM(JSON_VALUE(...TotalDiscount...)) ...) AS TotalDiscount
FROM (...)
GROUP BY [s].[Key];

Each aggregate is translated into a separate correlated scalar subquery, causing multiple scans of the same data.

Impact

This affects reporting and analytical workloads and makes aggregation over JSON-owned properties impractical for large datasets.
It also limits adoption of JSON-owned types in real-world reporting scenarios where server-side aggregation is required.

Describe the solution you'd like

Suggested Behavior

Translate to a single GROUP BY with all JSON_VALUE projections in one pass, e.g.:

SELECT CAST(i.InvoiceDate AS date) AS [Date],
       SUM(CAST(JSON_VALUE(i.Summary, '$.TotalAmount') AS bigint)) AS TotalAmount,
       SUM(CAST(JSON_VALUE(i.Summary, '$.TotalDiscount') AS bigint)) AS TotalDiscount
FROM Invoices i
GROUP BY CAST(i.InvoiceDate AS date);

Environment

• EF Core version: 10.0.0
• Database provider: Microsoft.EntityFrameworkCore.SqlServer
• .NET SDK: .NET 10.0

[roji]

AI Analysis

Confirmed behavior

The issue is confirmed on EF Core 10.0.3 / SQL Server. When using GroupBy with aggregation over JSON-owned properties, each aggregate is translated as a separate correlated scalar subquery instead of an inline aggregation within the GROUP BY:

Generated SQL (2 subqueries for 2 aggregates):

SELECT [i0].[Key] AS [Date],
    (SELECT COALESCE(SUM(CAST(JSON_VALUE([i1].[c], '$.TotalAmount') AS bigint)), ...) FROM (...) WHERE ...) AS [TotalAmount],
    (SELECT COALESCE(SUM(CAST(JSON_VALUE([i3].[c], '$.TotalDiscount') AS bigint)), ...) FROM (...) WHERE ...) AS [TotalDiscount]
FROM (...) AS [i0]
GROUP BY [i0].[Key]

Desired SQL (single GROUP BY, inline aggregation):

SELECT CONVERT(date, [i].[CreatedAt]) AS [Date],
       SUM(CAST(JSON_VALUE([i].[Summary], '$.TotalAmount') AS bigint)) AS TotalAmount,
       SUM(CAST(JSON_VALUE([i].[Summary], '$.TotalDiscount') AS bigint)) AS TotalDiscount
FROM [Invoices] AS [i]
GROUP BY CONVERT(date, [i].[CreatedAt])

Additional finding

This is not specific to JSON-owned types. I also tested with:

• Regular scalar properties on the entity → produces efficient single GROUP BY with inline SUM() ✅
• Table-splitting owned types (non-JSON OwnsOne) → produces same N-subquery pattern ❌
• JSON-owned types → produces same N-subquery pattern ❌

So the issue affects all owned types when aggregating their properties in a GroupBy.

Related issues

• Aggregate function on owned type fails in EF8 #36990 – "Aggregate function on owned type fails in EF8" – a different but related issue where certain aggregates (e.g. Count with predicate) on owned types throw an exception rather than producing suboptimal SQL. Already milestoned for 11.0.0 and assigned to @roji. The fix for Aggregate function on owned type fails in EF8 #36990 may also be the natural place to address this suboptimal translation pattern.
• Aggregate function on owned type fails in EF7 #29201 – "Aggregate function on owned type fails in EF7" – the predecessor of Aggregate function on owned type fails in EF8 #36990, fixed for EF7 but partially regressed.
• Query: further improve translation of queries with Distinct or Groupby in correlated subquery, when entity identifiers are not easy to obtain #26077 – "Query: further improve translation of queries with Distinct or GroupBy in correlated subquery" – a general backlog item about improving GroupBy/correlated subquery translation.

This is filed as a feature request (not a bug), and the behavior is technically correct — it just produces suboptimal SQL with N scans instead of 1.

[roji]

Thanks. We're aware that there are various GroupBy SQL issues, and I hope to do a concerted push on improving the quality of GroupBy translation - I'll definitely take a look at this when that happens.

[mohsen-mars]

Thanks for the clarification and for tracking this.
Appreciate the work on improving GroupBy translation.