Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Understanding .AsEnumerable() in LINQ to SQL

Given the following LINQ to SQL query:

var test = from i in Imports            where i.IsActive            select i; 

The interpreted SQL statement is:

SELECT [t0].[id] AS [Id] .... FROM [Imports] AS [t0] WHERE [t0].[isActive] = 1 

Say I wanted to perform some action in the select that cannot be converted to SQL. Its my understanding that the conventional way to accomplish this is to do AsEnumerable() thus converting it to a workable object.

Given this updated code:

var test = from i in Imports.AsEnumerable()            where i.IsActive            select new             {                 // Make some method call             }; 

And updated SQL:

SELECT [t0].[id] AS [Id] ... FROM [Imports] AS [t0]  

Notice the lack of a where clause in the executed SQL statement.

Does this mean the entire "Imports" table is cached into memory? Would this slow performance at all if the table contained a large amount of records?

Help me to understand what is actually happening behind the scenes here.

like image 254
Mike Fielden Avatar asked Jul 22 '10 16:07

Mike Fielden


People also ask

What is the use of AsEnumerable in LINQ?

In LINQ, AsEnumerble() method is used to convert the specific type of given list to its IEnumerable() equivalent type.

How do you use AsEnumerable?

AsEnumerable() in C#To cast a specific type to its IEnumerable equivalent, use the AsEnumerable() method. It is an extension method. int[] arr = new int[5]; arr[0] = 10; arr[1] = 20; arr[2] = 30; arr[3] = 40; arr[4] = 50; Now, get the IEnumerable equivalent.

Does AsEnumerable execute the query?

Calling AsEnumerable( ) does not execute the query, enumerating it does. IQueryable is the interface that allows LINQ to SQL to perform its magic.

What is the difference between AsEnumerable and AsQueryable?

AsEnumerable preserves deferred execution and does not build an often useless intermediate list. On the other hand, when forced execution of a LINQ query is desired, ToList can be a way to do that. AsQueryable can be used to make an enumerable collection accept expressions in LINQ statements.


2 Answers

The reason for AsEnumerable is to

AsEnumerable(TSource)(IEnumerable(TSource)) can be used to choose between query implementations when a sequence implements IEnumerable(T) but also has a different set of public query methods available

So when you were calling the Where method before, you were calling a different Where method from the IEnumerable.Where. That Where statement was for LINQ to convert to SQL, the new Where is the IEnumerable one that takes an IEnumerable, enumerates it and yields the matching items. Which explains why you see the different SQL being generated. The table will be taken in full from the database before the Where extension will be applied in your second version of the code. This could create a serious bottle neck, because the entire table has to be in memory, or worse the entire table would have to travel between servers. Allow SQL server to execute the Where and do what it does best.

like image 155
Yuriy Faktorovich Avatar answered Sep 28 '22 12:09

Yuriy Faktorovich


At the point where the enumeration is enumerated through, the database will then be queried, and the entire resultset retrieved.

A part-and-part solution can be the way. Consider

var res = (     from result in SomeSource     where DatabaseConvertableCriterion(result)     && NonDatabaseConvertableCriterion(result)     select new {result.A, result.B} ); 

Let's say also that NonDatabaseConvertableCriterion requires field C from result. Because NonDatabaseConvertableCriterion does what its name suggests, this has to be performed as an enumeration. However, consider:

var partWay = (     from result in SomeSource     where DatabaseConvertableCriterion(result)     select new {result.A, result.B, result.C} ); var res = (     from result in partWay.AsEnumerable()     where NonDatabaseConvertableCriterion select new {result.A, result.B} ); 

In this case, when res is enumerated, queried or otherwise used, as much work as possible will be passed to the database, which will return enough to continue the job. Assuming that it is indeed really impossible to rewrite so that all the work can be sent to the database, this may be a suitable compromise.

like image 22
Jon Hanna Avatar answered Sep 28 '22 11:09

Jon Hanna