Why is Entity Framework's AsEnumerable() downloading all data from the server?

Question

What is the explanation for EF downloading all result rows when AsEnumerable() is used?

What I mean is that this code:

context.Logs.AsEnumerable().Where(x => x.Id % 2 == 0).Take(100).ToList();

will download all the rows from the table before passing any row to the Where() method and there could be millions of rows in the table.

What I would like it to do, is to download only enough to gather 100 rows that would satisfy the Id % 2 == 0 condition (most likely just around 200 rows).

Couldn't EF do on demand loading of rows like you can with plain ADO.NET using Read() method of SqlDataReader and save time and bandwidth?

I suppose that it does not work like that for a reason and I'd like to hear a good argument supporting that design decision.

NOTE: This is a completely contrived example and I know normally you should not use EF this way, but I found this in some existing code and was just surprised my assumptions turned out to be incorrect.

Answer 1

The short answer: The reason for the different behaviors is that, when you use IQueryable directly, a single SQL query can be formed for your entire LINQ query; but when you use IEnumerable, the entire table of data must be loaded.

The long answer: Consider the following code.

context.Logs.Where(x => x.Id % 2 == 0)

context.Logs is of type IQueryable<Log>. IQueryable<Log>.Where is taking an Expression<Func<Log, bool>> as the predicate. The Expression represents an abstract syntax tree; that is, it's more than just code you can run. Think of it as being represented in memory, at runtime, like this:

Lambda (=>)
  Parameters
    Variable: x
  Body
    Equals (==)
      Modulo (%)
        PropertyAccess (.)
          Variable: x
          Property: Id
        Constant: 2
      Constant: 0

The LINQ-to-Entities engine can take context.Logs.Where(x => x.Id % 2 == 0) and mechanically convert it into a SQL query that looks something like this:

SELECT *
FROM "Logs"
WHERE "Logs"."Id" % 2 = 0;

If you change your code to context.Logs.Where(x => x.Id % 2 == 0).Take(100), the SQL query becomes something like this:

SELECT *
FROM "Logs"
WHERE "Logs"."Id" % 2 = 0
LIMIT 100;

This is entirely because the LINQ extension methods on IQueryable use Expression instead of just Func.

Now consider context.Logs.AsEnumerable().Where(x => x.Id % 2 == 0). The IEnumerable<Log>.Where extension method is taking a Func<Log, bool> as a predicate. That is only runnable code. It cannot be analyzed to determine its structure; it cannot be used to form a SQL query.

Answer 2

Entity Framework and Linq use lazy loading. It means (among other things) that they will not run the query until they need to enumerate the results: for instance using ToList() or AsEnumerable(), or if the result is used as an enumerator (in a foreach for instance).

Instead, it builds a query using predicates, and returns IQueryable objects to further "pre-filter" the results before actually returning them. You can find more infos here for instance. Entity framework will actually build a SQL query depending on the predicates you have passed it.

In your example:

context.Logs.AsEnumerable().Where(x => x.Id % 2 == 0).Take(100).ToList();

From the Logs table in the context, it fetches all, returns a IEnumerable with the results, then filters the result, takes the first 100, then lists the results as a List.

On the other hand, just removing the AsEnumerable solves your problem:

context.Logs.Where(x => x.Id % 2 == 0).Take(100).ToList();

Here it will build a query/filter on the result, then only once the ToList() is executed, query the database.

It also means that you can dynamically build a complex query without actually running it on the DB it until the end, for instance:

var logs = context.Logs.Where(a); // first filter
if (something) {
    logs = logs.Where(b); // second filter
}
var results = logs.Take(100).ToList(); // only here is the query actually executed

Update

As mentionned in your comment, you seem to already know what I just wrote, and are just asking for a reason.

It's even simpler: since AsEnumerable casts the results to another type (a IQueryable<T> to IEnumerable<T> in this case), it has to convert all the results rows first, so it has to fetch the data first. It's basically a ToList in this case.