Why does LINQ to SQL generate multiple select queries?

I am using LINQ2SQL. I just noticed (in the SQL Profiler) that LINQ was generating multiple select statements for the below LINQ

var tableData 开发者_JAVA技巧= dataContext.TableName.ToList();

The SQL profiler shows the below DML statements twice

Select columnNames from TableName

What is the reason for this?

The query as presented will do exactly one select statement. I wonder if the problem here is hidden in moving the question to a simple example. Most likely in the actual code it doesn't push the data into a list. Methods like .Where() only compose a query - it doesn't load the data into a list, so the following is 2 queries:

var tableData = dataContext.TableName.Where(row => row.Foo == bar);
foreach(var items in tableData) {...}
foreach(var items in tableData) {...}

Adding a .ToList() will force it to buffer in memory in a single TSQL query; subsequent enumeration over the list will be done in memory (LINQ-to-Objects).

Of course, you can profile what LINQ-to-SQL is executing pretty simply:

dataContext.Log = Console.Out; // or any other text-writer

SQL Server Profiler subscribes to the following events by default;

Audit Login
Audit Logout
Existing Connection
RPC:Completed
SQL:BatchCompleted
SQL:BatchStarting

If you start the profiler with these default events you see every batch statement repeated 2 times just because of SQL:BatchStarting and SQL:BatchCompleted events. It is a common misconception that worth to be checked.