13

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
}
cllpse
  • 21,396
  • 37
  • 131
  • 170
  • 4
    Sounds like you are doing a database call on each iteration. You might run the SQL Profiler to see if that is true... – Yves M. Aug 26 '10 at 14:10
  • Why call AsEnumerable()? AsEnumerable changes an object at compile time to IEnumerable if it already implements IEnumerable. Why not iterate the rows by using the Rows property of a table? – Wix Aug 26 '10 at 14:11
  • 1
    @Wix: `DataTable` doesn't already implement `IEnumerable`. When you call `AsEnumerable` on a `DataTable` you're calling the `DataTableExtensions.AsEnumerable` method, *not* `Enumerable.AsEnumerable`. http://msdn.microsoft.com/en-us/library/system.data.datatableextensions.asenumerable.aspx – LukeH Aug 26 '10 at 14:49
  • 4
    Um... You are complaining about a slow foreach.... but you never actually show us that foreach. Show us some more complete code, and we can probably make it even faster... – James Curran Aug 26 '10 at 15:42
  • In your question you say *But if I call `ToList()` **right after** my `AsEnumerable()`* (which made it an interesting question for me), but in your code you call `ToList` after the `Select`, not `AsEnumerable` (which I think is all about deferred execution - query being run over and over). But without seeing the `Where` on `item` in your `foreach` code its hard to give a conclusive answer. Can you show us how the `Where` is called inside the `foreach` (especially since `ObjectRepresentation` is not an `IEnumerable`)? Most probably you're calling the `Where` on `collection` variable! – nawfal Sep 29 '13 at 05:24

3 Answers3

13

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)
Amy B
  • 108,202
  • 21
  • 135
  • 185
9

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.

NetSide
  • 3,849
  • 8
  • 30
  • 41
  • 1
    In the OP's particular scenario, he may be causing a database query to happen for each iterated record, but it is *not* true in general that enumerating a deferred query with foreach will query each item separately. The whole collection is fetched when the enumerator is first used. – quentin-starin Aug 26 '10 at 15:04
  • 3
    He's querying a datatable, not a Linq DataContext or ObjectContext. There's no way a query on the datatable will cause a DB query to execute – Thomas Levesque Aug 27 '10 at 08:31
  • 3
    yep, you are right. But this is a general explanation to understand what linq does with methods. For each item, it will take some time to get data more than using ToList before for each statement. – NetSide Aug 27 '10 at 11:48
0

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

peSHIr
  • 6,279
  • 1
  • 34
  • 46