LINQ to SQL and Connections

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


2 thoughts on “LINQ to SQL and Connections

  1. Steen

    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.

    1. Steve Godbold Post author

      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!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s