Stop parameterizing uncorrelated subqueries
- 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
Assessment
This issue has not been assessed yet.