Linq- AsEnumerable and AsQueryable

Both of these operators belong to Conversion Operators category.

AsQueryable operator: There are 2 overloaded versions of this method. 

One overloaded version converts System.Collections.IEnumerable to System.Linq.IQueryable

The other overloaded version converts a generic System.Collections.Generic.IEnumerable<T> to a generic System.Linq.IQueryable<T>

The main use of AsQueryable operator is unit testing to mock a queryable data source using an in-memory data source.

EmployeeDBDataContext dbContext = new EmployeeDBDataContext();

            // TOP 5 Male Employees By Salary

            var result = dbContext.Employees.Where(x => x.Gender == "Male")

                                    .OrderByDescending(x => x.Salary).Take(5);

            Console.WriteLine("Top 5 Salaried Male Employees");

            foreach (Employee e in result)

            {

                Console.WriteLine(e.Name + "\t" + e.Gender + "\t" + e.Salary);

            }

After running above query, open SQL Profiler and run a new trace and then run the console application.

* : Notice that the following SQL Query is executed against the database. 

exec sp_executesql N'SELECT TOP (5) [t0].[ID], [t0].[Name], [t0].[Gender], [t0].[Salary]

FROM [dbo].[Employees] AS [t0]

WHERE [t0].[Gender] = @p0

ORDER BY [t0].[Salary] DESC',N'@p0 nvarchar(4000)',@p0=N'Male'

Now, Change the LINQ query in the console application 

FROM

var result = dbContext.Employees.Where(x => x.Gender == "Male")

                                                          .OrderByDescending(x => x.Salary).Take(5);

TO 

var result = dbContext.Employees.AsEnumerable()

                                                          .Where(x => x.Gender == "Male")

                                                          .OrderByDescending(x => x.Salary).Take(5);

Run the console application and notice the query generated in SQL Profiler.

SELECT [t0].[ID], [t0].[Name], [t0].[Gender], [t0].[Salary]

FROM [dbo].[Employees] AS [t0]

AsEnumerable operator breaks the query into 2 parts
1. The "inside part" that is the query before AsEnumerable operator is executed as Linq-to-SQL
2. The "ouside part" that is the query after AsEnumerable operator is executed as Linq-to-Objects

So in this example the following SQL Query is executed against SQL Server, all the data is brought into the console application and then the WHERE, ORDERBY & TOP operators are applied on the client-side

SELECT [t0].[ID], [t0].[Name], [t0].[Gender], [t0].[Salary]

FROM [dbo].[Employees] AS [t0]

So in short, use AsEnumerable operator to move query processing to the client side.

The main difference, from a user's perspective, is that, when you use IQueryable (with a provider that supports things correctly), you can save a lot of resources.

For example, if you're working against a remote database, with many ORM systems, you have the option of fetching data from a table in two ways, one which returns IEnumerable, and one which returns an IQueryable. 

Say, for example, you have a Products table, and you want to get all of the products whose cost is >$25.

If you do:
IEnumerable products = myORM.GetProducts();
var productsOver25 = products.Where(p => p.Cost >= 25.00);

What happens here, is the database loads all of the products, and passes them across the wire to your program. Your program then filters the data. In essense, the database does a "SELECT * FROM Products", and returns EVERY product to you.

With the right IQueryable provider, on the other hand, you can do:

IQueryable products = myORM.GetQueryableProducts();
var productsOver25 = products.Where(p => p.Cost >= 25.00);
The code looks the same, but the difference here is that the SQL executed will be "SELECT * FROM Products WHERE Cost >= 25".

From your POV as a developer, this looks the same. However, from a performance standpoint, you may only return 2 records across the network instead of 20,000.

 

Posted in: LINQ