Remove duplicated SUM subquery in Entity Framework Core query

Question

I have the following LINQ code:

return from policy in db.Policy.Include(it => it.LedgerLines)
       let balance = policy.LedgerLines.Sum(it => it.Amount)
       where balance > 0m && balance < 5m
       select policy;

This gets translated to

SELECT ...
FROM [Policy] AS [p]
LEFT JOIN [PolicyLedger] AS [p0] ON [p].[Id] = [p0].[PolicyId]
WHERE (((SELECT SUM([p1].[Amount])
         FROM [PolicyLedger] AS [p1]
         WHERE [p].[Id] = [p1].[PolicyId]) > 0.0)) 
   AND ((SELECT SUM([p2].[Amount])
         FROM [PolicyLedger] AS [p2]
         WHERE [p].[Id] = [p2].[PolicyId]) < 5.0)
ORDER BY [p].[Id], [p0].[Id]

Is there any way to only execute the SUM([p1].[Amount]) subquery once?

(EF Core 3.1)

Answer 1

The line

let balance = policy.LedgerLines.Sum(it => it.Amount)

which is the equivalent of intermediate projection clearly indicates the intent to reuse the expression.

But EF Core query translator puts a lot of efforts to produce "pretty" queries by eliminating subqueries as much as possible. Unfortunately in this case it seems to go too much in that regard.

With that being said, you can consider it to be a translation defect, leave the LINQ query "as is" and wait for improved translation - EFC 5.x doesn't improve that, may be EFC 6.0 or later, if ever.

But here is one not so distracting trick to let EFC 3.1 / 5.x generate JOIN to GROUP BY subquery and reuse the SUM expression.

The only change to the original LINQ query is to replace the above let statement with the following

from balance in policy.LedgerLines
    .GroupBy(it => it.PolicyId)
    .Select(g => g.Sum(it => it.Amount))

which gets translated to

SELECT ...
FROM [Policy] AS [p]
INNER JOIN (
    SELECT SUM([p0].[Amount]) AS [c], [p0].[PolicyId]
    FROM [PolicyLedger] AS [p0]
    GROUP BY [p0].[PolicyId]
) AS [t] ON [p].[Id] = [t].[PolicyId]
LEFT JOIN [PolicyLedger] AS [p1] ON [p].[Id] = [p1].[PolicyId]
WHERE ([t].[c] > 0.0) AND ([t].[c] < 5.0)
ORDER BY [p].[Id], [p1].[Id]

Answer 2

You could start your query from the LedgerLine entity and use a GroupBy() to build the sum of the Amount column for each policy. However, you can't group on a navigation property, so you have to group on the PolicyId instead. This means you need to join the PolicyId column with the Policies table/DbSet afterwards to get the actual Policy entity (with any required included collection properties).

The code can look like this:

var result = context.LedgerLines
                .Include(it => it.Policy)
                .GroupBy(it => it.PolicyId)
                .Select(it => new {
                    policyId = it.Key,
                    sum = it.Sum(a => a.Amount)
                })
                .Join(context.Policies.Include(it => it.LedgerLines),
                    it => it.policyId,
                    it => it.Id,
                    (a,b) => new {
                        a.sum,
                        policy=b
                    })
                .Where(it => it.sum > 0m && it.sum < 5m)
                .Select(it => it.policy)
                .ToList();

This will generate a query like this (for MySQL):

SELECT `p`.`Id`, `p`.`Name`, `l0`.`Id`, `l0`.`Amount`, `l0`.`PolicyId`
FROM (
    SELECT `l`.`PolicyId`, SUM(`l`.`Amount`) AS `c`
    FROM `LedgerLines` AS `l`
    GROUP BY `l`.`PolicyId`
) AS `t`
INNER JOIN `Policies` AS `p` ON `t`.`PolicyId` = `p`.`Id`
LEFT JOIN `LedgerLines` AS `l0` ON `p`.`Id` = `l0`.`PolicyId`
WHERE (CAST(`t`.`c` AS decimal(18, 2)) > 0) AND (CAST(`t`.`c` AS decimal(18, 2)) < 5)
ORDER BY `p`.`Id`, `l0`.`Id`

As you see only one SUM() call is used, but I'm unsure about the performance as you JOIN over the LedgerLines table twice, not to mention that this code looks weird and cumbersome.