Query optimization: remove inner join from subquery navigation contains #4389
Description
When a sub query uses Contains on a navigation, like the following:
[ConditionalFact]
public virtual void Where_subquery_on_navigation()
{
using (var context = CreateContext())
{
var query = from p in context.Products
where p.OrderDetails.Contains(context.OrderDetails.FirstOrDefault(orderDetail => orderDetail.Quantity == 1))
select p;
var result = query.ToList();
Assert.Equal(1, result.Count);
}
}The SQL contains an inner join on the same table:
SELECT [p].[ProductID], [p].[Discontinued], [p].[ProductName], [p].[UnitsInStock]
FROM [Products] AS [p]
WHERE (
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM (
SELECT [o].[OrderID], [o].[ProductID]
FROM [Order Details] AS [o]
WHERE [p].[ProductID] = [o].[ProductID]
) AS [t]
INNER JOIN (
SELECT TOP(1) [orderDetail].[OrderID], [orderDetail].[ProductID]
FROM [Order Details] AS [orderDetail]
WHERE [orderDetail].[Quantity] = 1
) AS [t0] ON ([t].[OrderID] = [t0].[OrderID]) AND ([t].[ProductID] = [t0].[ProductID]))
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
) = 1In cases where the Inner Join is on the same table, it may be possible to eliminate the join
SELECT [p].[ProductID], [p].[Discontinued], [p].[ProductName], [p].[UnitsInStock]
FROM [Products] AS [p]
WHERE (
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM (
SELECT TOP(1) [orderDetail].[ProductID]
FROM [Order Details] AS [orderDetail]
WHERE [orderDetail].[Quantity] = 1
) AS [t]
WHERE [p].[ProductID] = [t].[ProductID])
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
) = 1