Slow foreach() on a LINQ query - ToList() boosts performance immensely - why is this?

Question

I kind of grasp the whole delayed execution concept, but the following has me puzzled...

On a DataTable containing about 1000 rows, I call AsEnumerable(). I then select the entities returned into an IEnumerable of strongly typed classes (1)... Here's where I get confused: I do a foreach loop on the collection; selecting stuff from the individual items in the collection using a bunch of Where() calls (2) ... And it's dead slow.

1. DataTable.AsEnumerable().Select(r => new ObjectRepresentation { ... });
2. item.Where(i => i.SomeEnum == SomeEnum.Something)

... But if I call ToList() right after my AsEnumerable() call on the DataTable, the foreach loop takes less than a second to complete.

What am I missing here? Am I effectively calling AsEnumerable() each time my loop iterates? Or each time I access an item in the collection? Or each time I do a Where() call on an item in the collection? Or all the above?


Update

Somewhat complete code:

public class ObjectRepresentation
{
    public SomeEnum SomeEnum { get; set; }
}


var collection = DataTable.AsEnumerable().Select(r => new ObjectRepresentation
{
    SomeEnum = (SomeEnum)Convert.ToInt32(r["SomeEnum"])
});

foreach(var item in collection) // slow loop
{
    // 10 or so Where() calls on item inside this loop
}

collection = collection.ToList(); // Hit hyper speed button!

foreach(var item in collection) // fast loop
{
    // 10 or so Where() calls on item inside this loop
}

Answer 1

You don't understand which methods are deferred and which are not, so you don't understand when your code defines operations vs performs operations.

These are all deferred, they return a Enumerable based on the source.

source.AsEnumerable
source.Select
source.Where

These enumerate the source and so are not deferred.

source.ToList
source.First
source.Single
foreach(var x in source)

Answer 2

It will not get all items from database until you type

 ToList or First or Single

In foreach, you send a query into database for each item. So it works slower. Open your sql profiler to understand better.

Answer 3

Indeed you seem to have no clear idea what is execution of code and what is definition of the intent to (possibly) execute later when the results are actually used. I suggest reading up one this part of LINQ.

And possibly try executing both of your variants with a debugger attached so you can actually see what code is executing in which order and what is actually happening to your data. You might be in for a (big?) surprise here...