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.

Yes, the order matters. See: http://stackoverflow.com/questions/3311244/understanding-asenumerable-in-linq-to-sql — stephen.vakil, Apr 26 '16 at 18:12
This depends on your LINQ provider, but you don't specify which one you're using. LINQ to SQL? Entity Framework? Something else? — , Apr 26 '16 at 18:17
Refer https://stackoverflow.com/questions/10110266/why-linq-to-entities-does-not-recognize-the-method-system-string-tostring/43262421#43262421 — LCJ, Jun 20 '17 at 21:06

Arturo Menchaca · Accepted Answer · 2016-04-26T18:25:04.097

4

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.

edited Apr 26 '16 at 18:25

answered Apr 26 '16 at 18:15

Arturo Menchaca

15,783
1
29
53

3

`If by equivalent you means to the final results, then yes, the difference is in the second query you are using in-memory extensions;` That's not necessarily true. It's possible for the query provider to have slightly (or possibly even significantly) different semantics than the in-memory equivalents of those operations. – Servy Apr 26 '16 at 18:17
@Servy: It's true, Updated the answer. – Arturo Menchaca Apr 26 '16 at 18:22
2

Also `AsEnumerable` doesn't "transform" the collection, it just forces the compiler to bind subseuent calls to `IEnumerable` extension methods instead of `IQueryable`. – D Stanley Apr 26 '16 at 18:24
@Servy Good observation. Something that comes to mind would be the equality operation on a string. The default collation for SQL server is case insensitive, whereas the default string equality comparer IS case sensitive. That means `Where(s => s.SomeString.Equals("Foo"))` could produce different results depending on the order that the extension methods were chained. – DVK Apr 26 '16 at 18:37
@S.Bozzoni: You,re welcome, please consider the answer as accepted. Thank you. – Arturo Menchaca Apr 26 '16 at 20:26

score 1 · Answer 2 · answered Aug 03 '17 at 10:10

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:

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

Linq Queries containing "AsEnumerable()" equivalence

2 Answers2