EF Core: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

I had a working reporting query in EF6, and since I upgraded to Core 7.0.5, I'm getting the error:

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

I have reservations, each with multiple payment attempts, and I want to select only the most recent payment attempt, and then sum all these payment amounts.

var query = 
    from reservation in DbContext.Reservations
    join p1 in DbContext.Payments on reservation.Id equals p1.ReservationId into p2
    let payment = p2.OrderByDescending(x => x.Id).FirstOrDefault()
    orderby reservation.Id descending
    select new ReservationPaymentReport
    {
       ReservationId = reservation.Id,
       PaymentId = payment != null ? payment.Id : (int?)null,
       PaymentAmount = payment != null ? payment.Amount : (decimal?)null,
       IsPaymentCompleted = payment != null && paymentCompletedStatusIds.Contains(payment.StatusId),
    };

Then I do a final calculation using that base query:

var paymentTotal = query
    .Where(x => x.PaymentId != null && x.IsPaymentCompleted.Value)
    .Sum(x => x.PaymentAmount) ?? 0;

Where should I restructure/optimise this to get it working?

UPDATE: Here is the SQL produced by EF6 that was working correctly, using CROSS APPLY. I have cleaned it up and substituted names for readability:

SELECT 
SUM(Filter3.PaymentTotal) AS PaymentTotal
FROM
(
    SELECT CASE WHEN p2.Id IS NOT NULL THEN p2.Amount END AS PaymentTotal
    FROM (SELECT Id FROM dbo.Reservations) AS r
    OUTER APPLY
    (
        SELECT TOP 1 p1.Id, p1.Amount, p1.StatusId
        FROM
        (
            SELECT p.Id, p.Amount, p.StatusId
            FROM dbo.Payments AS p
            WHERE r.Id = p.ReservationId
        )  AS p1
        ORDER BY p1.Id DESC
    ) AS p2
    WHERE
    (
        CASE WHEN (p2.Id IS NOT NULL) THEN p2.Id END IS NOT NULL
    )
    AND
    (
        (
            CASE
                WHEN (p2.Id IS NOT NULL AND p2.StatusId IN (6, 9, 7) AND p2.StatusId IS NOT NULL) THEN cast(1 as bit)
                WHEN ( NOT (p2.Id IS NOT NULL AND p2.StatusId IN (6, 9, 7) AND p2.StatusId IS NOT NULL)) THEN cast(0 as bit)
            END
        ) = 1
    )
) AS Filter3

And here is the cleaned-up SQL produced by EF Core 7.0.5:

SELECT COALESCE 
(
    SUM((
    SELECT TOP 1 p2.Amount
    FROM Payments AS p2
    WHERE r.Id = p2.ReservationId
    ORDER BY p2.Id DESC)), 0.0
)
FROM Reservations AS r
WHERE
    ((
        SELECT TOP 1 p.Id
        FROM Payments AS p
        WHERE r.Id = p.ReservationId
        ORDER BY p.Id DESC
    ) IS NOT NULL)
    AND EXISTS
    (
        SELECT 1
        FROM Payments AS p0
        WHERE r.Id = p0.ReservationId
    )
    AND
    (
        SELECT TOP 1 p1.StatusId
        FROM Payments AS p1
        WHERE r.Id = p1.ReservationId
        ORDER BY p1.Id DESC
    ) IN (6, 9, 7)

Solution

This was a known missing feature in EF Core 8 and below, and support has now been rolled out with EF 9.

Here is the ticket on Github that tracked the development of the feature: https://github.com/dotnet/efcore/issues/34256

And here is the release announcement: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#significantly-improved-linq-querying-capabilities