Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Abonnements

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 :

Publié vendredi 13 juin 2008 20:20 par Matthieu MEZIL

Commentaires

Pas de commentaires

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01