dotnet / dotnet/efcore

Translated to uncorrelated NOT IN subquery instead of correlated NOT EXISTS

Open
#32,459 0 comments 1 reaction 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 example, test Collection_navigation_equal_to_null_for_subquery_using_ElementAtOrDefault_parameter:

```sql
SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE NOT EXISTS (
SELECT 1
FROM [Orders] AS [o]
WHERE [c].[CustomerID] = [o].[CustomerID]
ORDER BY [o].[OrderID]
OFFSET @__prm_0 ROWS)
```

We should instead translate with NOT IN:

```sql
SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE [c].[CustomerID] NOT IN (
SELECT [o].[CustomerID]
FROM [Orders] AS [o]
ORDER BY [o].[OrderID]
OFFSET @__prm_0 ROWS)
```

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.