title: Using EF Core to invoke stored procedure and closing connection
body: <p>I have an ASP.NET Core 2.2 application using EF Core. I have service class which typically use a <code>DbContext</code> for any CRUD operations. However in one of the method (<code>Assign</code> method below) I need to use stored procedure. So I am using the following code. Note that <code>DbContext</code> is injected as <code>Scoped</code> instance.</p>
  
  <pre><code>public class MyService : IMyService
  {
     private readonly MyDbContext _dbContext;
  
     public MyService(MyDbContext dbContext)
     {
        _dbContext = dbContext;
     }
  
     public async Task&lt;Employee&gt; GetByID(int id)
     {
        return await _dbContext.Employees.FindById(id);
     }
  
     public async Task&lt;int?&gt; Assign(int itemID, int userID)
     {
          using (var cmd = _dbContext.Database.GetDbConnection().CreateCommand())
          {
              var p1 = new SqlParameter("@ItemId", SqlDbType.Int);
              p1.Value = itemID;
  
              var p2 = new SqlParameter("@UserID", SqlDbType.Int);
              p2.Value = userID;
  
              cmd.CommandText = "dbo.prcAssign";
              cmd.CommandType = CommandType.StoredProcedure;
  
              cmd.Parameters.Add(p1);
              cmd.Parameters.Add(p2);
  
              await _dbContext.Database.OpenConnectionAsync();
  
              var result = await cmd.ExecuteScalarAsync();
  
              if (result != null)
              {
                  return Convert.ToInt32(result);
              }
  
              return null;                
          }
      }
  }
  </code></pre>
  
  <p>The stored procedure is using a SQL transaction internally. I wanted to know if I need to explicitly close the DB connection in the <code>Assign</code> method or the connection will be automatically get closed on request end by the container?</p>
  
  <p>Issue is since the stored procedure is using a transaction, it's putting read lock on the table (we actually want the read lock on the table to avoid getting dirty data). The stored procedure takes a few milliseconds to execute and we hardly have 50 users. However very often we get deadlock on <code>Employee</code> table:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<br>
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action<code>1 wrapCloseInAction)<br>
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action</code>1 wrapCloseInAction)<br>
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)<br>
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady)<br>
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()<br>
    at System.Data.SqlClient.SqlDataReader.get_MetaData()<br>
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)<br>
    at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()<br>
    at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)<br>
    at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)<br>
    at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult)      > at System.Threading.Tasks.TaskFactory<code>1.FromAsyncCoreLogic(IAsyncResult iar, Func</code>2 endFunction, Action<code>1 endAction, Task</code>1 promise, Boolean requiresSynchronization)<br>
    --- End of stack trace from previous location where exception was thrown ---     </p>
    
    <p>at xxxxx.Services.MyService.Assign(Int32 itemID, Int32 userID) in D:\xxxx\Services\MyService.cs:line 84     </p>
  </blockquote>