Am I misunderstanding LINQ to SQL .AsEnumerable()?
C#.NetLinqLinq to-SqlC# Problem Overview
Consider this code:
var query = db.Table
.Where(t => SomeCondition(t))
.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?
C# Solutions
Solution 1 - C#
Calling AsEnumerable(
) does not execute the query, enumerating it does.
IQueryable
is the interface that allows LINQ to SQL
to perform its magic. IQueryable
implements IEnumerable
so when you call AsEnumerable()
, you are changing the extension-methods being called from there on, ie from the IQueryable
-methods to the IEnumerable
-methods (ie changing from LINQ to SQL
to LINQ to Objects
in this particular case). But you are not executing the actual query, just changing how it is going to be executed in its entirety.
To force query execution, you must call ToList()
.
Solution 2 - C#
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).
Solution 3 - C#
Justin Niessner's answer is perfect.
I just want to quote a MSDN explanation here: .NET Language-Integrated Query for Relational Data
>The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable
I hope this is what is meant by: >IQueryable-methods to the IEnumerable-methods (ie changing from LINQ to SQL to LINQ to Objects
Once it is LINQ to Objects we can apply object's methods (e.g. ToString()). This is the explanation for one of the frequently asked questions about LINQ - https://stackoverflow.com/questions/10110266/why-linq-to-entities-does-not-recognize-the-method-system-string-tostring
According to ASENUMERABLE - codeblog.jonskeet, AsEnumerable
can be handy when:
>some aspects of the query in the database, and then a bit more manipulation in .NET – particularly if there are aspects you basically can’t implement in LINQ to SQL (or whatever provider you’re using).
It also says:
>All we’re doing is changing the compile-time type of the sequence which is propagating through our query from IQueryable
Finally, also see this related question: Returning IEnumerable
Solution 4 - C#
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.
Solution 5 - C#
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.