Linq Queries containing "AsEnumerable()" equivalence

Question

Are this 2 queries functionally equivalent?

1)

var z=Categories
         .Where(s=>s.CategoryName.Contains("a"))
         .OrderBy(s => s.CategoryName).AsEnumerable()
         .Select((x,i)=>new {x.CategoryName,Rank=i});

2)

var z=Categories.AsEnumerable()
         .Where(s=>s.CategoryName.Contains("a"))
         .OrderBy(s => s.CategoryName)
         .Select((x,i)=>new {x.CategoryName,Rank=i});

I mean, does the order of "AsNumerable()" in the query change the number of data items retrieved from the client, or the way they are retrieved?

Thank you for you help.

Answer 1

Are this 2 queries functionally equivalent?

If by equivalent you means to the final results, then probably yes (depending how the provider implements those operations), the difference is in the second query you are using in-memory extensions.

I mean, does the order of "AsNumerable()" in the query change the number of data items retrieved from the client, or the way they are retrieved?

Yes, in the first query, Where and OrderBy will be translated to SQL and the Select will be executed in memory.

In your second query all the information from the database is brought to memory, then is filtered and transformed in memory.

---

Categories is probably an IQueryable, so you will be using the extensions in Queryable class. this version of the extensions receive a Expression as parameter, and these expression trees is what allows transform your code to sql queries.

AsEnumerable() returns the object as an IEnumerable, so you will be using the extensions in Enumerable class that are executed directly in memory.

Answer 2

Yes they do the same thing but in different ways. The first query do all the selection,ordering and conditions in the SQL database itself.

However the second code segment fetches all the rows from the database and store it in the memory. Then after it sorts, orders, and apply conditions to the fetched data i.e now in the memory.

AsEnumerable() breaks the query into two parts:

1. The Inside-Part(query before AsEnumerable) is executed as LINQ-to-SQL
2. The Outside-Part(query after AsEnumerable) is executed as LINQ-to-Objects