Don't parameterize non-lambda arguments in DbContext-rooted subqueries
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
For non-lambda arguments of LINQ operators, we can't make the distinction between constant and parameter. So, to avoid producing different SQLs for different values, we parameterize:
```c#
_ = ctx.Blogs.Take(5).Sum(p => p.Id);
```
SQL:
```sql
SELECT COALESCE(SUM([t].[Id]), 0)
FROM (
SELECT TOP(@__p_0) [b].[Id]
FROM [Blogs] AS [b]
) AS [t]
```
When the operator is embedded, we get a constant instead, which is good:
```c#
_ = ctx.Blogs.Where(b => b.Posts.Take(5).Sum(p => p.Id) == 8).ToList();
```
SQL:
```sql
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE (
SELECT COALESCE(SUM([t].[Id]), 0)
FROM (
SELECT TOP(5) [p].[Id], [p].[BlogId]
FROM [Post] AS [p]
WHERE [b].[Id] = [p].[BlogId]
) AS [t]) = 8
```
However, when the subquery has DbContext as its root, we get a parameter although we shouldn't:
```c#
_ = ctx.Blogs.Where(b => ctx.Blogs.Take(5).Sum(p => p.Id) == 8).ToList();
```
SQL:
```sql
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE (
SELECT COALESCE(SUM([t].[Id]), 0)
FROM (
SELECT TOP(@__p_0) [b0].[Id], [b0].[Name]
FROM [Blogs] AS [b0]
) AS [t]) = 8
```
This is because the `ctx.Blogs.Take(5)` is detected as evaluatable, and when evaluating, we have a specific check for IQueryable which runs the expression through ExtractParameters again; as a result, that fragment is processed as if it's not inside a lambda. This doesn't happen with the b.Posts (2nd sample) above, since that's not IQueryable.
Contributor guide
Assessment
This issue has not been assessed yet.