LINQ to SQL is designed to be a rapid application development tool for smaller applications. With this in mind, you probably wouldn’t expect it to scale particularly well – but this doesn’t mean that you shouldn’t be concerned about how it handles your connections under the hood!I was asked the other day about context lifespan, and how it affects performance. I couldn’t really answer the question with any authority as I didn’t actually know what the data context was doing with the associated connection. So I finally got time this evening to sit and write a couple of tests to find out, and while the results aren’t particularly surprising it’s a good thing to know. Here’s how I went about it…
First things first – if I’m intending to monitor my connection I’ll need to subscribe to some kind of state change event. Luckily for me the DataContext object exposes the underlying connection, and it’s associated StateChange event. This allowed me to find out what state I’d moved from, and to. Next up I wrote a simple LINQ to SQL query to get me around 100 records from a local database, and an insert method to add a record to a table. I then threw in some tracing to let me know what was going on and there you have it – a LINQ to SQL connection monitor. Now, the results?
As I said, they’re not particularly surprising. For the select query we open the connection twice at the beginning of the for loop (my mechanism for forcing the execution), then once more for the duration of the for loop as we iterate across the collection. The same result (3 opens, 3 closes) occurs when we force immediate execution through the use of the .First() extension method.
For the insert statement – just a single connection open and close.
There is a caveat here, in that if you provide your own connection to the DataContext you’re using and you open the connection explicitly before hand the DataContext will attempt to leave it in the state it was provided in. Isn’t it a good guest? 
If you’re interested in learning more about the way LINQ to SQL works with databases there’s plenty of good stuff here
Well….tried both approches.
1. using the connection used when creating the databasemodel and..
2. giving it an open connection
Performancewise the connection-approch was only marginally, and not always faster.
BUT it did solve a problem we had with using the right database when moving from development to live servers, so thanks for this post.
I believe there’s been a bit of work done under the hood since this post so the characteristics may have changed. That said I’m glad it helped!