dotnet / dotnet/efcore

Don't parameterize non-lambda arguments in DbContext-rooted subqueries

Open
#32,697 0 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

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

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.