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

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.

CodePudding user response：

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;

CodePudding user response：

var products = context.Products.ToArray();
var query =
    from product in products
    join typeGroup in
        from product in products
        group product by product.Type on product.Type equals typeGroup.Key
    where product.SalePrice > typeGroup.Average(product => product.SalePrice)
    select product;

Attention:
The product in second from beginning sql is different from the first.
EFCore6 could not translate this LINQ on IQuerable for now? Anybody has a better way?
So we use ToArray, and it takes all product data into memory. So maybe the raw sql one should be better.
To split it will solve the problem and make it more readable.
The reason is IQuerable.GroupBy() cant be the final operator, which is planned to be improved at EFCore 7.