Sql Exception With .net 4 & EF

we are using .net 4 MVC2 with EF & Sql Server 2005.

For some requests, and it happens rarely, and only when we are doing search which is implemented with classes mapped to stored procedures performing full text search, we get the exception:

[SqlException (0x80131904):开发者_开发知识库 Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
   System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) +182
   System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, MergeOption mergeOption, ObjectParameter[] parameters) +218
   System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters) +53

If the search is retried after the error occurs (with same or different term), it works regularly.

Any suggestions well appreciated

Thanks

--MB

THOUGHT 1: The exception seems to happen after the connection to sql server is made, and while the stored procedure is being executed, am I correct? If this is the case, then I would need to extend the query execution timeout and not the connection timeout? Is this possible, where?

THOUGHT 2: Perhaps I am wrong and this could be a stale connection in the pool? In Java world you can pass the sql that is executed on the connection before the app obtains it to make sure the connection is opened and working? Could that be causing this. I have not been able to locate such an option within http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

THOUGHT 3: I'm lost :)

You can try setting the ObjectContext.CommandTimeout property:

Gets or sets the timeout value, in seconds, for all object context operations. A null value indicates that the default value of the underlying provider will be used.

Yes, try increasing the connection timeout, which is in web.config:

<connectionStrings>
    <add name="AdventureWorksEntities" 
         connectionString="metadata=.\AdventureWorks.csdl|.\AdventureWorks.ssdl|.\AdventureWorks.msl;
         provider=System.Data.SqlClient;provider connection string='Data Source=localhost;
         Initial Catalog=AdventureWorks;Integrated Security=True;**Connection Timeout=60;**
         multipleactiveresultsets=true'" providerName="System.Data.EntityClient" />
</connectionStrings>

Also, have you actually tried seeing how long the SPROC takes to execute when you execute it manually?

I was using EF 4.3.1, SQL 2008 and had the same problem with a EF linq query taking longer than the default 30 seconds.

So I set the "Connection Timeout=120;" in the connection string.

Then override the DbContext constructor and set all Command timeouts to use the Connection Timeout from the Connection String;

public class FooContext : DbContext
{
    public FooContext()
    {
        // Set all commands to use the connection timeout from the connection string
        SetCommandTimeout(this.Database.Connection.ConnectionTimeout);
    }

    public void SetCommandTimeout(int timeout)
    {
        // Get the ObjectContext related to this DbContext
        var objectContext = (this as IObjectContextAdapter).ObjectContext;
        objectContext.CommandTimeout = this.Database.Connection.ConnectionTimeout;            
    }
}

If you only wanted to increase timeout on certain queries, then remove the constructor and set as as required;

var db = new FooContext();
db.SetCommandTimeout(120);
db.Bars.ToList();

The answer by Jeff Ogata is correct but it didn't solve the timeout issue that I just had so I thought I would elaborate in case anyone else runs into this. If you create a command to execute a stored procedure the new command has a default timeout of 30 seconds regardless of the timeout set on the context. You will need to adjust this timeout separately. Here's an example:

_context.Database.CommandTimeout = 600; // This will not be used by cmd!
using (var cmd = _context.CreateCommandForStoredProc("StoredProcsName"))
{
    cmd.CommandTimeout = 600;
    await cmd.ExecuteScalarAsync();
}