A single non-foldable grouped aggregate forces all sibling navigation aggregates in the same projection into correlated subqueries

Start by reading the EF Core query translation tests and the grouped aggregate translation entry points; the issue does not name repository files or tests. Run the supplied repro with the SQL Server provider and compare generated SQL with and without the non-foldable aggregate. Done means foldable navigation aggregates remain in the flat grouped query when a non-foldable aggregate is present, with regression coverage for both repro shapes.

Bug description

Summary

In EF Core 11, when a GroupBy(...).Select(...) projection mixes:

• (a) aggregates over a to-one navigation value that normally fold into a flat JOIN + GROUP BY (e.g. g.Average(x => x.Author.Rating)), and
• (b) any aggregate EF cannot express inline in that flat grouping,

then every aggregate of kind (a) in the projection is translated as a per-group correlated subquery instead of folding. The non-foldable aggregate is not the only thing that correlates — its mere presence drags the otherwise-foldable siblings onto the correlated-subquery strategy ("co-presence poisoning").

The trigger is any non-foldable aggregate, not a specific operator. Observed triggers include:

• g.Select(x => x.Value).Distinct().Count() (distinct count)
• g.Select(x => x.Value).Sum() / .Average() / .Max() — the select-then-aggregate form (as opposed to the foldable selector overload g.Sum(x => x.Value))
• any aggregate shape the translator can't inline alongside the joined aggregates in a single GROUP BY

Minimal repro

Model: Blog (one-to-one Author nav via AuthorId), grouped by Blog.CategoryId.

var query = db.Set<Blog>()
	.GroupBy(x => x.CategoryId)
	.Select(g => new
	{
		Key          = g.Key,
		AvgRating    = g.Average(x => x.Author!.Rating),  // foldable nav aggregate
		MaxRating    = g.Max(x => x.Author!.Rating),      // foldable nav aggregate
		NonFoldable  = g.Select(x => x.BlogId).Distinct().Count()
						// or: g.Select(x => x.Author!.Rating).Average()  (select-then-aggregate)
	});

var sql = query.ToQueryString();

Actual SQL (EF Core 11.0.0) — siblings correlated

With the distinct-count trigger, the distinct count itself folds inline as COUNT(DISTINCT ...), yet both foldable nav aggregates are pushed into correlated subqueries:

SELECT [b].[CategoryId] AS [Key], (
	SELECT AVG(CAST([a].[Rating] AS float))
	FROM [Blogs] AS [b0]
	INNER JOIN [Authors] AS [a] ON [b0].[AuthorId] = [a].[AuthorId]
	WHERE [b].[CategoryId] = [b0].[CategoryId]) AS [AvgRating], (
	SELECT MAX([a0].[Rating])
	FROM [Blogs] AS [b1]
	INNER JOIN [Authors] AS [a0] ON [b1].[AuthorId] = [a0].[AuthorId]
	WHERE [b].[CategoryId] = [b1].[CategoryId]) AS [MaxRating], COUNT(DISTINCT ([b].[BlogId])) AS [DistinctCount]
FROM [Blogs] AS [b]
GROUP BY [b].[CategoryId]

With the select-then-aggregate trigger (g.Select(x => x.Author!.Rating).Average()), all three aggregates — including the trigger itself — become correlated subqueries:

SELECT [b].[CategoryId] AS [Key], (
	SELECT AVG(CAST([a].[Rating] AS float))
	FROM [Blogs] AS [b0]
	INNER JOIN [Authors] AS [a] ON [b0].[AuthorId] = [a].[AuthorId]
	WHERE [b].[CategoryId] = [b0].[CategoryId]) AS [AvgRating], (
	SELECT MAX([a0].[Rating])
	FROM [Blogs] AS [b1]
	INNER JOIN [Authors] AS [a0] ON [b1].[AuthorId] = [a0].[AuthorId]
	WHERE [b].[CategoryId] = [b1].[CategoryId]) AS [MaxRating], (
	SELECT AVG(CAST([a1].[Rating] AS float))
	FROM [Blogs] AS [b2]
	INNER JOIN [Authors] AS [a1] ON [b2].[AuthorId] = [a1].[AuthorId]
	WHERE [b].[CategoryId] = [b2].[CategoryId]) AS [SelectThenAvg]
FROM [Blogs] AS [b]
GROUP BY [b].[CategoryId]

AvgRating and MaxRating each became a correlated subquery that re-scans Blogs and re-joins Authors. With K foldable nav aggregates you get K correlated subqueries.

(The join is an INNER JOIN here because Blog.AuthorId is a required FK; with a nullable nav it is a LEFT JOIN. The poisoning is identical either way.)

Expected SQL

The non-foldable aggregate may still require its own subquery — that is acceptable and unavoidable. What should not happen is the foldable sibling aggregates being dragged along with it. The foldable nav aggregates should remain in a single flat JOIN + GROUP BY, with only the non-foldable field isolated.

The non-foldable aggregate could remain a single correlated subquery, as long as it does not force the foldable siblings to correlate too:

SELECT [b].[category_id] AS [Key],
	   AVG([a].[rating]) AS [AvgRating],          -- stays flat
	   MAX([a].[rating]) AS [MaxRating],          -- stays flat
	   (SELECT COUNT(DISTINCT [b0].[blog_id])     -- one correlated subquery is fine
		FROM [Blogs] AS [b0]
		WHERE [b0].[category_id] = [b].[category_id]) AS [NonFoldable]
FROM [Blogs] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[author_id] = [a].[author_id]
GROUP BY [b].[category_id];

Or the non-foldable aggregate could be isolated into a pre-grouped derived table joined by the group key (also leaves the siblings flat):

SELECT [s].[Key], [s].[AvgRating], [s].[MaxRating], [n].[Value] AS [NonFoldable]
FROM (
	SELECT [b].[category_id] AS [Key],
		   AVG([a].[rating]) AS [AvgRating],
		   MAX([a].[rating]) AS [MaxRating]
	FROM [Blogs] AS [b]
	LEFT JOIN [Authors] AS [a] ON [b].[author_id] = [a].[author_id]
	GROUP BY [b].[category_id]
) AS [s]
INNER JOIN (
	SELECT [b0].[category_id] AS [Key], COUNT(DISTINCT ([b0].[blog_id])) AS [Value]
	FROM [Blogs] AS [b0]
	GROUP BY [b0].[category_id]
) AS [n] ON [s].[Key] = [n].[Key];

The key expectation either way: the cost is isolated to the one non-foldable field (1 subquery), not multiplied across every foldable sibling (K subqueries).

Control: remove the non-foldable aggregate ⇒ siblings fold

Removing only the non-foldable member from the same projection yields the expected flat JOIN + GROUP BY with zero correlated subqueries:

SELECT [b].[CategoryId] AS [Key], AVG(CAST([a].[Rating] AS float)) AS [AvgRating], MAX([a].[Rating]) AS [MaxRating]
FROM [Blogs] AS [b]
INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[AuthorId]
GROUP BY [b].[CategoryId]

So a single non-foldable aggregate's presence is the sole trigger for the sibling degradation.

Impact

• Co-presence / spillover: the expensive correlated subqueries are produced for aggregates that are themselves perfectly foldable; one unrelated non-foldable field triggers it.
• Scales poorly: wide grouped projections (reporting scenarios with many aggregate columns) produce one correlated subquery per foldable nav aggregate as soon as a single non-foldable field is added.
• Appears specific to EF Core 11 behavior.

Things that do NOT work around it (all collapse into one SQL statement)

• Nesting the non-foldable aggregate in a sub-anonymous object (new { ..., Nested = new { NonFoldable } }) — EF flattens it into the same projection.
• Chaining a second .Select() that defers the non-foldable aggregate — EF re-joins the base table (fan-out) and still correlates the siblings, or throws KeyNotFoundException: 'EmptyProjectionMember' depending on shape.
• DistinctBy(x => x.Key).Count() — untranslatable (InvalidOperationException: ... DistinctBy ... could not be translated). (Translation support for DistinctBy would also be a welcome enhancement.)

Request

A non-foldable aggregate may legitimately need its own subquery, but its presence should not force the foldable sibling aggregates in the same projection to correlate as well. Isolate the cost to the non-foldable field (via a correlated subquery for just that field, or a pre-grouped derived table joined by key) while keeping the foldable siblings in a flat GROUP BY.

Version / environment

• EF Core: 11.0.0
• Provider: Microsoft.EntityFrameworkCore.SqlServer
• Target framework: .NET 11
• SQL generated via ToQueryString() (no database connection required)

Your code

// Self-contained EF Core 11 repro: a single non-foldable grouped aggregate forces all
// sibling navigation aggregates in the same projection into correlated subqueries.
//
// How to run:
//   1. Create a new xUnit test project targeting net11.0.
//   2. Add package references:
//        Microsoft.EntityFrameworkCore.SqlServer   (11.0.*)
//        xunit, xunit.runner.visualstudio, Microsoft.NET.Test.Sdk
//   3. Drop this file in and run the tests.
//
// No database connection is required: the repro uses ToQueryString(), which produces the
// provider SQL from the model alone (nothing is executed against a server).

using System;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Xunit;
using Xunit.Abstractions;

namespace EfCoreNonFoldableAggregateRepro
{
    // ---- Model -------------------------------------------------------------------------------

    public class Author
    {
        public int AuthorId { get; set; }
        public int Rating { get; set; }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public int CategoryId { get; set; }

        public int AuthorId { get; set; }
        public Author? Author { get; set; } // to-one navigation
    }

    // ---- DbContext ---------------------------------------------------------------------------

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs => Set<Blog>();
        public DbSet<Author> Authors => Set<Author>();

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // A connection string is required to resolve the SQL Server provider, but no
            // connection is opened because the tests only call ToQueryString().
            optionsBuilder.UseSqlServer(
                "Server=localhost;Database=ReproDb;Trusted_Connection=True;TrustServerCertificate=True");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .HasOne(b => b.Author)
                .WithMany()
                .HasForeignKey(b => b.AuthorId);
        }
    }

    // ---- Repro tests -------------------------------------------------------------------------

    public class NonFoldableAggregatePoisoningTests
    {
        private readonly ITestOutputHelper _output;

        public NonFoldableAggregatePoisoningTests(ITestOutputHelper output) => _output = output;

        // Counts correlated aggregate subqueries: a nested "(SELECT AGG(...) ... )" block.
        private static int CountCorrelatedAggregateSubqueries(string sql) =>
            Regex.Matches(sql, @"\(\s*SELECT\s+(?:MAX|MIN|SUM|AVG|COUNT)\s*\(", RegexOptions.IgnoreCase).Count;

        /// <summary>
        /// CONTROL: two foldable nav aggregates with NO non-foldable sibling.
        /// Expected: a single flat JOIN + GROUP BY, zero correlated subqueries.
        /// </summary>
        [Fact]
        public void FoldableNavAggregatesAlone_DoNotCorrelate()
        {
            using var db = new BloggingContext();

            var query = db.Blogs
                .GroupBy(x => x.CategoryId)
                .Select(g => new
                {
                    Key = g.Key,
                    AvgRating = g.Average(x => x.Author!.Rating),
                    MaxRating = g.Max(x => x.Author!.Rating)
                });

            var sql = query.ToQueryString();
            _output.WriteLine(sql);

            Assert.Equal(0, CountCorrelatedAggregateSubqueries(sql));
        }

        /// <summary>
        /// BUG: add ONE non-foldable aggregate (distinct count) to the same projection.
        /// Actual (EF Core 11): the two foldable nav aggregates are ALSO pushed into correlated
        /// subqueries, so the count is &gt; 1 (the distinct-count itself folds inline).
        ///
        /// Expected: the foldable siblings stay flat; at most the single non-foldable field needs
        /// a subquery, so the count should be &lt;= 1.
        /// </summary>
        [Fact]
        public void NonFoldableDistinctCount_PoisonsFoldableSiblings()
        {
            using var db = new BloggingContext();

            var query = db.Blogs
                .GroupBy(x => x.CategoryId)
                .Select(g => new
                {
                    Key = g.Key,
                    AvgRating = g.Average(x => x.Author!.Rating),   // foldable
                    MaxRating = g.Max(x => x.Author!.Rating),       // foldable
                    DistinctCount = g.Select(x => x.BlogId).Distinct().Count() // non-foldable trigger
                });

            var sql = query.ToQueryString();
            _output.WriteLine(sql);

            var correlated = CountCorrelatedAggregateSubqueries(sql);

            // Demonstrates the bug: the foldable siblings were dragged into correlation.
            // This assertion is expected to FAIL on EF Core 11 (observed correlated == 2).
            Assert.True(
                correlated <= 1,
                $"Expected at most the single non-foldable field to need a subquery, " +
                $"but {correlated} correlated subqueries were produced (foldable siblings were poisoned).");
        }

        /// <summary>
        /// BUG (second trigger): the same poisoning occurs with the select-then-aggregate form
        /// g.Select(x =&gt; x.Author.Rating).Average() instead of a distinct count, showing the
        /// trigger is ANY non-foldable aggregate, not specifically COUNT(DISTINCT).
        /// </summary>
        [Fact]
        public void NonFoldableSelectThenAggregate_PoisonsFoldableSiblings()
        {
            using var db = new BloggingContext();

            var query = db.Blogs
                .GroupBy(x => x.CategoryId)
                .Select(g => new
                {
                    Key = g.Key,
                    AvgRating = g.Average(x => x.Author!.Rating),   // foldable (selector overload)
                    MaxRating = g.Max(x => x.Author!.Rating),       // foldable (selector overload)
                    SelectThenAvg = g.Select(x => x.Author!.Rating).Average() // non-foldable trigger
                });

            var sql = query.ToQueryString();
            _output.WriteLine(sql);

            var correlated = CountCorrelatedAggregateSubqueries(sql);

            // Expected to FAIL on EF Core 11 (the foldable siblings get correlated too).
            Assert.True(
                correlated <= 1,
                $"Expected at most the single non-foldable field to need a subquery, " +
                $"but {correlated} correlated subqueries were produced (foldable siblings were poisoned).");
        }
    }
}

Stack traces

Verbose output

EF Core version

11.0.0

Database provider

No response

Target framework

.NET 11

Operating system

No response

IDE

No response