efcore: SetCommandTimeout does not SetCommandTimeout for connection.CreateCommand()

Steps to reproduce

var connection = _context.Database.GetDbConnection();
if (connection.State != ConnectionState.Open)
  connection.Open();

_context.Database.SetCommandTimeout(180);
Console.WriteLine(connection.ConnectionTimeout);
using (var command = connection.CreateCommand())
{
  Console.WriteLine(command.CommandTimeout);
  _context.Database.SetCommandTimeout(180);
  Console.WriteLine(command.CommandTimeout);
  command.CommandText = myLongRunningQuery;

  using (var reader = command.ExecuteReader())
  {
    while (reader.Read())
    {
        // do stuff...
    }
  }
}

The issue

I have a medium sized database (a few M records) and I am trying to run a query to create a report. Unfotunately, the most optimized command is not currently supported via LINQ. So, I’m using the above code to run the query. The query takes ~60s to run, but it times out. The Console.WriteLine methods produce the following:

200 // connection timeout
30 // command timeout before trying to set
30 // command timeout after trying to set

The command always times out at 30s. But Context.Database.GetCommandTimeout() = 180. So, am I not creating the command object properly? I would imagine the preferred method is something like: Context.MyEntities.FromSql(query) or Context.Database.ExecuteSqlCommand(query), but the problem is that the query results cannot be mapped to MyEntity and I need the results of the query not just the # of rows affected.

Exception message: 
   An unhandled exception was thrown by the application.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Stack trace:
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at david.web.Controllers.ReportsController.<>c__DisplayClass3_4.<GetUsage>b__3(<>f__AnonymousType3`2 dataFactory)
   at AutoMapper.Internal.EnumerableExtensions.Each[T](IEnumerable`1 items, Action`1 action)
   at david.web.Controllers.ReportsController.<>c__DisplayClass3_3.<GetUsage>b__2(DataType dataType)
   at AutoMapper.Internal.EnumerableExtensions.Each[T](IEnumerable`1 items, Action`1 action)
   at david.web.Controllers.ReportsController.<GetUsage>d__3.MoveNext()

Further technical details

EF Core version: 1.0.0 Operating system: Windows 7 Visual Studio version: 2015 Pro

Other details about my project setup: Azure SQL database

About this issue

  • Original URL
  • State: closed
  • Created 8 years ago
  • Comments: 19 (7 by maintainers)

Most upvoted comments

I know this issue is closed, but I will say that this isn’t at all intuitive behavior. If you had a class DynamicHouse that had a SetDefaultLightSwitchToggle(Toggle.Off) and then you called _dynamicHouse.CreateRoom() which returned an instance of class Room which had a DefaultLightSwitchToggle property, what would you expect that value to be? It just doesn’t make sense for a composing object to have a factory method to create an object that shares the same property as the composing object and for it to not default the value to be that of the composing object.

@taylorchasewhite the way I solved this is to borrow Dapper Query for stored procedures and then have Dictionary of stored procedure names which will return a default or specified timeout to Dapper. It was one of the only places I felt forced to use an library other than EF Core.

database.SetCommandTimeout(60);
var uWu = database.GetCommandTimeout();

uWu is null. 🌝

hit this today, I’d vote to reopen, having to use this seemingly unnecessary code in extension methods that drop down to ado:

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandTimeout = context.Database.GetCommandTimeout() ?? 0;
    ...

…no other method would be appropriate because the caller may have overridden the command timeout via Database.SetCommandTimeout()