Aggregate over a collection navigation generates invalid SQL when a single-result subquery projects more than one member (11.0 RC1 Regression)

[benedict-odonovan]

Bug description

An aggregate over a collection navigation generates SQL that SQL Server rejects when the aggregated expression combines a value from a FirstOrDefault() subquery with a column from the outer query:

Msg 8124: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

This is a regression: the query executes on 11.0.0-preview.6.26359.118 and fails on 11.0.0-rc.1.26410.101.

Each blog sums a value over its posts. The summed value multiplies a column read from a single-result subquery (m.Views) by a column on the outer entity (b.Rating):

var query = from b in ctx.Blogs
            let projected = from p in b.Posts
                            let m = p.Metrics.Select(x => new { x.Views, x.Likes }).FirstOrDefault()
                            select new { Views = m.Views * b.Rating, Likes = m.Likes * b.Rating }
            select new
            {
                b.Id,
                TotalViews = projected.Sum(x => x.Views),
                TotalLikes = projected.Sum(x => x.Likes)
            };

On 11.0.0-rc.1.26410.101 this produces:

SELECT [b].[Id], (
    SELECT ISNULL(SUM([m1].[Views] * [b].[Rating]), 0)
    FROM [Posts] AS [p]
    LEFT JOIN (
        SELECT [m0].[Views], [m0].[PostId]
        FROM (
            SELECT [m].[Views], [m].[PostId], ROW_NUMBER() OVER(PARTITION BY [m].[PostId] ORDER BY [m].[Id]) AS [row]
            FROM [Metrics] AS [m]
        ) AS [m0]
        WHERE [m0].[row] <= 1
    ) AS [m1] ON [p].[Id] = [m1].[PostId]
    WHERE [b].[Id] = [p].[BlogId]) AS [TotalViews], (
    SELECT ISNULL(SUM([m4].[Likes] * [b].[Rating]), 0)
    FROM [Posts] AS [p0]
    LEFT JOIN (
        SELECT [m3].[Likes], [m3].[PostId]
        FROM (
            SELECT [m2].[Likes], [m2].[PostId], ROW_NUMBER() OVER(PARTITION BY [m2].[PostId] ORDER BY [m2].[Id]) AS [row]
            FROM [Metrics] AS [m2]
        ) AS [m3]
        WHERE [m3].[row] <= 1
    ) AS [m4] ON [p0].[Id] = [m4].[PostId]
    WHERE [b].[Id] = [p0].[BlogId]) AS [TotalLikes]
FROM [Blogs] AS [b]

SUM([m1].[Views] * [b].[Rating]) sits inside a sub-query correlated on [b].[Id] = [p].[BlogId], so [b].[Rating] is an outer reference and [m1].[Views] is a second column in the same aggregated expression. SQL Server rejects that, and the query fails at execution.

On 11.0.0-preview.6.26359.118 the same LINQ produces valid SQL. The multiplication is evaluated in an OUTER APPLY, so each SUM receives exactly one column:

SELECT [b].[Id], (
    SELECT ISNULL(SUM([s].[value]), 0)
    FROM [Posts] AS [p]
    OUTER APPLY (
        SELECT (
            SELECT TOP(1) [m].[Views]
            FROM [Metrics] AS [m]
            WHERE [p].[Id] = [m].[PostId]) * [b].[Rating] AS [value]
    ) AS [s]
    WHERE [b].[Id] = [p].[BlogId]) AS [TotalViews], (
    SELECT ISNULL(SUM([s0].[value]), 0)
    FROM [Posts] AS [p0]
    OUTER APPLY (
        SELECT (
            SELECT TOP(1) [m0].[Likes]
            FROM [Metrics] AS [m0]
            WHERE [p0].[Id] = [m0].[PostId]) * [b].[Rating] AS [value]
    ) AS [s0]
    WHERE [b].[Id] = [p0].[BlogId]) AS [TotalLikes]
FROM [Blogs] AS [b]

The number of members read from the single-result subquery decides which form is generated. Projecting one member instead of two keeps the OUTER APPLY form on 11.0.0-rc.1.26410.101 and the query executes:

var query = from b in ctx.Blogs
            let projected = from p in b.Posts
                            let m = p.Metrics.Select(x => new { x.Views }).FirstOrDefault()
                            select new { Views = m.Views * b.Rating }
            select new
            {
                b.Id,
                TotalViews = projected.Sum(x => x.Views)
            };

SELECT [b].[Id], (
    SELECT ISNULL(SUM([s].[value]), 0)
    FROM [Posts] AS [p]
    OUTER APPLY (
        SELECT (
            SELECT TOP(1) [m].[Views]
            FROM [Metrics] AS [m]
            WHERE [p].[Id] = [m].[PostId]) * [b].[Rating] AS [value]
    ) AS [s]
    WHERE [b].[Id] = [p].[BlogId]) AS [TotalViews]
FROM [Blogs] AS [b]

Your code

using Microsoft.EntityFrameworkCore;

public class Blog
{
    public int Id { get; set; }
    public int Rating { get; set; }
    public List<Post> Posts { get; set; } = null!;
}

public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; } = null!;
    public List<Metric> Metrics { get; set; } = null!;
}

public class Metric
{
    public int Id { get; set; }
    public int PostId { get; set; }
    public Post Post { get; set; } = null!;
    public int Views { get; set; }
    public int Likes { get; set; }
}

public class AppContext : DbContext
{
    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();
    public DbSet<Metric> Metrics => Set<Metric>();

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

public static class Program
{
    public static void Main()
    {
        using var ctx = new AppContext();

        var query = from b in ctx.Blogs
                    let projected = from p in b.Posts
                                    // Projecting only x.Views here keeps the working translation.
                                    let m = p.Metrics.Select(x => new { x.Views, x.Likes }).FirstOrDefault()
                                    select new { Views = m.Views * b.Rating, Likes = m.Likes * b.Rating }
                    select new
                    {
                        b.Id,
                        TotalViews = projected.Sum(x => x.Views),
                        TotalLikes = projected.Sum(x => x.Likes)
                    };

        Console.WriteLine(query.ToQueryString());

        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        // Throws on 11.0.0-rc.1.26410.101, succeeds on 11.0.0-preview.6.26359.118.
        Console.WriteLine($"rows: {query.ToList().Count}");
    }
}

Stack traces

Microsoft.Data.SqlClient.SqlException (0x80131904): Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main()
Error Number:8124,State:1,Class:16

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]

(Apologies for the number of issues raised over the past few days, I'm really excited about the query translation optimisations in EF Core 11 and I've been trying it out on a large existing codebase. It has lots of complex queries that include subqueries and aggregates so I seem to come across scenarios that aren't present in the existing tests!)

I believe the issue is down to SqlServerAggregateOverSubqueryPostprocessor not lifting out aggregate arguments like [m1].[Views] * [b].[Rating] that reference columns from outer queries alongside other columns.

Currently the postprocessor only lifts an aggregate's whole argument, including column references to outer queries, into an OUTER APPLY / CROSS JOIN when it spots a subquery in it.

The issue has been around for a while but the optimisation in #38502 caused it to appear by changing the shape of the aggregate's argument.

Before that PR if you had two or more members in a single-result subquery it would repeat the subquery for each member, the postprocessor would spot the subqueries and lift out the whole argument (including the reference to the outer column) into an outer apply.

After the PR the repeated subqueries are replaced with a single join. Because there's no subquery the postprocessor doesn't fire and the reference to the outer column stays inside the aggregate.

I would propose renaming SqlServerAggregateOverSubqueryPostprocessor to SqlServerAggregateArgumentPostprocessor and adding a second trigger to lift aggregate arguments if they mix an outer-query column with a column of the SELECT the aggregate belongs to.

If that idea is acceptable I've got a local branch with passing tests I can turn into a PR. Thanks!