Is ToList required when using foreach with LINQ to Entities

Question

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?

Answer 1

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.

Answer 2

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.