Linq-to-sql context database connection handling

Question

What are the rules for how a linq-to-sql datacontext keeps the database connection open?

The question came up when we made a few tests on performance for one SubmitChanges() per updated entity instead of one SubmitChanges() for the entire batch of entities. Results:

Inserting 3000 items in one SubmitChanges() call... Duration: 1318ms

Inserting 3000 items in one SubmitChanges() call, within transactionscope... Duration: 1280ms

Inserting 3000 items in individual SubmitChanges() calls... Duration: 4377ms

Inserting 3000 items in individual SubmitChanges() calls within a transaction... Duration: 2901ms

Note that when doing individual SubmitChanges() for each changed entity, putting everything within a transaction improves performance, which was quite unexpected to us. In the sql server profiler we can see that the individual SubmitChanges() calls within the transaction do not reset the DB connection for each call, as opposed to the one without the transaction.

In what cases does the data context keep the connection open? Is there any detailed documentation available on how linq-to-sql handles connections?

Answer 1

You aren't showing the entire picture; LINQ-to-SQL will wrap a call to SubmitChanges in a transaction by default. If you are wrapping it with another transaction, then you won't see the connection reset; it can't until all of the SubmitChanges calls are complete and then when the external transaction is committed.

Answer 2

There may be a number of factors that could be influencing the timings besides when connections are opened/closed.

edit: I've removed the bit about tracked entities after realizing how linq2sql manages the cached entities and the dirty entities separately.

You can get a good idea how the connections are managed under the covers by using Reflector or some other disassembler to examine the methods on the SqlConnectionManager class. SubmitChanges will call ClearConnection on its IProvider (typically SqlProvider which then uses SqlConnectionManager) after the submit if it wrapped the submit in its own transaction, but not if the SubmitChanges is part of a larger transaction. When the the connection is opened and closed depends on whether there is other activity making use of the SqlConnectionManager.