Change The Default CommandTimeout of LINQ DataContext

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();
//Other DataContext specific methods
#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()
    {
        //Put your desired timeout here.
        this.CommandTimeout = 3600;

        //If you do not want to hard code it, then take it 
        //from Application Settings / AppSettings
        //this.CommandTimeout = Settings.Default.CommandTimeout;
    }
}

This is it! This is simple because:

1. It remains so silent that the clients of the DataContext class do not need to take care of the CommandTimeout value.
2. The change is only at a single place, which means no code duplication.
3. 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

• 22^nd May, 2008: Initial post