Sum on subqueries on EF Core Linq

Question

I want to get a result by typing subquery into the sum query.

The following code works when I write to Sql.

But on EF, how do I add another select at the top?

SQL

This code is work.

select 
    sum (data.rate)
from 
(
    SELECT 
        t1.Id,
        (c.rate / 
        (SELECT COUNT(1) FROM [table4] AS [t4] WHERE ([t4].[FKId] = p1.Id))) as rate        
FROM [table1] AS [t1]
INNER JOIN [table2] AS [t2] ON ([t1].[FKId] = [t2].[Id])
INNER JOIN [table3] AS [t3] ON ([t1].[FKId] = [t3].[Id]))
as data

C#

var data = await (
    ????
    from t1 in ctx.table1
    join t2 in ctx.table2 on new { t1.FKId} equals new { FKId = t2.Id}
    join t3 in ctx.table3 on new { t1.FKId} equals new { FKId = t3.Id}
    select new
    {
        rate = t3.Rate /
            (from t4 in ctx.table4
             where  t4.FKId == t2.Id
             select t4.Id)
            .Count())
     })
     .SumAsync(sm => (double?)sm.rate ?? 0);

This c# code is not working.

Error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

You are creating a lot of anonymous types that you aren't using. — NetMage, Dec 04 '19 at 20:51
Your SQL says `where t4.FKId = p1.Id` and your LINQ says `t4.FKId == t2.Id` - why are they different? Same for SQL `c.rate` and LINQ `t3.Rate`? — NetMage, Dec 04 '19 at 20:56

NetMage · Accepted Answer · 2022-02-04T20:19:58.950

Per this answer, EF Core 3.0 still can't handle this situation (!).

However, you can translate the query without a subquery (effectively, using SelectMany) and it should work:

var ans = await (from t1 in ctx.table1
                 join t2 in ctx.table2 on t1.FKId equals t2.Id
                 join t3 in ctx.table3 on t1.FKId equals t3.Id
                 from t4 in ctx.table4
                 where t4.FkId == t2.Id
                 group t4.Id by new { t3.rate, t2.Id } into t2g
                 select t2g.Key.rate / t2g.Count()
                )
                .SumAsync();

Sum on subqueries on EF Core Linq

1 Answers1