dotnet / dotnet/efcore

Stop parameterizing uncorrelated subqueries

Open
#34,261 3 comments 0 reactions 0 assignees View on GitHub
area-perf area-query
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

In queries such as the following:

```c#
_ = await context.Blogs
.Select(b => context.Posts.Count())
.ToListAsync();
```

The `context.Blogs.Count()` bit represents an uncorrelated subquery, i.e. the Posts are simply counted without any dependency on the outer Blog being processed. Because of this, the entire subquery is evaluatable and we extract it out to a parameter, which means this query is executed via two roundtrips:

```sql
SELECT COUNT(*)
FROM [Posts] AS [p]

SELECT @__Count_0
FROM [Blogs] AS [b]
```

Aside from the two roundtrips, the first query is executed synchronously, which is bad.

Note that if there's any parameter in the subquery, we no longer evaluate it, e.g.:

```c#
_ = await context.Blogs
.Select(b => context.Posts.Where(p => p.Id > 3).Count())
.ToListAsync();
```

We should stop evaluating for the case where there's no parameter.

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.