Failed translating LINQ to SQL when using "let" keyword after "group by".

[deadcativan]

Bug description

I wrote a linq using "let" clause to store the subexpression into a range variable "score", in order to use it in the "orderby" and "select".

            var query =
                from watchVideo in DbContext.Set<WatchVideoEntity>().AsNoTracking()
                where watchVideo.CreatedAt >= createdFrom && watchVideo.CreatedAt < createdBefore
                group watchVideo by watchVideo.VideoId into g
                let score = (g.Sum(x => x.CompletionRate) + globalAvgCompletionRate * bayesianAverageConfidence) / (g.Count() + bayesianAverageConfidence)
                where g.Count() >= minWatchCount
                orderby score descending
                select new
                {
                    VideoId = g.Key,
                    Score = score,
                };

It throwed exception :

The LINQ expression 'DbSet()
.Where(watchVideo => watchVideo.CreatedAt >= @createdFrom && watchVideo.CreatedAt < @createdBefore)
.GroupBy(watchVideo => watchVideo.VideoId)
.Select(g => new {
g = g,
score = g
.AsQueryable()
.Sum(x => x.CompletionRate) + @p / (double)(g
.AsQueryable()
.Count() + @bayesianAverageConfidence)
})' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

But if I rewrote the linq to remove the let clause and use the subexpression directly, it worked.

            var query =
                from watchVideo in DbContext.Set<WatchVideoEntity>().AsNoTracking()
                where watchVideo.CreatedAt >= createdFrom && watchVideo.CreatedAt < createdBefore
                group watchVideo by watchVideo.VideoId into g
                where g.Count() >= minWatchCount
                orderby (g.Sum(x => x.CompletionRate) + globalAvgCompletionRate * bayesianAverageConfidence) / (g.Count() + bayesianAverageConfidence) descending
                select new
                {
                    VideoId = g.Key,
                    Score = (g.Sum(x => x.CompletionRate) + globalAvgCompletionRate * bayesianAverageConfidence) / (g.Count() + bayesianAverageConfidence),
                };

Stack traces

Verbose output

EF Core version

10

Database provider

No response

Target framework

.NET 10

Operating system

No response

IDE

No response

[roji]

Confirmed bug, not a regression (long-standing EF Core limitation). Duplicate of #17673.

Minimal repro

await using var context = new MyContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

// Fails:
var query =
    from watchVideo in context.Set<WatchVideoEntity>()
    group watchVideo by watchVideo.VideoId into g
    let score = g.Sum(x => x.CompletionRate)
    select new
    {
        VideoId = g.Key,
        Score = score,
    };

// Removing the let and inlining the score makes the query work:
// var query =
//     from watchVideo in context.Set<WatchVideoEntity>()
//     group watchVideo by watchVideo.VideoId into g
//     select new
//     {
//         VideoId = g.Key,
//         Score = g.Sum(x => x.CompletionRate),
//     };

var result = await query.ToListAsync();

public class MyContext : DbContext
{
    public DbSet<WatchVideoEntity> WatchVideos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection") ?? "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class WatchVideoEntity
{
    public int Id { get; set; }
    public int VideoId { get; set; }
    public double CompletionRate { get; set; }
}