GroupBy with Any() or All() prevents aggregates over reference navigations from being lifted into a join

[benedict-odonovan]

Bug description

In EF Core 11, GroupBy(key).Select(g => g.Max(o => o.Nav.Property)) now translates to a single SELECT with a join (#27933, fixed by #38668). Adding an Any() or All() call anywhere in the same result selector disables that optimisation for every aggregate in the group, and the query falls back to a correlated sub-query per aggregate, as EF Core 10 did.

Any and All themselves translate to a correlated EXISTS / NOT EXISTS, which is reasonable. The problem is that their presence also demotes their siblings: aggregates that read a reference navigation stop being lifted into the shared join and each become their own correlated sub-query.

db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        g.Key,
        Region = g.Max(o => o.Customer.Region),
        Amount = g.Sum(o => o.Amount),
        Westerly = g.Count(o => o.Customer.Region == "West"),
    });

Produces:

SELECT [o].[CustomerId] AS [Key], MAX([c].[Region]) AS [Region], COALESCE(SUM([o].[Amount]), 0.0) AS [Amount], COUNT(CASE
    WHEN [c].[Region] = N'West' THEN 1
END) AS [Westerly]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
GROUP BY [o].[CustomerId]

while the same query with Any in place of Count

        Westerly = g.Any(o => o.Customer.Region == "West"),

produces:

SELECT [o].[CustomerId] AS [Key], (
    SELECT MAX([c].[Region])
    FROM [Orders] AS [o0]
    INNER JOIN [Customers] AS [c] ON [o0].[CustomerId] = [c].[Id]
    WHERE [o].[CustomerId] = [o0].[CustomerId]) AS [Region], COALESCE(SUM([o].[Amount]), 0.0) AS [Amount], CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Orders] AS [o1]
        INNER JOIN [Customers] AS [c0] ON [o1].[CustomerId] = [c0].[Id]
        WHERE [o].[CustomerId] = [o1].[CustomerId] AND [c0].[Region] = N'West') THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Westerly]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerId]

and with All

        Westerly = g.All(o => o.Customer.Region == "West"),

SELECT [o].[CustomerId] AS [Key], (
    SELECT MAX([c].[Region])
    FROM [Orders] AS [o0]
    INNER JOIN [Customers] AS [c] ON [o0].[CustomerId] = [c].[Id]
    WHERE [o].[CustomerId] = [o0].[CustomerId]) AS [Region], COALESCE(SUM([o].[Amount]), 0.0) AS [Amount], CASE
    WHEN NOT EXISTS (
        SELECT 1
        FROM [Orders] AS [o1]
        INNER JOIN [Customers] AS [c0] ON [o1].[CustomerId] = [c0].[Id]
        WHERE [o].[CustomerId] = [o1].[CustomerId] AND [c0].[Region] <> N'West') THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Westerly]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerId]

In both cases MAX([c].[Region]) was computed from the join in the first query, and is now a correlated sub-query that re-scans Orders and re-joins Customers.

The trigger is the presence of the quantifier, not anything it reads. A bare Any() with no predicate, and an All(...) whose predicate touches no navigation, both have the same effect:

db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        g.Key,
        Region = g.Max(o => o.Customer.Region),
        HasAny = g.Any(),
    });

SELECT [o].[CustomerId] AS [Key], (
    SELECT MAX([c].[Region])
    FROM [Orders] AS [o0]
    INNER JOIN [Customers] AS [c] ON [o0].[CustomerId] = [c].[Id]
    WHERE [o].[CustomerId] = [o0].[CustomerId]) AS [Region], CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Orders] AS [o1]
        WHERE [o].[CustomerId] = [o1].[CustomerId]) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [HasAny]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerId]

        AllLarge = g.All(o => o.Amount > 10),

SELECT [o].[CustomerId] AS [Key], (
    SELECT MAX([c].[Region])
    FROM [Orders] AS [o0]
    INNER JOIN [Customers] AS [c] ON [o0].[CustomerId] = [c].[Id]
    WHERE [o].[CustomerId] = [o0].[CustomerId]) AS [Region], CASE
    WHEN NOT EXISTS (
        SELECT 1
        FROM [Orders] AS [o1]
        WHERE [o].[CustomerId] = [o1].[CustomerId] AND [o1].[Amount] <= 10.0) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [AllLarge]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerId]

Writing the predicate as Count(...) > 0 instead of Any(...) restores the single-SELECT translation, which is the workaround, and shows the optimisation is otherwise willing to handle the shape:

        Westerly = g.Count(o => o.Customer.Region == "West") > 0,

SELECT [o].[CustomerId] AS [Key], MAX([c].[Region]) AS [Region], COALESCE(SUM([o].[Amount]), 0.0) AS [Amount], CASE
    WHEN COUNT(CASE
        WHEN [c].[Region] = N'West' THEN 1
    END) > 0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Westerly]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
GROUP BY [o].[CustomerId]

Your code

using Microsoft.EntityFrameworkCore;

using var db = new AppContext();

Print("1. Count with a predicate over a reference navigation",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            Amount = g.Sum(o => o.Amount),
            Westerly = g.Count(o => o.Customer.Region == "West"),
        }));

Print("2. the same query with Any",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            Amount = g.Sum(o => o.Amount),
            Westerly = g.Any(o => o.Customer.Region == "West"),
        }));

Print("3. the same query with All",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            Amount = g.Sum(o => o.Amount),
            Westerly = g.All(o => o.Customer.Region == "West"),
        }));

Print("4. a bare Any() that reads no navigation at all",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            HasAny = g.Any(),
        }));

Print("5. All with a predicate that reads no navigation",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            AllLarge = g.All(o => o.Amount > 10),
        }));

Print("6. Any and All together",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            AnyWest = g.Any(o => o.Customer.Region == "West"),
            AllWest = g.All(o => o.Customer.Region == "West"),
        }));

Print("7. query 2 hand-written as Count(...) > 0",
    db.Orders
        .GroupBy(o => o.CustomerId)
        .Select(g => new
        {
            g.Key,
            Region = g.Max(o => o.Customer.Region),
            Amount = g.Sum(o => o.Amount),
            Westerly = g.Count(o => o.Customer.Region == "West") > 0,
        }));

static void Print<T>(string title, IQueryable<T> query)
{
    var sql = query.ToQueryString();
    var selects = System.Text.RegularExpressions.Regex.Matches(sql, @"\bSELECT\b").Count;
    Console.WriteLine($"===== {title}  [{selects} SELECT(s)] =====");
    Console.WriteLine(sql);
    Console.WriteLine();
}

public class Customer
{
    public int Id { get; set; }
    public string Region { get; set; } = null!;
    public decimal Discount { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public decimal Amount { get; set; }
    public decimal? Freight { get; set; }
}

public class AppContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer("Server=localhost;Database=Repro;Trusted_Connection=True;TrustServerCertificate=True");
}

Stack traces

Verbose output

EF Core version

11.0.0-rc.1.26410.101

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 11

Operating system

Windows 11

IDE

No response

[benedict-odonovan]

I believe the issue is down to the SelectorAggregateMethodNames and PredicateAggregateMethodNames whitelists in #38668 's TryLiftAggregatesOverNavigations. I'm unsure as to whether All() and Any() were intentionally excluded from the whitelist and if so, why. Adding them into the whitelist and running the tests again gives no failures.

Would it be acceptable for me to create a PR for this issue that adds All() and Any() as well as tests that match those added in #38668 for the existing whitelisted aggregates Max(), Min() etc?

[AndriySvyryd]

Would it be acceptable for me to create a PR for this issue that adds All() and Any() as well as tests that match those added in #38668 for the existing whitelisted aggregates Max(), Min() etc?

Sure