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)
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
Queryfor 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.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:
…no other method would be appropriate because the caller may have overridden the command timeout via
Database.SetCommandTimeout()