Supporting OUTER APPLY for aggregate queries

[Erichero]

I have posted a question on StackOverflow here:
https://stackoverflow.com/questions/78126110/ef-core-cannot-perform-an-aggregate-function-on-an-expression-containing-an-agg

The problem is that I have a reporting query that was working in EF6 and EF would translate it to use OUTER APPLY, but in EF Core 7.0.5 I get the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery". I see EF Core 8 still has the issue too.

I would like to know if there is a workaround or some way to get EF Core to translate this correctly to use OUTER APPLY, or if this is not supported then when it will come onto the roadmap. Thank you.

[roji]

Can you please submit a minimal, runnable code sample, using EF Core 8.0? The SO issue contains incomplete snippets only.

[Erichero]

I have attached a solution showing a working project on .Net Framework 4.5.2 and EF 6.2.0, and a failing project on .Net Core 8 and EF 8.0.6. Sql Server db backup is included. If you look at SQL tracing you can see the different outputs.

While it's possible to work around this by using stored procs, I use this base query approach for dozens of reporting aggregates and creating a separate proc for each one is a huge effort for something that was easy to do before :)

Thanks for your assistance.
EFOuterApply.zip

[cincuranet]

The query is:

var query =
    from order in context.Orders
    join orderItem1 in context.OrderItems on order.Id equals orderItem1.OrderId into orderItem2
    let orderItem = orderItem2.OrderByDescending(x => x.Price).FirstOrDefault()
    orderby order.Id
    select new 
    {
        OrderId = order.Id,
        HighestPrice = orderItem != null ? orderItem.Price : (decimal?) null,
    };
var average = query.Average(x => x.HighestPrice);

And EF6 generates:

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        AVG([Apply1].[A1]) AS [A1]
        FROM ( SELECT
            CASE WHEN ([Limit1].[Id] IS NOT NULL) THEN [Limit1].[Price] END AS [A1]
            FROM  [dbo].[Orders] AS [Extent1]
            OUTER APPLY  (SELECT TOP (1) [Project1].[Id] AS [Id], [Project1].[Price] AS [Price]
                FROM ( SELECT
                    [Extent2].[Id] AS [Id],
                    [Extent2].[Price] AS [Price]
                    FROM [dbo].[OrderItems] AS [Extent2]
                    WHERE [Extent1].[Id] = [Extent2].[OrderId]
                )  AS [Project1]
                ORDER BY [Project1].[Price] DESC ) AS [Limit1]
        )  AS [Apply1]
    )  AS [GroupBy1]

`query` (without `Average`)

...EF6:

SELECT
    [Project2].[Id] AS [Id],
    [Project2].[C1] AS [C1]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        CASE WHEN ([Limit1].[Id] IS NOT NULL) THEN [Limit1].[Price] END AS [C1]
        FROM  [dbo].[Orders] AS [Extent1]
        OUTER APPLY  (SELECT TOP (1) [Project1].[Id] AS [Id], [Project1].[Price] AS [Price]
            FROM ( SELECT
                [Extent2].[Id] AS [Id],
                [Extent2].[Price] AS [Price]
                FROM [dbo].[OrderItems] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[OrderId]
            )  AS [Project1]
            ORDER BY [Project1].[Price] DESC ) AS [Limit1]
    )  AS [Project2]
    ORDER BY [Project2].[Id] ASC

...EF Core:

      SELECT [o].[Id] AS [OrderId], (
          SELECT TOP(1) [o0].[Price]
          FROM [OrderItems] AS [o0]
          WHERE [o].[Id] = [o0].[OrderId]
          ORDER BY [o0].[Price] DESC) AS [HighestPrice]
      FROM [Orders] AS [o]
      ORDER BY [o].[Id]

[Erichero]

Thank you. Just noting that there are several StackOverflow questions open for the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".

[roji]

Duplicate of #34256

[roji]

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