Avoiding multiple query execution in ASP.NET

Many times you will create a LINQ to Entities query and then iterate over its result. The iteration causes the query to execute. If you iterate over the query twice, the second iteration triggers a new query to the database instead of reusing the previous query data.

You have to iterate over the result of a query multiple times. Because you know such a query always returns the same data, you don’t want the query to be executed each time.

The solution to this problem is pretty easy. The first time you perform the query, you download data into a List<T> class. All the iterations use the in-memory list instead of the query result. The following listing shows how you can do this easily.

var customers = ctx.Customers.ToList();
foreach (var c in customers) { ... }
foreach (var c in customers) { ... }

This simple tweak really makes a difference. Unexpected query execution is one of the most common causes of slow performance that you’ll encounter in applications.