LINQ To Entities: stranger and stranger
I was looking for this LINQ To Entities "bug ?".
I watched the SQL generated queries and in fact it's even stranger than what I thought at first.
For the first LINQ To Entities query:
from c in context.Customers.Include("Orders")
where c.CompanyName.StartsWith("An")
let od = (from o in c.Orders
select o.OrderDate).OrderByDescending(o => o).FirstOrDefault()
orderby od descending
select c;
I get (with foreach (var c in cQuery) Console.WriteLine(c.CompanyName);):
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
The SQL generated query is:
SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region]
FROM ( SELECT
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Country] AS [Country],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Fax] AS [Fax],
[Extent1].[Phone] AS [Phone],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Region] AS [Region],
(SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate]
FROM ( SELECT
[Extent2].[OrderDate] AS [OrderDate]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Extent1].[CustomerID] = [Extent2].[CustomerID]
) AS [Project1]
ORDER BY [Project1].[OrderDate] DESC) AS [C1]
FROM [dbo].[Customers] AS [Extent1]
WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1
) AS [Project2]
ORDER BY [Project2].[C1] DESC
What is very strange is the fact we don't get the orders. So in fact the Include doesn't do its job!
Now for the second one:
from c in context.Customers.Include("Orders")
where c.CompanyName.StartsWith("An")
orderby (from o in c.Orders
select o.OrderDate).OrderByDescending(o => o).FirstOrDefault() descending
select c;
I have this result:
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
with this SQL query:
SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region],
[Project2].[OrderID] AS [OrderID],
[Project2].[C1] AS [C1],
[Project2].[C3] AS [C2],
[Project2].[C2] AS [C3],
[Project2].[EmployeeID] AS [EmployeeID],
[Project2].[Freight] AS [Freight],
[Project2].[OrderDate1] AS [OrderDate],
[Project2].[OrderID1] AS [OrderID1],
[Project2].[RequiredDate] AS [RequiredDate],
[Project2].[ShipAddress] AS [ShipAddress],
[Project2].[ShipCity] AS [ShipCity],
[Project2].[ShipCountry] AS [ShipCountry],
[Project2].[ShipName] AS [ShipName],
[Project2].[ShippedDate] AS [ShippedDate],
[Project2].[ShipPostalCode] AS [ShipPostalCode],
[Project2].[ShipRegion] AS [ShipRegion],
[Project2].[ShipVia] AS [ShipVia],
[Project2].[CustomerID1] AS [CustomerID1]
FROM ( SELECT
[Filter1].[Address] AS [Address],
[Filter1].[City] AS [City],
[Filter1].[CompanyName] AS [CompanyName],
[Filter1].[ContactName] AS [ContactName],
[Filter1].[ContactTitle] AS [ContactTitle],
[Filter1].[Country] AS [Country],
[Filter1].[CustomerID] AS [CustomerID],
[Filter1].[Fax] AS [Fax],
[Filter1].[Phone] AS [Phone],
[Filter1].[PostalCode] AS [PostalCode],
[Filter1].[Region] AS [Region],
[Limit1].[OrderDate] AS [OrderDate],
[Limit1].[OrderID] AS [OrderID],
1 AS [C1],
[Extent3].[CustomerID] AS [CustomerID1],
[Extent3].[EmployeeID] AS [EmployeeID],
[Extent3].[Freight] AS [Freight],
[Extent3].[OrderDate] AS [OrderDate1],
[Extent3].[OrderID] AS [OrderID1],
[Extent3].[RequiredDate] AS [RequiredDate],
[Extent3].[ShipAddress] AS [ShipAddress],
[Extent3].[ShipCity] AS [ShipCity],
[Extent3].[ShipCountry] AS [ShipCountry],
[Extent3].[ShipName] AS [ShipName],
[Extent3].[ShippedDate] AS [ShippedDate],
[Extent3].[ShipPostalCode] AS [ShipPostalCode],
[Extent3].[ShipRegion] AS [ShipRegion],
[Extent3].[ShipVia] AS [ShipVia],
CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
FROM (SELECT [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], [Extent1].[Country] AS [Country], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[Fax] AS [Fax], [Extent1].[Phone] AS [Phone], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Region] AS [Region]
FROM [dbo].[Customers] AS [Extent1]
WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1 ) AS [Filter1]
OUTER APPLY (SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate], [Project1].[OrderID] AS [OrderID]
FROM ( SELECT
[Extent2].[OrderDate] AS [OrderDate],
[Extent2].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Filter1].[CustomerID] = [Extent2].[CustomerID]
) AS [Project1]
ORDER BY [Project1].[OrderDate] DESC ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Filter1].[CustomerID] = [Extent3].[CustomerID]
) AS [Project2]
ORDER BY [Project2].[OrderDate] DESC, [Project2].[CustomerID] ASC, [Project2].[OrderID] ASC, [Project2].[C3] ASC
which returns this result:
CompanyName = Ana Trujillo Emparedados y helados, OrderID1 = 10926
CompanyName = Antonio Moreno Taquería, OrderID1 = 10856
CompanyName = Ana Trujillo Emparedados y helados, OrderID1 = 10759
CompanyName = Antonio Moreno Taquería, OrderID = 10682
CompanyName = Antonio Moreno Taquería, OrderID = 10677
CompanyName = Ana Trujillo Emparedados y helados, OrderID = 10625
CompanyName = Antonio Moreno Taquería, OrderID = 10573
CompanyName = Antonio Moreno Taquería, OrderID = 10535
CompanyName = Antonio Moreno Taquería, OrderID = 10507
CompanyName = Antonio Moreno Taquería, OrderID = 10365
CompanyName = Ana Trujillo Emparedados y helados , OrderID = 10308
The include is correct here BUT not my LINQ To Entities result.
As it seems that the EF Materializer doesn't consider identical adjacent rows to determine customers, I think EF should generate this SQL Query:
SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region],
[Project2].[OrderID] AS [OrderID],
[Project2].[C1] AS [C1],
[Project2].[C3] AS [C2],
[Project2].[C2] AS [C3],
[Project2].[EmployeeID] AS [EmployeeID],
[Project2].[Freight] AS [Freight],
[Project2].[OrderDate1] AS [OrderDate],
[Project2].[OrderID1] AS [OrderID1],
[Project2].[RequiredDate] AS [RequiredDate],
[Project2].[ShipAddress] AS [ShipAddress],
[Project2].[ShipCity] AS [ShipCity],
[Project2].[ShipCountry] AS [ShipCountry],
[Project2].[ShipName] AS [ShipName],
[Project2].[ShippedDate] AS [ShippedDate],
[Project2].[ShipPostalCode] AS [ShipPostalCode],
[Project2].[ShipRegion] AS [ShipRegion],
[Project2].[ShipVia] AS [ShipVia],
[Project2].[CustomerID1] AS [CustomerID1]
FROM ( SELECT
[Filter1].[Address] AS [Address],
[Filter1].[City] AS [City],
[Filter1].[CompanyName] AS [CompanyName],
[Filter1].[ContactName] AS [ContactName],
[Filter1].[ContactTitle] AS [ContactTitle],
[Filter1].[Country] AS [Country],
[Filter1].[CustomerID] AS [CustomerID],
[Filter1].[Fax] AS [Fax],
[Filter1].[Phone] AS [Phone],
[Filter1].[PostalCode] AS [PostalCode],
[Filter1].[Region] AS [Region],
[Limit1].[OrderDate] AS [OrderDate],
[Limit1].[OrderID] AS [OrderID],
1 AS [C1],
[Extent3].[CustomerID] AS [CustomerID1],
[Extent3].[EmployeeID] AS [EmployeeID],
[Extent3].[Freight] AS [Freight],
[Extent3].[OrderDate] AS [OrderDate1],
[Extent3].[OrderID] AS [OrderID1],
[Extent3].[RequiredDate] AS [RequiredDate],
[Extent3].[ShipAddress] AS [ShipAddress],
[Extent3].[ShipCity] AS [ShipCity],
[Extent3].[ShipCountry] AS [ShipCountry],
[Extent3].[ShipName] AS [ShipName],
[Extent3].[ShippedDate] AS [ShippedDate],
[Extent3].[ShipPostalCode] AS [ShipPostalCode],
[Extent3].[ShipRegion] AS [ShipRegion],
[Extent3].[ShipVia] AS [ShipVia],
CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
FROM (SELECT [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], [Extent1].[Country] AS [Country], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[Fax] AS [Fax], [Extent1].[Phone] AS [Phone], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Region] AS [Region]
FROM [dbo].[Customers] AS [Extent1]
WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1 ) AS [Filter1]
OUTER APPLY (SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate], [Project1].[OrderID] AS [OrderID]
FROM ( SELECT
[Extent2].[OrderDate] AS [OrderDate],
[Extent2].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Filter1].[CustomerID] = [Extent2].[CustomerID]
) AS [Project1]
ORDER BY [Project1].[OrderDate] DESC ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Filter1].[CustomerID] = [Extent3].[CustomerID]
) AS [Project2]
ORDER BY [Project2].[OrderDate] DESC, [Project2].[CustomerID] ASC, [Project2].[OrderID] ASC, [Project2].[C3] ASC
Indeed the ORDER By OrderDate is done before.