dotnet / dotnet/efcore

Refrain from doing split query when not necessary

Open
#30,025 2 comments 2 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

Test Collection_projection_over_GroupBy_over_parameter runs the following LINQ query:

```c#
var validIds = new List { "L1 01", "L1 02" };

return AssertQuery(
async,
ss => ss.Set()
.Where(l1 => validIds.Contains(l1.Name))
.GroupBy(l => l.Date)
.Select(g => new { g.Key, Ids = g.Select(e => e.Id) }),
elementSorter: e => e.Key,
elementAsserter: (e, a) =>
{
AssertEqual(e.Key, a.Key);
AssertCollection(e.Ids, a.Ids);
});
```

This produces the following SQL with single query (ComplexNavigationsCollectionsQuerySqlServerTest):

```sql
SELECT [t].[Date], [t0].[Id]
FROM (
SELECT [l].[Date]
FROM [LevelOne] AS [l]
WHERE [l].[Name] IN (N'L1 01', N'L1 02')
GROUP BY [l].[Date]
) AS [t]
LEFT JOIN (
SELECT [l0].[Id], [l0].[Date]
FROM [LevelOne] AS [l0]
WHERE [l0].[Name] IN (N'L1 01', N'L1 02')
) AS [t0] ON [t].[Date] = [t0].[Date]
ORDER BY [t].[Date]
```

And the following two queries with split query (ComplexNavigationsCollectionsSplitQuerySqlServerTest):

```sql
SELECT [l].[Date]
FROM [LevelOne] AS [l]
WHERE [l].[Name] IN (N'L1 01', N'L1 02')
GROUP BY [l].[Date]
ORDER BY [l].[Date];

SELECT [t0].[Id], [t].[Date]
FROM (
SELECT [l].[Date]
FROM [LevelOne] AS [l]
WHERE [l].[Name] IN (N'L1 01', N'L1 02')
GROUP BY [l].[Date]
) AS [t]
INNER JOIN (
SELECT [l0].[Id], [l0].[Date]
FROM [LevelOne] AS [l0]
WHERE [l0].[Name] IN (N'L1 01', N'L1 02')
) AS [t0] ON [t].[Date] = [t0].[Date]
ORDER BY [t].[Date];
```

There doesn't seem to be a good reason to actually split the query. I'm not sure what the characteristics of this scenario are, but we should investigate if there are other scenarios where we're needlessly doing split query.

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.