How to handle Dapper's timeout issue while using a long running StoredProc on Asp.Net Core

Question

I am working on Asp.Net Core 6. I am using Dapper for my data access. While it works fine on short-running storedprocs, one of the storedproc takes an extended time to complete and I get the timeout error. When I ran the storeproc on the SSMS directly, it took 11 minutes to complete. The following is my code using the dapper and storedproc. I am getting a timeout error. I tried to use commandTimeout:null, but still it times out. When I tried commandTimeout:900, the query completed right away without returning any result set. I also tried the QueryAsync method. The following is the timeout error:

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]

  An unhandled exception has occurred while executing the request.

  Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

   ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.

     at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

     at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

     at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

     at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

     at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()

     at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

     at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

     at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)

     at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

     at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

     at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1133

     at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1161

     at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

     at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

     at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 801

     at Project.DataAccess.Dapper.Reports.Reports.GetRiskByYearAsync(ReportsConfigurationVM reportsConfigurationVM) in D:\Projects\Main\ProjectUpdate\ProjectNew\Project.DataAccess\Dapper\Reports\Reports.cs:line 68

     at ProjectNew.Controllers.ProjectReportsController.ProcessReport(ReportsConfigurationVM reportsConfigurationVM) in D:\Projects\Main\ProjectUpdate\ProjectNew\ProjectNew\Controllers\ProjectReportsController.cs:line 82

     at lambda_method101(Closure , Object )

     at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()

  --- End of stack trace from previous location ---

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()

  --- End of stack trace from previous location ---

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

     at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)

     at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)

     at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)

     at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)

     at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)

     at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

  ClientConnectionId:eaa90f24-e94d-4648-a41c-94463b0cf149

  Error Number:-2,State:0,Class:11
``````aspx-csharp
public IEnumerable<ProjectRiskByYear> GetProjectDetailsByYearAsync(ReportsConfigurationVM reportsConfigurationVM)
{
    var parameters = new DynamicParameters();
    parameters.Add("@ProjectStatusID", reportsConfigurationVM.ProjectStatusID, DbType.String);
    parameters.Add("@ProjectNumber", reportsConfigurationVM.ProjectNumber, DbType.String);
    parameters.Add("@ProjectName", reportsConfigurationVM.ProjectName, DbType.String);
using (var connection = new SqlConnection(_connectionString))
{         
	   var projectDetails = connection.Query<ProjectRiskByYear>("proc_GetProjectDetailsByYear",   		  		parameters, commandType: CommandType.StoredProcedure, commandTimeout:null);         
	return (IEnumerable<ProjectRiskByYear>)projectDetails;     
}    
}