Sum followed by a group by: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery. "

[cts-tradeit]

This queryable:

IQueryable<Holding> holdings = dbContext
            .Set<Position>()
            .Join(contractIds, pos => pos.ContractId, contractId => contractId, (pos, contractId) => pos)
            .Select(pos => new
            {
                pos.ContractId,
                pos.AccountId,
                pos.InstrumentId,
                pos.BalanceTypeId,
                pos.BalanceTimeType,
                pos.Currency,
                Quantity = pos.Quantity - dbContext
                    .Set<Movement>()
                    .Where(movement => movement.AccountId == pos.AccountId &&
                                       movement.InstrumentId == pos.InstrumentId &&
                                       movement.BalanceTypeId == pos.BalanceTypeId &&
                                       movement.BalanceTimeType == pos.BalanceTimeType &&
                                       ((movement.Currency == null && pos.Currency == null) && (movement.Currency == pos.Currency)) &&
                                       movement.MovementDate.Date > targetDateTime.Date)
                    .Sum(movement => movement.Quantity),
            })
            .GroupBy(pos => new { pos.ContractId, pos.AccountId, pos.InstrumentId, pos.BalanceTypeId, pos.Currency })
            .Select(pos => new Holding
            {
                ContractId = pos.Key.ContractId,
                AccountId = pos.Key.AccountId,
                InstrumentId = pos.Key.InstrumentId,
                BalanceTypeId = pos.Key.BalanceTypeId,
                Currency = pos.Key.Currency,
                TradedQuantity = pos.Sum(subPos => subPos.BalanceTimeType == tradeDateBalanceTimeType ? subPos.Quantity : 0),
                SettledQuantity = pos.Sum(subPos => subPos.BalanceTimeType == settlementDateBalanceTimeType ? subPos.Quantity : 0),
                CollateralInQuantity = pos.Sum(subPos => subPos.BalanceTimeType == collateralBalanceTimeType ? subPos.Quantity : 0),
            });

Produces SQL query which is invalid:

[Parameters=[@__tradeDateBalanceTimeType_4='?' (DbType = Int32), @__targetDateTime_Date_3='?' (DbType = DateTime2), @__settlementDateBalanceTimeType_5='?' (DbType = Int32), @__collateralBalanceTimeType_6='?' (DbType = Int32), @__userId_0='?' (DbType = Int32), @__targetDate_1='?' (DbType = Date), @__targetDate_2='?' (DbType = Date)], CommandType='Text', CommandTimeout='30']
SELECT [p].[ContractId], [p].[AccountId], [p].[InstrumentId], [p].[BalanceTypeId], [p].[Currency], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__tradeDateBalanceTimeType_4 THEN [p].[Quantity] - (
        SELECT COALESCE(SUM([m].[Quantity]), 0.0)
        FROM [CTS CustodyWeb IMC].[imc].[Movements] AS [m]
        WHERE ((((([m].[AccountId] = [p].[AccountId]) AND ([m].[InstrumentId] = [p].[InstrumentId])) AND ([m].[BalanceTypeId] = [p].[BalanceTypeId])) AND ([m].[BalanceTimeType] = [p].[BalanceTimeType])) AND ((([m].[Currency] IS NULL) AND ([p].[Currency] IS NULL)) AND (([m].[Currency] = [p].[Currency]) OR (([m].[Currency] IS NULL) AND ([p].[Currency] IS NULL))))) AND (CONVERT(date, [m].[MovementDate]) > @__targetDateTime_Date_3))
    ELSE 0.0
END), 0.0) AS [TradedQuantity], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__settlementDateBalanceTimeType_5 THEN [p].[Quantity] - (
        SELECT COALESCE(SUM([m0].[Quantity]), 0.0)
        FROM [CTS CustodyWeb IMC].[imc].[Movements] AS [m0]
        WHERE ((((([m0].[AccountId] = [p].[AccountId]) AND ([m0].[InstrumentId] = [p].[InstrumentId])) AND ([m0].[BalanceTypeId] = [p].[BalanceTypeId])) AND ([m0].[BalanceTimeType] = [p].[BalanceTimeType])) AND ((([m0].[Currency] IS NULL) AND ([p].[Currency] IS NULL)) AND (([m0].[Currency] = [p].[Currency]) OR (([m0].[Currency] IS NULL) AND ([p].[Currency] IS NULL))))) AND (CONVERT(date, [m0].[MovementDate]) > @__targetDateTime_Date_3))
    ELSE 0.0
END), 0.0) AS [SettledQuantity], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__collateralBalanceTimeType_6 THEN [p].[Quantity] - (
        SELECT COALESCE(SUM([m1].[Quantity]), 0.0)
        FROM [CTS CustodyWeb IMC].[imc].[Movements] AS [m1]
        WHERE ((((([m1].[AccountId] = [p].[AccountId]) AND ([m1].[InstrumentId] = [p].[InstrumentId])) AND ([m1].[BalanceTypeId] = [p].[BalanceTypeId])) AND ([m1].[BalanceTimeType] = [p].[BalanceTimeType])) AND ((([m1].[Currency] IS NULL) AND ([p].[Currency] IS NULL)) AND (([m1].[Currency] = [p].[Currency]) OR (([m1].[Currency] IS NULL) AND ([p].[Currency] IS NULL))))) AND (CONVERT(date, [m1].[MovementDate]) > @__targetDateTime_Date_3))
    ELSE 0.0
END), 0.0) AS [CollateralInQuantity]
FROM [CTS CustodyWeb IMC].[imc].[Positions] AS [p]
INNER JOIN (
    SELECT [u].[ContractId]
    FROM [CTS CustodyWeb].[cw].[UserContracts] AS [u]
    WHERE (([u].[UserId] = @__userId_0) AND ([u].[ValidFrom] <= @__targetDate_1)) AND (([u].[ValidTo] IS NULL) OR (@__targetDate_2 <= [u].[ValidTo]))
) AS [t] ON [p].[ContractId] = [t].[ContractId]
GROUP BY [p].[ContractId], [p].[AccountId], [p].[InstrumentId], [p].[BalanceTypeId], [p].[Currency]

However if we remove:

Quantity = pos.Quantity - dbContext
                    .Set<Movement>()
                    .Where(movement => movement.AccountId == pos.AccountId &&
                                       movement.InstrumentId == pos.InstrumentId &&
                                       movement.BalanceTypeId == pos.BalanceTypeId &&
                                       movement.BalanceTimeType == pos.BalanceTimeType &&
                                       ((movement.Currency == null && pos.Currency == null) && (movement.Currency == pos.Currency)) &&
                                       movement.MovementDate.Date > targetDateTime.Date)
                    .Sum(movement => movement.Quantity),

in the Select before GroupBy, we get:

[Parameters=[@__tradeDateBalanceTimeType_3='?' (DbType = Int32), @__settlementDateBalanceTimeType_4='?' (DbType = Int32), @__collateralBalanceTimeType_5='?' (DbType = Int32), @__userId_0='?' (DbType = Int32), @__targetDate_1='?' (DbType = Date), @__targetDate_2='?' (DbType = Date)], CommandType='Text', CommandTimeout='30']
SELECT [p].[ContractId], [p].[AccountId], [p].[InstrumentId], [p].[BalanceTypeId], [p].[Currency], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__tradeDateBalanceTimeType_3 THEN [p].[Quantity]
    ELSE 0.0
END), 0.0) AS [TradedQuantity], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__settlementDateBalanceTimeType_4 THEN [p].[Quantity]
    ELSE 0.0
END), 0.0) AS [SettledQuantity], COALESCE(SUM(CASE
    WHEN [p].[BalanceTimeType] = @__collateralBalanceTimeType_5 THEN [p].[Quantity]
    ELSE 0.0
END), 0.0) AS [CollateralInQuantity]
FROM [CTS CustodyWeb IMC].[imc].[Positions] AS [p]
INNER JOIN (
    SELECT [u].[ContractId]
    FROM [CTS CustodyWeb].[cw].[UserContracts] AS [u]
    WHERE (([u].[UserId] = @__userId_0) AND ([u].[ValidFrom] <= @__targetDate_1)) AND (([u].[ValidTo] IS NULL) OR (@__targetDate_2 <= [u].[ValidTo]))
) AS [t] ON [p].[ContractId] = [t].[ContractId]
GROUP BY [p].[ContractId], [p].[AccountId], [p].[InstrumentId], [p].[BalanceTypeId], [p].[Currency]

It seems that the first Select is not used as sub-query but rather included within the SUMs from second SELECT (after .GroupBy()) which results in SUM(SELECT (Sum(...))).

EFC: 6.0.5
EFC.SqlServer: 6.0.5

[ajcvickers]

@cts-tradeit It's not clear to us how to make this LINQ query translate to SQL Server, since SQL Server does not support nested aggregates. What SQL do you expect to be generated here?

[cts-tradeit]

@ajcvickers Yes, that's why it should NOT be translated as nested aggregates!

One solution would be delegating one of the sums to a subquery

SELECT  
    SUM(CASE WHEN position2.Type = 1 THEN position2.Quantity ELSE 0 END) as TradedQuantity, 
    SUM(CASE WHEN position2.Type = 2 THEN position2.Quantity ELSE 0 END) as SattledQuantity,
    .... 
FROM (
    SELECT  
           position.BalanceTimeType as Type,
           position.Quantity - (SELECT SUM(movement.Quantity) FROM dbo.Movements WHERE .....) as Quantity,
           ....
    FROM dbo.Positions position
) position2

My current workround is a IQueryable that produces roughly this SQL (instead of subtracting Movements generally before summing Quantities, it subtract respective Movements of each type):

SELECT  
    SUM(CASE WHEN position2.Type = 1 THEN position.Quantity ELSE 0 END) -  (SELECT SUM(movement.Quantity) FROM dbo.Movements movement WHERE movement.Type = 1 AND ...) as TradedQuantity, 
    SUM(CASE WHEN position2.Type = 2 THEN position.Quantity ELSE 0 END) -  (SELECT SUM(movement.Quantity) FROM dbo.Movements movement WHERE movement.Type = 2 AND ...) as SattledQuantity,
FROM dbo.Positions position

However, this approach is somewhat resource wasting as SQL Server repeatedly queries table Movements even though it could have been done all at once.

Even if SQL Server supported nested aggregates, the generated SQL would be frankly quite resource wastefull the same way as my workaround is, it suffers from the same repeated quering of table Movements.

[ajcvickers]

@cts-tradeit Thanks for the additional analysis.

Note from triage: currently blocked on #20291.

[roji]

Duplicate of #34256

[roji]

Am going to use #34256 to track working around the SQL Server limitation in EF.