9

I have a performance question regarding how LINQ performs with a foreach over an queryable entities from Entity Framework.

Is it better (and faster) to do either

foreach(var thing in myentities.GetThemAll())

or

foreach(var thing in myentities.GetThemAll().ToList())

Will the first example actually result in myentities.GetThemAll().Count() (+1 ?) trips to the database each collecting one row?

Chris
  • 26,744
  • 48
  • 193
  • 345

2 Answers2

11

It is better, if you have only to iterate through your elements to not call ToList(). This is because when we call it, an immediate execution of the corresponding query is triggered and one in memory collection will be created.

If you don't call ToList you will avoid the creation of the in memory collection that will hold the results of your query.

Either you follow the first way, either the second, you will make one round trip to the database.

Christos
  • 53,228
  • 8
  • 76
  • 108
  • So if I just do a foreach on an entities query it will still do just one query (to fetch all the results) then iterate over them in memory rather than going back to the database for each result? – Chris Nov 13 '14 at 17:26
  • Thanks for the insight but how does that match up with this question which states the opposite? http://stackoverflow.com/questions/3575886/slow-foreach-on-a-linq-query-tolist-boosts-performance-immensely-why-is – Chris Nov 13 '14 at 17:28
  • Calling ToList brings result in memory – Ehsan Sajjad Nov 13 '14 at 17:43
  • 2
    @Chris please notice the first comment under the accepted answer, `The whole collection is fetched when the enumerator is first used`. When we make use of `foreach`, under the hood we get an `Enumerator`, which will help us to iterate through the elements of the sequence we have. – Christos Nov 13 '14 at 17:44
3

There's a balance to strike here, and it depends on context.

The first one streams the results from the database, so doesn't load it all into memory at once, this is good if you only need to iterate once, and clients know that they are dealing with something that is coming from the database. It saves on memory, and initial execution time.

With the ToList() it will do the full query, and load every item into memory prior to doing the foreach. This is good in the way that you have your data access over all in one go, which could be beneficial if you will be referring to the enumerable multiple times within the method or don't want to keep the connection open for long.

If memory is an issue, go for the first one, otherwise, its probably simpler to use ToList() when dealing with entity framework outside of a repository.

gmn
  • 4,199
  • 4
  • 24
  • 46