C# Linq seeing Audit Logout after every read

Question

This is not http://stackoverflow.com/questions/279401/sql-connection-pooling-and-audit-login-logout.

I've got a C# .NET 3.5 app that updates about 30K records on SQL 2008 on a local database.

The logic is it first checks to see if the record exists SingleOrDefault(p => p.stock=stock && p.number=number) and either adds the record or updates.

I'm focusing only on the reads using SingleOrDefault.

I created the datacontext file using SQLMetal.exe.

After every read I see in SQL Profiler:
Audit Logout (1300 reads)
RPC:Completed sp_reset_connection
Audit Login
RPC:Completed sp_executesql 'select etc...'

I created a new project on another machine using 2005 and a different database but got different results.
Audit Login
RPC:Completed sp_execute sql ...
RPC:Completed sp_execute sql ...
RPC:Completed sp_execute sql ...
...

Any ideas?

Answer 1

Wrapping everything in a TransactionState object solved this problem for me. For example:

using (TransactionScope ts = new TransactionScope())
{
   // do db stuff here

   ts.Complete();
}

Note that I did this even though I was only doing database reads.

Also note that you have to add a reference to System.Transactions to your project, if it's not there already.