LINQ To Entities : de plus en plus étrange
Afin de comprendre ce qui se passait dans ce que je pense être un bug sur LINQ To Entities, j'ai analysé les requêtes SQL générées par EF.
Pour la première requête LINQ To Entities:
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;
j'obtiens (avec foreach (var c in cQuery) Console.WriteLine(c.CompanyName);):
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
La requête SQL générée est la suivante :
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
Vu qu'il n'y a pas d'Order dans le résultat, cela signifie que le Include n'est pas pris en compte !
Avec la deuxième requête:
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;
j'obtiens :
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
la requête SQL générée est la suivante :
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
ce qui donne ceci :
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
Le Include est correct mais pas le résultat de la requête LINQ To Entities.
Vu que l'EFMaterializer semble ignorer les Customers identiques qui se suivent (pour la récupération des Customers), il faudrait que la requête SQL soit la suivante :
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
En effet, le ORDER BY OrderDate est déjà fait dans la sous-requête.
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
