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.

This entry was posted in 7671, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>