Am I misunderstanding LINQ to SQL .AsEnumerable()?

Question

Consider this code:

var query = (from a in db.Table
             where a = SomeCondition
             select a.SomeNumber).AsEnumerable();

int recordCount = query.Count();
int totalSomeNumber = query.Sum();
decimal average = query.Average();

Assume "query" takes a very long time to run. I need to get the record count, total SomeNumber's returned, and take an average at the end. I thought based on my reading that .AsEnumerable() would execute the query using LINQ-to-SQL, then use LINQ-to-Objects for the Count, Sum, and Average. Instead, when I do this in LINQPad, I see the same query is run three times. If I replace .AsEnumerable() with .ToList(), it only gets queried once. Am I missing something about what AsEnumerable is/does?

Thanks in advance.

Answer 1

Yes. All that AsEnumerable will do is cause the Count, Sum, and Average functions to be executed client-side (in other words, it will bring back the entire result set to the client, then the client will perform those aggregates instead of creating COUNT() SUM() and AVG() statements in SQL).

Answer 2

I would presume that ToList forces Linq to fetch the records from the database. When you then perform the proceeding calculations they are done against the in memory objects rather than involving the database.

Leaving the return type as an Enumerable means that the data is not fetched until it is called upon by the code performing the calculations. I guess the knock on of this is that the database is hit three times - one for each calculation and the data is not persisted to memory.

Answer 3

Calling AsEnumerable() does not guarantee that the results will be executed immediately.

IQueryable is the interface that allows LINQ to SQL to perform its magic. IQueryable implements IEnumerable so when you call AsEnumerable(), nothing really happens.

To force query execution, you must call ToList().

Answer 4

Well, you are on the right track. The problem is that an IQueryable (what the statement is before the AsEnumerable call) is also an IEnumerable, so that call is, in effect, a nop. It will require forcing it to a specific in-memory data structure (e.g., ToList()) to force the query.