66

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?

Ocelot20
  • 10,510
  • 11
  • 55
  • 96

6 Answers6

84

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().

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • 53
    It's certainly incorrect to think that "nothing really happens". While `AsEnumerable` doesn't evaluate the query at the time that it's called , it *definitely* has an effect. Anything further called on the query will be evaluated using LINQ to objects, so you can't compose additional elements onto the query (another `Where` or an `OrderBy` or anything of that nature) that will become part of the SQL statement. – Adam Robinson Aug 02 '10 at 17:50
  • 1
    A really great example of what this does and why you want to use it: Suppose you're building up a query and the first block ends with `OrderBy(...)`, the type is now `IOrderedEnumerable` so later down the road you can keep appending `ThenBy(...)` and even later you can then say `return originalQuery.AsEnumerable()` to cast it back to a regular `IEnumerable` – The Muffin Man Jun 08 '13 at 04:19
  • 2
    I prefer ToArray which does the same thing, unless you specifically need the List implementation. – Rush Frisby Dec 11 '13 at 20:57
  • 3
    @rushonerok `ToList()` is faster, so unless your objects are living for a long time, use `ToList` and not `ToArray`, see http://stackoverflow.com/a/16323412/691294 – flindeberg Sep 08 '15 at 08:57
  • @flindeberg apparently they are the same speed if the Count is known before hand.See Scott Rippey's comment: https://stackoverflow.com/a/1106012/2063755 – David Klempfner Oct 07 '19 at 10:21
  • 1
    @Backwards_Dave Well, yes, I do not disagree with that. It seems the statement I was refuting is removed now, but the general case is that allocating more memory than needed (i.e. `ToList()`) is faster than ensuring just the right amount memory (i.e. `ToArray()`) is faster. My argument is rather, if you do not know which one to use, use `ToList()` since its abstraction is higher and you do not get a performance penalty. – flindeberg Oct 07 '19 at 10:54
20

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).

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
  • 1
    But the OP's point is that he assumed what you say is true, but empirical test show that it is not. – James Curran Aug 02 '10 at 16:47
  • -1 This is simply not true. IQueryable implements IEnumerable so the call to AsEnumerable is a no-op and doesn't force query execution. – Justin Niessner Aug 02 '10 at 16:58
  • 14
    @James, Justin: You misunderstand. I never said that `AsEnumerable()` would cause the query to evaluate, I said that the only thing that adding it would do is that *when* the aggregates are evaluated, they'll be done client side (the entire result set will be enumerated *on the client*, and the aggregate will be calculated) instead of being translated into a SQL statement. – Adam Robinson Aug 02 '10 at 17:48
  • It's good to note that you might want to use `AsEnumerable()` when you have an `IOrderedEnumerable` because you ended the first part of your query with some type of OrderBy. – The Muffin Man Jun 08 '13 at 04:21
  • 1
    @JustinNiessner That comment is plain factual wrong, how is static type casting **EVER** a `NOP`? It changes the entire execution scheme... which is kinda important since LINQ is built as extension methods (ie static types) not inheritance (dynamic/runtime types). – flindeberg Sep 08 '15 at 09:08
6

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 into an IEnumerable, tricking the compiler into treating the rest of the query as locally executed.

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 - Why LINQ to Entities does not recognize the method 'System.String ToString()?

According to ASENUMERABLE - codeblog.jonskeet, AsEnumerable can be handy

Finally, also see this related question: Returning IEnumerable vs. IQueryable

LCJ
  • 22,196
  • 67
  • 260
  • 418
3

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.

mipe34
  • 5,596
  • 3
  • 26
  • 38
James Curran
  • 101,701
  • 37
  • 181
  • 258
1

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.

Sergio
  • 9,761
  • 16
  • 60
  • 88
1

Just adding a little more clarification:

I thought based on my reading that .AsEnumerable() would execute the query using LINQ-to-SQL

It will not execute the query right away, as Justin's answer explains. It only will be materialized (hit the database) later on.

Instead, when I do this in LINQPad, I see the same query is run three times.

Yes, and note that all three queries are exact the same, basically fetching all rows from the given condition into memory and then computing the count/sum/avg locally.

If I replace .AsEnumerable() with .ToList(), it only gets queried once.

But still getting all data into memory, with the advantage that now it run only once.

If performance improvement is a concern, just remove .AsEnumerable() and then the count/sum/avg will be translated correctly to their SQL correspondents. Doing so three queries will run (probably faster if there are index satisfying the conditions) but with a lot less memory footprint.

Jonatas
  • 88
  • 1
  • 8