Linq to SQL data source best practice

Question

When using linq to SQL in my project I am currently creating the data context as late as possible in the code execution and disposing it as soon as possible. This results in the data context being open and closed many times on a page.

Another option is to open the data class on page load and dispose it on page unload so the connection is only opened and closed once.

Is there a significant difference in the optimization between the two methods or does one represent a best practice?

My original thought was that I wanted the connection open as little time as possible but considering how fast a page load is am I costing myself more time in opening and closing these connections than the resource hit of keeping it open is worth?

Answer 1

One thing to consider: the DataContext's lifetime doesn't effect how long the SQL Server connection is kept open. The SQL statement generated by your Linq To Sql is only executed when you evaluate your query.

So when you call (for example), .ToList(), or call .DataBind(), then the SQL statement is run. The connection is opened and closed at that time.

Answer 2

Behind the scenes the LinQ to SQL manager uses a connection pool IIRC. So unless you explicitly kill the connection somehow that should not be your concern.