AsEnumerable: not only to use unsupported methods
AsEnumerable extension method allow us to use an IEnumerable<T> instead of an IQueryable<T>. This allows, for example, to call an unsupported method by LINQ To Entities in a LINQ To Entities request.
However, this method can have another interest.
Imagine that you want to get Northwind orders if date is greater or equal than 1998 with their property Customer only if country is France with a LINQ to Entities request.
To do this, you need to load into our context the right orders and customers.
How to do this with only one LINQ request?
We can imagine that this one is good:
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;
But no. Indeed, the SQL request only gets the Orders. So Customers aren't loaded into the context.
The generated SQL request is following:
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]
Note that with SQL Server optimizations, this request has the same execution plan than this one :
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)
Now, if we use AsEnumerable like this
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;
the SQL request gets the orders we want and the customers we want:
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]
So they are loaded into the context and we have what we expected.