How to translate this SQL into LINQ, with correlated subquery single table

Question

The raw SQL uses correlated subquery for single table, any possibility to translate this with single LINQ Query?

SELECT * From dbo.Products AS P1
WHERE SalePrice > (SELECT AVG(SalePrice)
                   FROM dbo.Products AS P2
                   WHERE P1.Type = P2.Type GROUP BY Type)

it groups products by type and select the product whose SalePrice is greater than type group average. LINQ data source is a EFCore DbSet.

Answer 1

Try the following query:

var products = context.Products;

var grouped = 
    from p in products
    group p by p.Type into g
    select new 
    {
        Type = g.Key,
        Average = g.Average(x => x.SalePrice)
    };

var query = 
    from p in products
    join g in grouped on p.Type equals g.Type
    where p.SalePrice > g.Average
    select p;

Answer 2

from product in products.AsEnumerable()
group product by product.Type into typeGroup
from product in typeGroup
where product.SalePrice > typeGroup.Average(product => product.SalePrice)
select product;

Attention: the product in second from beginning SQL is different from the first.

EF Core 6 could not translate this LINQ on IQueryable

So we use AsEnumerable(), and it takes all product data into memory. So maybe the raw SQL one should be better.

Splitting it into two query like another answer will solve the problem and make it more readable.

The reason is IQueryable.GroupBy() can't be the final operator for EF 6, which is planned to be improved with EF Core 7.

To prevent GroupBy() being the final operator. The solution is:

var query =
    from product in products
    join typeGroup in
        from product in products
        group product by product.Type into typeGroup
        select new { Type = typeGroup.Key, Average = typeGroup.Average(p => p.SalePrice) } 
    on product.Type equals typeGroup.Type
    where product.SalePrice > typeGroup.Average
    select product;