Does calling ToList multiple times effect performance?

Question

I often times see that calling ToList will make query get executed every time. I'm a little confused about that. So in this scenario, would calling ToList the second time make the query get executed more than once? In what scenarios does the query get execute more than once?

//This gets from database and ToList is already called here
List<Customer> Customers = GetCustomersFromDataBase();

//ToList is called here again
List<Customer> FilteredCustomers = (from c in Customerswhere c.id == 1c).ToList();

Edit

I just provided this code example to understand what ToList really does. I'm not implementing it this way. Also, I make a stored procedure call to return customers from db and create a list of customers from the returned datatable.

Answer 1

Calling ToList (or ToArray, AsEnumerable, etc) on an IQueryable will execute the query. After that any LINQ expression you use will be operating on the in-memory list.

For instance, your code:

List<Customer> Customers = GetCustomersFromDataBase();

This will result in a List of Customer objects which represent the database records. The list is now independent of the query used to fetch the results from the database, although linked records in the Customer objects will still need to be fetched from the database.

Then when you do this:

List<Customer> FilteredCustomers = (from c in Customers where c.id == 1c).ToList();

This is not operating on the database but on the results in memory that were returned from the previous statement. In this case you will not be querying the database multiple times.

Where it does impact performance is when you have an IQueryable object that you call ToList on directly multiple times. Each call to ToList on an IQueryable will result in a call to the database, with all of the attendant object tracking and such in the background. Generally this is something you want to avoid, although additional filtering of the query prior to enumerating the results may result in better performance if the total number of records selected by the query is large and the filters pull small subsets of the data.