CODEWITHSUNDEEP
c#linq
cllpse
cllpse

Reputation: 21727

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

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
}

Upvotes: 13

Views: 8827

Answers (3)

Amy B
Amy B

Reputation: 110221

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 define, but do not execute, an operation.

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)

Upvotes: 13

peSHIr
peSHIr

Reputation: 6370

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

Upvotes: 0

NetSide
NetSide

Reputation: 3900

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.

Upvotes: 9

Related Questions