Linq : Significance of AsEnumerable

While LINQ provides a big umbrella to query in-memory as well as remote collections alike (part of the benefit is having a standard vocabulary to query any collection), there are subtle differences, which rises from the way the two classes of Linq queries work.

A Linq query over in-memory collections usually work over the IEnumerable extensions, while the queries over LINQ to SQL work over the IQueryable extensions. This is a subtle difference, considering the IQueryable inherits from IEnumerable, but the consequences can strike you if you are not expecting them.

IQuerable approach (remote query) translates the entire query to the corresponding Database Query and execute it over in the Server.

However, this means one needs to be aware that this approach has its limitations set to the Database Server capabilities, for example, Linq To Sql, is to restricted to the capabilities of the concerned Database Server.

Consider the following example. The following code attempts to read data from Exam table, where the Description Field contains a Date string. For making our life easier, we are using a RegEx.
Regex regex = new Regex(@"([01]?[0-9]|2[0-3]):[0-5][0-9]");
var output = Exams.Take (100).Where(x=> regex.IsMatch(x.Description));

However, this would result in an error as Sql doesn’t recognize regular expression, which means, Linq wouldn’t be able to convert the statement to an equivalent Sql Query.

This is where the IEnumerable.AsEnumerable method comes into picture. Let’s examine the source of the extension method in Referencesource.

  public static IEnumerable<TSource> AsEnumerable<TSource>(this IEnumerable<TSource> source)
   return source;

The method as such, if you were to look up in reference source, might be doing very less, but its significance in the scheme of things is no less important. What it does is that it converts an IQueryable to IEnumerable and there by splits the Linq statement into 2, one that executes on the Server and other, as local collection.

For example, to fix our code above, we would need to change the statement as following.

var output = Exams.Take (100).AsEnumerable().Where(x=> regex.IsMatch(x.Description));

This would split our query into two. The first part would query the Database to fetch 100 rows from Exam table. The second part would then query the resulting collection locally using the Regex expression.

Although the differences are pretty evident once you use it, it is pretty useful to visit the backing classes to understand why it behaves so.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s