Using EF Core to invoke stored procedure and closing connection

c# ef-core-2.0 ef-core-2.2 entity-framework-core sql-server

Question

I have an ASP.NET Core 2.2 application using EF Core. I have service class which typically use a DbContext for any CRUD operations. However in one of the method (Assign method below) I need to use stored procedure. So I am using the following code. Note that DbContext is injected as Scoped instance.

public class MyService : IMyService
{
   private readonly MyDbContext _dbContext;

   public MyService(MyDbContext dbContext)
   {
      _dbContext = dbContext;
   }

   public async Task<Employee> GetByID(int id)
   {
      return await _dbContext.Employees.FindById(id);
   }

   public async Task<int?> 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;                
        }
    }
}

The stored procedure is using a SQL transaction internally. I wanted to know if I need to explicitly close the DB connection in the Assign method or the connection will be automatically get closed on request end by the container?

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 Employee table:

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.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult) > at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---

at xxxxx.Services.MyService.Assign(Int32 itemID, Int32 userID) in D:\xxxx\Services\MyService.cs:line 84

1
1
7/31/2019 9:04:23 AM

Popular Answer

I wanted to know if I need to explicitly close the DB connection in the Assign method or the connection will be automatically get closed on request end by the container?

It doesn't matter if the connection will be closed automatically at some later point or not. Following the good programming practices, one should release the allocated resources (Create -> Dispose, Open -> Close etc.).

EF Core internally is doing that for each operation which needs open connection, so you'd better do the same, e.g. something like this

await _dbContext.Database.OpenConnectionAsync();
try
{
    var result = await cmd.ExecuteScalarAsync();

    if (result != null)
    {
        return Convert.ToInt32(result);
    }

    return null;
}
finally
{
    _dbContext.Database.CloseConnection();
}
1
9/18/2019 3:29:54 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow