SQL optimization when Including collection and results are filtered by parent PK

[rowanmiller]

Given the following query...

            var order = db.Orders
                .Include(o => o.Lines)
                .SingleOrDefault(o => o.OrderId == orderId);

We generate the following SQL statements. The join to Order could be eliminated in the second query because it filters by the same column that is being used to join (i.e. we could filter by the FK rather than the PK).

SELECT TOP(2) [o].[CheckoutBegan], [o].[OrderId], [o].[OrderPlaced], [o].[State], [o].[Total], [o].[Username]
FROM [Order] AS [o]
WHERE [o].[OrderId] = @__orderId_0
ORDER BY [o].[OrderId]

SELECT [o].[OrderId], [o].[PricePerUnit], [o].[ProductId], [o].[Quantity]
FROM [OrderLine] AS [o]
INNER JOIN (
    SELECT DISTINCT TOP(2) [o].[OrderId]
    FROM [Order] AS [o]
    WHERE [o].[OrderId] = @__orderId_0
) AS [o0] ON [o].[OrderId] = [o0].[OrderId]
ORDER BY [o0].[OrderId]

[smitpatel]

@anpete

[anpete]

@smitpatel Not done.

[Muppets]

I'm not sure if this is still an issue. Testing the same test as above:

            using (var context = CreateContext())
            {
                var order = context.Customers
                    .Include(c => c.Orders)
                    .SingleOrDefault(o => o.CustomerID == "1");
            }

Now produces a single SQL call:

SELECT [t].[CustomerID], [t].[Address], [t].[City], [t].[CompanyName], [t].[ContactName], [t].[ContactTitle], [t].[Country], [t].[Fax], [t].[Phone], [t].[PostalCode], [t].[Region], [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM (
    SELECT TOP(2) [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] = N'1'
    ORDER BY [c].[CustomerID]
) AS [t]
LEFT JOIN [Orders] AS [o] ON [t].[CustomerID] = [o].[CustomerID]
ORDER BY [t].[CustomerID

Feels like we can close this now unless I've missed something?