Closed Erichero closed 1 week ago
Can you please submit a minimal, runnable code sample, using EF Core 8.0? The SO issue contains incomplete snippets only.
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
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]
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".
Duplicate of https://github.com/dotnet/efcore/issues/34256
Am going to use https://github.com/dotnet/efcore/issues/34256 to track working around the SQL Server limitation in EF.
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.