Query optimization: remove inner join from subquery navigation contains

[mikary]

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
) = 1

In 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

[AndriySvyryd]

@smitpatel Duplicate of #3419?

[smitpatel]

We need to see what is SQL generated in new pipeline.

[smitpatel]

The test is blocked on #15260