AsEnumerable, pas forcément pour faire du LINQ To Object
L'extension method AsEnumerable permet de passer de IQueryable<T> à IEnumerable<T>. Cela permet par exemple d'appeler dans une requêtes LINQ To SQL (ou LINQ To Entities) des méthodes non gérées par LINQ To SQL/Entities.
Cependant, cette méthode peut avoir d'autres intérêts.
Imaginons que l'on veuille récupérer tous les Orders (de Northwind) dont la date serait supérieur ou égale à 1998 avec leur propriété Customer rempli si et seulement si le pays est la france avec l'Entity Framework (sans Lazy Loading).
Pour cela, il faut charger dans le contexte les customers qui vont bien et retourner les orders qui vont bien.
Comment faire cela en une seule requête LINQ ?
On pourrait imaginer que ce code marche :
from oc in
from o in context.Orders
where o.OrderDate.HasValue && o.OrderDate.Value.Year >= 1998
select new { Order = o, Customer = context.Customers.Where(c => c.CustomerID == o.Customers.CustomerID && c.Country == "FRANCE").FirstOrDefault() }
select oc.Order;
Mais non. En effet, la requête SQL ne récupérant au final que les Orders, les Customers ne seront pas chargés dans le contexte.
La requête SQL générée sera la suivante :
SELECT
1 AS [C1],
[Filter1].[OrderID] AS [OrderID],
[Filter1].[EmployeeID] AS [EmployeeID],
[Filter1].[OrderDate] AS [OrderDate],
[Filter1].[RequiredDate] AS [RequiredDate],
[Filter1].[ShippedDate] AS [ShippedDate],
[Filter1].[ShipVia] AS [ShipVia],
[Filter1].[Freight] AS [Freight],
[Filter1].[ShipName] AS [ShipName],
[Filter1].[ShipAddress] AS [ShipAddress],
[Filter1].[ShipCity] AS [ShipCity],
[Filter1].[ShipRegion] AS [ShipRegion],
[Filter1].[ShipPostalCode] AS [ShipPostalCode],
[Filter1].[ShipCountry] AS [ShipCountry],
[Filter1].[CustomerID] AS [CustomerID]
FROM (SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], [Extent1].[RequiredDate] AS [RequiredDate], [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], [Extent1].[ShipRegion] AS [ShipRegion], [Extent1].[ShipPostalCode] AS [ShipPostalCode], [Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
WHERE ([Extent1].[OrderDate] IS NOT NULL) AND ((DATEPART (year, [Extent1].[OrderDate])) >= 1998) ) AS [Filter1]
OUTER APPLY (SELECT TOP (1) [Extent2].[CustomerID] AS [CustomerID], [Extent2].[CompanyName] AS [CompanyName], [Extent2].[ContactName] AS [ContactName], [Extent2].[ContactTitle] AS [ContactTitle], [Extent2].[Address] AS [Address], [Extent2].[City] AS [City], [Extent2].[Region] AS [Region], [Extent2].[PostalCode] AS [PostalCode], [Extent2].[Country] AS [Country], [Extent2].[Phone] AS [Phone], [Extent2].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent2]
WHERE ([Extent2].[CustomerID] = [Filter1].[CustomerID]) AND (N'FRANCE' = [Extent2].[Country]) ) AS [Limit1]
Notons qu'après l'optimisation de SQL Server, cette requête a le même plan d'exécution que celle-ci :
SELECT
1 AS C1,
OrderID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
CustomerID
FROM Orders
WHERE (OrderDate IS NOT NULL) AND ((DATEPART (year, OrderDate)) >= 1998)
En revanche, si on utilise cette requête LINQ :
from oc in
(from o in context.Orders
where o.OrderDate.HasValue && o.OrderDate.Value.Year >= 1998
select new { Order = o, Customer = context.Customers.Where(c => c.CustomerID == o.Customers.CustomerID && c.Country == "FRANCE").FirstOrDefault() }
).AsEnumerable()
select oc.Order;
la requête SQL va ramener les orders qui vont bien mais également les customers qui vont bien :
SELECT
1 AS [C1],
1 AS [C2],
[Filter1].[OrderID] AS [OrderID],
[Filter1].[EmployeeID] AS [EmployeeID],
[Filter1].[OrderDate] AS [OrderDate],
[Filter1].[RequiredDate] AS [RequiredDate],
[Filter1].[ShippedDate] AS [ShippedDate],
[Filter1].[ShipVia] AS [ShipVia],
[Filter1].[Freight] AS [Freight],
[Filter1].[ShipName] AS [ShipName],
[Filter1].[ShipAddress] AS [ShipAddress],
[Filter1].[ShipCity] AS [ShipCity],
[Filter1].[ShipRegion] AS [ShipRegion],
[Filter1].[ShipPostalCode] AS [ShipPostalCode],
[Filter1].[ShipCountry] AS [ShipCountry],
[Filter1].[CustomerID] AS [CustomerID],
[Limit1].[CustomerID] AS [CustomerID1],
[Limit1].[CompanyName] AS [CompanyName],
[Limit1].[ContactName] AS [ContactName],
[Limit1].[ContactTitle] AS [ContactTitle],
[Limit1].[Address] AS [Address],
[Limit1].[City] AS [City],
[Limit1].[Region] AS [Region],
[Limit1].[PostalCode] AS [PostalCode],
[Limit1].[Country] AS [Country],
[Limit1].[Phone] AS [Phone],
[Limit1].[Fax] AS [Fax]
FROM (SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], [Extent1].[RequiredDate] AS [RequiredDate], [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], [Extent1].[ShipRegion] AS [ShipRegion], [Extent1].[ShipPostalCode] AS [ShipPostalCode], [Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
WHERE ([Extent1].[OrderDate] IS NOT NULL) AND ((DATEPART (year, [Extent1].[OrderDate])) >= 1998) ) AS [Filter1]
OUTER APPLY (SELECT TOP (1) [Extent2].[CustomerID] AS [CustomerID], [Extent2].[CompanyName] AS [CompanyName], [Extent2].[ContactName] AS [ContactName], [Extent2].[ContactTitle] AS [ContactTitle], [Extent2].[Address] AS [Address], [Extent2].[City] AS [City], [Extent2].[Region] AS [Region], [Extent2].[PostalCode] AS [PostalCode], [Extent2].[Country] AS [Country], [Extent2].[Phone] AS [Phone], [Extent2].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent2]
WHERE ([Extent2].[CustomerID] = [Filter1].[CustomerID]) AND (N'FRANCE' = [Extent2].[Country]) ) AS [Limit1]
ceux-ci seront donc présents dans le contexte nous permettant d'arriver au résultat souhaité.
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 :
