dotnet / dotnet/efcore

Consider doing split query on a reference navigation if the other side is a collection

Open
#29,174 2 comments 0 reactions 0 assignees View on GitHub
area-perf area-query customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

We currently support split query on collection navigations:

```c#
_ = await ctx.Blogs.Include(b => b.Posts).AsSplitQuery().ToListAsync();
```

```sql
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
ORDER BY [b].[Id]

SELECT [p].[PostId], [p].[BlogId], [p].[Title], [b].[Id]
FROM [Blogs] AS [b]
INNER JOIN [Posts] AS [p] ON [b].[Id] = [p].[BlogId]
ORDER BY [b].[Id]
```

However, if the query is flipped around and starts with the Posts, we do not:

```c#
_ = await ctx.Posts.Include(p => p.Blog).AsSplitQuery().ToListAsync();
```

```sql
SELECT [p].[PostId], [p].[BlogId], [p].[Title], [b].[Id], [b].[Name]
FROM [Posts] AS [p]
LEFT JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
```

This is because the Post.Blog navigation is a reference navigation. However, the single query we produce has the same "cartesian explosion" effect that split queries were designed to solve: each Post row contains and duplicates the details of the Blog.

We could perform the above as a split query, doing so whenever either the navigation *or its reverse navigation* is a collection navigation.

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.