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.

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

3 Responses to AsEnumerable: not only to use unsupported methods

  1. Hi, Matthieu,

    I can’t duplicate what I thought you were trying to accomplish with your T-SQL, but a much simpler query seems to work for me.

    Check out http://oakleafblog.blogspot.com/2008/06/matthieu-mezil-illustrates-use-of.html.

    Cheers,

    –rj

  2. Hi Roger.
    My post wasn’t clear so I change it.
    I think the difference is that you do a LINQ to SQL query and I do a LINQ To Entities query.

  3. To see what I mean, try this:

    static void Main(string[] args)

    {

        using (var context = new NorthwindEntities())

        {

            var q = 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.Customer.CustomerID && c.Country == “FRANCE”).FirstOrDefault() }

                    select oc.Order;

            foreach (var order in q)

                if (order.Customer != null)

                    Console.WriteLine(order.OrderID);

        }

        Console.WriteLine(“—“);

        using (var context = new NorthwindEntities())

        {

            var q = 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.Customer.CustomerID && c.Country == “FRANCE”).FirstOrDefault() }

                        ).AsEnumerable()

                    select oc.Order;

            foreach (var order in q)

                if (order.Customer != null)

                    Console.WriteLine(order.OrderID);

     

        }

    }

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>