LINQ: DataContext Life and System.Data.SqlClient.SqlException: Timeout expired.

Question

Hi there,

i seem to be getting a lot of this in my Linq 2 SQL

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

There is really no reason for it, its a simple query that returns 1 record.

I was thinking about opening my datacontext within a Using statement on each method that needs it, i currently am using a private module level variable to open the datacontext..

Is this recommended?

I don't see why it would be timing out, i can only think that i have too many data contexts around....

Any ideas?

Answer 1

There is a good article here (Best practice and effective way of using DataContext in Linq to SQL) about Linq 2 SQL data context best practices... Might be worth a read :)

Answer 2

The datacontext is designed to be opened when you use it, and then thrown away. For example:

using (var dc = new MyDataContext())
    dc.sp_DailyJob();

Right after the using block, the datacontext is released back to the connection pool.

Now, if you cache a DataContext in a local module, it might go unused for a while. Then SQL Server will eventually close your connection because it's idle for too long. The next call generates the Timeout expired message.

I'd remove the caching of the DataContext, and keep create a new DataContext for every call or query you run. Dispose them as soon as you can. There's no performance overhead in that, because the connections are cached by a highly optimized connection pool.