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

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)
            };

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)
            };

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}");
    }
}