There are times, no matter how much optimization you do, that a query just takes a long time to run.  I had one such query in a recent project I was working on where we are using Entity Framework.  The issue I ran into was that the call to the Stored Procedure containing the query would continuously timeout.  I needed a solution and it took some time to find one, so I thought I would share to help alleviate someone else’s pain.

My first thought was that the setting would be added to the connection string for the EF model.  Then it dawned on me that the time in the connection string is just for making the actual connection to the database – duh. Smile  I began looking for similar issues on the various search engines, but I could not find anything comparable.  I then resorted to inspecting the EF documentation on MSDN.  After some searching, I found my answer.   Best of all, the solution was a one-liner (I love those).  Here is the solution:

Using context As New yourContextName()
    context.CommandTimeout = durationInSeconds
    result = context.YourSPCall()
End Using

Basically, once you create your context, you set the CommandTimeout property to the number of seconds to allow the command to execute before throwing a Timeout exception.  Easy!