Iterating over IQueryable with forEach vs. calling ToList() and then doing forEach - performance

Question

I've been watching one course where the author mentioned that if you have some IQueryable, say:

var someQuery = dbContext.Table.Where(x => x.Name == "Mark");

and then try to iterate over the results of someQuery using foreach, it keeps the database connection open until the whole foreach ends, and a lot of beginner developers make this mistake and do tons of logic in the foreach. Instead, she recommended just calling ToList() up-front and then doing foreach on the in-memory collection.

I can't find any reference on this though, that Entity Framework keeps the database open until the foreach loop stops. How do I evaluate if this is true performance-wise?

Answer 1

The ToList()method applied to an IQueryable<T> is an extension method you can see in System.core.dll with a .NET decompiler; The method copies the "private array" if the source is an ICollection<T>, otherwise it executes a foreach loop from the source.

The real implementation of an an IQueryable<T> is System.Data.Entity.Infrastructure.DbQuery<TResult> and it is not an ICollection<T>.

So, the ToList() basically copy the source using a foreach.

Now, it depends on the implementation, but using EF core as example, there's a Enumerator class where you can see that the Connection (IRelationalConnection) is closed when the class is disposed. According to another answer, and ms documentation, you should close the connection as soon as you can to release the connection and make it available in the connection pool.

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

so, if you are doing complex operations or executing other queries or anything else inside the foreach, the ToList is preferred.