dotnet / dotnet/efcore

GroupBy possibly causes needless duplication of predicate

Open
#29,670 1 comment 3 reactions 0 assignees View on GitHub
area-groupby area-perf area-query
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

LINQ:

```c#
var orderIds = new [] { Guid.NewGuid(), Guid.NewGuid() };

var orders = dbContext.Orders
.Where(c => dbContext.AsQueryable(orderIds).Contains(c.OrderId))
.Select(c => new
{
c.Project.Code,
c.Project.Revenue
})
.GroupBy(c => new
{
c.Code
})
.Select(c => new
{
c.Key.Code,
Sum = c.Sum(e => e.Revenue)
}).ToList();
```

SQL:

```sql
exec sp_executesql N'SELECT [p].[Code], (
SELECT COALESCE(SUM([p1].[Revenue]), 0.0)
FROM [Orders] AS [o0]
INNER JOIN [Projects] AS [p0] ON [o0].[ProjectId] = [p0].[ProjectId]
INNER JOIN [Projects] AS [p1] ON [o0].[ProjectId] = [p1].[ProjectId]
WHERE EXISTS (
SELECT 1
FROM STRING_SPLIT(@__source_1, @__separator_2) AS [s0]
WHERE CONVERT(UNIQUEIDENTIFIER, [s0].[Value]) = [o0].[OrderId]) AND [p].[Code] = [p0].[Code]) AS [Sum]
FROM [Orders] AS [o]
INNER JOIN [Projects] AS [p] ON [o].[ProjectId] = [p].[ProjectId]
WHERE EXISTS (
SELECT 1
FROM STRING_SPLIT(@__source_1, @__separator_2) AS [s0]
WHERE CONVERT(UNIQUEIDENTIFIER, [s].[Value]) = [o].[OrderId])
GROUP BY [p].[Code]',N'@__source_1 nvarchar(4000),@__separator_2 nvarchar(4000)',@__source_1=N'7bd14254-eac8-413d-9328-2fda4725b690,e624c495-55dd-436a-8c62-6a7e49c8435d',@__separator_2=N','
```

It seems like the (possibly expensive) WHERE EXISTS shouldn't be duplicated from the predicate to the projection.

Query originally posted because of an unrelated bug in #29638.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.