LINQ To SQL Command Time out

What should we do when our ado.net commands are timing out if we have already optimized our queries / procedures. We know that these commands should take that long. we just want our code to be supporting this delay and not throw any kind of exception.

This is a general scenario we face when we are dealing with data access. In ADO.Net, we access database by creating connections and command objects. If we know that our commands will take long, we increase time out for our commands.

But how should we do that in LINQ to SQL? We don't create any connections are command objects explicitly. Rather we have datacontexts and collections. We generally face timeout issues when we call stored procedures using LINQ To SQL DataContexts. The easiest answer is to change the time out for all data access.

Before presenting the solution let me make this know that LINQ to SQL classes are implemented using Partial classes. You can add definitions to these partial classes in your code. Now, we can define various event handlers in these partial classes other than various other implementation details. One of the event is OnCreated. Here is the solution of this problem by defining OnCreated event handler in partial class of DataContext. The vb.net code is as follows:

Private Sub OnCreated()
        Me.CommandTimeout = Integer.MaxValue
End Sub