Why use .AsEnumerable() rather than casting to IEnumerable<T>?

Readability is the main issue here. Consider that

Table.AsEnumerable().Where(somePredicate)

is far more readable than

((IEnumerable<TableObject>)Table).Where(somePredicate).

Or imagine wanting to execute part of the query on the SQL Server and the rest in memory:

Table.Where(somePredicate)
     .Select(someProjection)
     .AsEnumerable()
     .SomethingElse()

versus

((IEnumerable<SomeProjectionType>)Table.Where(somePredicate)
                                       .Select(someProjection))
                                       .SomethingElse()

Now, as for why such a method is useful at all think of the example of a Table in a LINQ to SQL DataContext. As Table is an IQueryable it implements IEnumerable. When you invoke a Where method on such a Table and enumerate through the results, code is executed that eventually causes a SQL statement to be executed on a SQL Server. What AsEnumerable does is says, no, I don't want to use the LINQ to SQL provider to execute the Where, I want to use the LINQ to Objects implementation of Where.

Thus enumerating over

Table.Where(somePredicate)

causes a query to be executed on a SQL Server whereas enumerating over

Table.AsEnumerable().Where(somePredicate)

brings the table represented by Table into memory and executes the Where functionality in memory (and not on the SQL Server!)

This is the point of AsEnumerable: to allow you to hide a specific implementation of IEnumerable methods and instead use the standard implementation.

I've thought of a reason apart from readability, though related to query implementation: using Linq to Objects on anonymous types returned via another Linq provider. You can't cast to an anonymous type (or a collection of anonymous types), but you can use .AsEnumerable() to perform the cast for you.

Example:

// Get an IQueryable of anonymous types.
var query = from p in db.PeopleTable /* Assume Linq to SQL */
            select new { Name = p.Name, Age = p.Age };

// Execute the query and pull the results into an IEnumerable of anonymous types
var @enum = query.AsEnumerable();

// Use Linq to Objects methods to further refine.
var refined = from p in @enum
              select new
              {
                  Name = GetPrettyName(p.Name),
                  DOB = CalculateDOB(p.Age, DateTime.Now)
              };

Clearly the reason here is that we want to use something like Linq to SQL to pull down some records into an anonymous type, then perform some custom logic (that wouldn't be possible via Linq to SQL) using Linq to Objects on the client-side.

Casting to IEnumerable<_anon> isn't possible, so .AsEnumerable() is the only way to go.

Thanks everyone who answered to help me piece this together. =)

As I'm reading the book C# 6.0 in a Nutshell. Below is an example of AsEnumerable in the book.

---

The purpose is to cast an IQueryable<T> sequence to IEnumerable<T>, forcing subsequent query operators to bind to Enumerable operators instead of Queryable operators. This causes the remainder of the query to execute locally.

To illustrate, suppose we had a MedicalArticles table in SQL Server and wanted to use LINQ to SQL or EF to retrieve all articles on influenza whose abstract contained less than 100 words. For the latter predicate, we need a regular expression:

Regex wordCounter = new Regex (@"\b(\w|[-'])+\b");

var query = dataContext.MedicalArticles
            .Where (article => article.Topic == "influenza" &&
            wordCounter.Matches (article.Abstract).Count < 100);

The problem is that SQL Server doesn’t support regular expressions, so the LINQ-to-db providers will throw an exception, complaining that the query cannot be translated to SQL. We can solve this by querying in two steps: first retrieving all articles on influenza through a LINQ to SQL query, and then filtering locally for abstracts of less than 100 words:

Regex wordCounter = new Regex (@"\b(\w|[-'])+\b");

IEnumerable<MedicalArticle> sqlQuery = dataContext.MedicalArticles
    .Where (article => article.Topic == "influenza");

IEnumerable<MedicalArticle> localQuery = sqlQuery
    .Where (article => wordCounter.Matches (article.Abstract).Count < 100);

With AsEnumerable, we can do the same in a single query:

var query = dataContext.MedicalArticles
      .Where (article => article.Topic == "influenza")
      .AsEnumerable()
      .Where (article => wordCounter.Matches (article.Abstract).Count < 100);

An alternative to calling AsEnumerable is to call ToArray or ToList. The advantage of AsEnumerable is that it doesn’t force immediate query execution, nor does it create any storage structure.