Possible Duplicate:
EF4.2 extra left outer join to same table
I have three entities in a Parent -> Child -> Grandchild relationship: Order -> OrderCustomer -> OrderItem
Each child entity has a non-nullable FK to its parent (i.e. OrderItem.OrderCustomerID and OrderCustomer.OrderID). And I want to query all OrderItems (grandchildren) for a group of Orders. In SQL I would write:
select
oc.OrderID,
oi.Quantity,
oi.SKU
from OrderItems oi
join OrderCustomers oc on oi.OrderCustomerID = oc.OrderCustomerID
where
oc.OrderID in (1, 2, 3, 4, 5)
...so this is what I wrote in LINQ...
OrderItems
.Where(oi => new[] { 1, 2, 3, 4, 5 }.Contains(oi.OrderCustomer.OrderID))
.Select(oi => new
{
oi.OrderCustomer.OrderID,
oi.Quantity,
oi.SKU
})
...but this is the SQL generated by EF...
SELECT
[Extent1].[OrderCustomerID] AS [OrderCustomerID],
[Extent3].[OrderID] AS [OrderID],
[Extent1].[Quantity] AS [Quantity],
[Extent1].[SKU] AS [SKU]
FROM [dbo].[OrderItems] AS [Extent1]
INNER JOIN [dbo].[OrderCustomers] AS [Extent2] ON [Extent1].[OrderCustomerID] = [Extent2].[OrderCustomerID]
LEFT OUTER JOIN [dbo].[OrderCustomers] AS [Extent3] ON [Extent1].[OrderCustomerID] = [Extent3].[OrderCustomerID]
WHERE
[Extent2].[OrderID] = 1 OR [Extent3].[OrderID] IN (2,3,4,5)
Why both an INNER JOIN and an OUTER JOIN?
And why split up the WHERE clause?
On a database with millions of records this query is horribly slow.
But wait, if I change the LINQ to do the Select before the Where...
OrderItems
.Select(oi => new
{
oi.OrderCustomer.OrderID,
oi.Quantity,
oi.SKU
})
.Where(x => new[] { 1, 2, 3, 4, 5 }.Contains(x.OrderID))
...I get the SQL I wanted...
SELECT
[Extent1].[OrderCustomerID] AS [OrderCustomerID],
[Extent2].[OrderID] AS [OrderID],
[Extent1].[Quantity] AS [Quantity],
[Extent1].[SKU] AS [SKU]
FROM [dbo].[OrderItems] AS [Extent1]
INNER JOIN [dbo].[OrderCustomers] AS [Extent2] ON [Extent1].[OrderCustomerID] = [Extent2].[OrderCustomerID]
WHERE
[Extent2].[OrderID] IN (1,2,3,4,5)
So, I guess I can help EF by doing the LINQ Select first, but does anyone know what is up with the first query?
