Introduction
At times, the execution of a LINQ to SQL query may take a longer time and exceed the default value of the DataContext
class's CommandTimeout
property. The following article will present a way to achieve an application-wide CommandTimeout
for your DataContext
class.
The Problem
LINQ to SQL codes that invoke a long running database query/stored procedure end up with a System.Data.SqlClient.SqlException
: Timeout expired Exception.
The Solution
The default value of the DataContext
class's CommandTimeout
is set to 30 seconds. Any database queries taking a longer time to complete than 30 seconds will throw a System.Data.SqlClient.SqlException
: Timeout expired Exception.
One solution to this problem is to set the value of the CommandTimeout
before each time a LINQ to SQL DataContext
object is created and such a query is invoked. But the problem with this approach is, it will introduce code duplication and related issues.
We actually want to be able to change the default value of the CommandTimeout
property and we want to do it efficiently. Fortunately enough, the Visual Studio 2008 auto generated DataContext
subclasses provide an easy way to achieve this target using extensibility through partial methods.
The C# Code
Usually, an auto generated DataContext
subclass has a few partial method declarations like the following:
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
#endregion
We are interested in the method called OnCreated()
here. If you take a look into the constructors of the DataContext
subclass, you will notice that this partial method is called from all the overloaded versions of the constructors in the end.
For those who are new to Partial Methods, please see this MSDN documentation. For a brief, partial methods are defined as extensibility points in a class. So, if there is no definition other than the declaration for that partial method [in another partial class], the call is just ignored. However, if there is an implementation, it will be invoked as a regular method.
So, we are going to define the body of the OnCreated()
method in another partial class with the same full name as that of the auto generated class and set the desired timeout value there in the following way:
partial class SampleDBDataContext : System.Data.Linq.DataContext
{
partial void OnCreated()
{
this.CommandTimeout = 3600;
}
}
This is it! This is simple because:
- It remains so silent that the clients of the
DataContext
class do not need to take care of the CommandTimeout
value. - The change is only at a single place, which means no code duplication.
- Placing this method in a separate file in a partial class makes sure this change will persist although the auto generated class may be overwritten when a change takes place in the DBML file.
History
- 22nd May, 2008: Initial post