This SqlTransaction has completed; it is no longer usable #4746

bhasinp created 2 years ago

ABP Framework version: v5.1.3
UI type: Angular
DB provider: EF Core
Tiered (MVC) or Identity Server Separated (Angular): yes
Exception message and stack trace: {"error":{"code":null,"message":"This SqlTransaction has completed; it is no longer usable.","details":"InvalidOperationException: This SqlTransaction has completed; it is no longer usable.\r\nSTACK TRACE: at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck()\r\n at Microsoft.Data.SqlClient.SqlTransaction.Commit()\r\n at System.Data.Common.DbTransaction.CommitAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location ---\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.CommitAsync(CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.CommitAsync(CancellationToken cancellationToken)\r\n at Volo.Abp.Uow.EntityFrameworkCore.EfCoreTransactionApi.CommitAsync()\r\n at Volo.Abp.Uow.UnitOfWork.CommitTransactionsAsync()\r\n at Volo.Abp.Uow.UnitOfWork.CompleteAsync(CancellationToken cancellationToken)\r\n at Volo.Abp.AspNetCore.Uow.AbpUnitOfWorkMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)\r\n at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass6_1.<<UseMiddlewareInterface>b__1>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n at Volo.Abp.AspNetCore.ExceptionHandling.AbpExceptionHandlingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)\r\n","data":null,"validationErrors":null}}
Steps to reproduce the issue:" In API site, when I consume a stored procedure (which is returning data correctly) and after successfully processing the request i'm getting this error in response, however there is no exception at api function.

maliming created 2 years ago

Support Team Fullstack Developer

Can I reproduce the problem locally?

bhasinp created 2 years ago

Hi maliming,

Let me give you some sample code for this.

The method, GetOrgUnitUsers is present under EfCoreUserRepository:EfCoreRepository<motrDbContext, IdentityUser>, IEfCoreUserRepository

So when I call GetOrgUnitUsers from my API endpoint (ApplicationService), I get the response from SP and everything at code level works well, but as soon as I return the response from the API, in browser I get the above error response.

public async Task<List<WorkGroupUser>> GetOrgUnitUsers(string OrgUnitIds, CancellationToken cancellationToken = default)
{
    await EnsureConnectionOpenAsync(cancellationToken);


    using (var command = CreateCommand("GetOrgUnitUsers", CommandType.StoredProcedure, new SqlParameter("OrgUnitIds", OrgUnitIds)))
    {

        using (var dataReader = ((await command).ExecuteReaderAsync(cancellationToken)))
        {
            var result = new List<WorkGroupUser>();
            var reader = (await dataReader);


            while (await reader.ReadAsync(cancellationToken))
            {
                result.Add(new WorkGroupUser
                {
                    OrgUnitId = reader["OrgUnitId"].ToString(),
                    Id = reader["Id"].ToString(),
                    Name = reader["Name"].ToString(),
                    Surname = reader["Surname"].ToString(),
                    DisplayName = reader["DisplayName"].ToString(),
                    Email = reader["Email"].ToString()
                });


            }
            await CloseConnectionAsync();

            return result;
        }
    }

}

Helper Methods

private async Task<DbCommand> CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
{
        var context = await GetDbContextAsync();
        var command = context.Database.GetDbConnection().CreateCommand();

        command.CommandText = commandText;
        command.CommandType = commandType;
        command.Transaction = context.Database.CurrentTransaction?.GetDbTransaction();

        foreach (var parameter in parameters)
        {
            command.Parameters.Add(parameter);
        }

        return command;
    }

    private async Task EnsureConnectionOpenAsync(CancellationToken cancellationToken = default)
    {
        var connection = (await GetDbContextAsync()).Database.GetDbConnection();

        if (connection.State != ConnectionState.Open)
        {
            await connection.OpenAsync(cancellationToken);
        }
    }
    private async Task CloseConnectionAsync()
    {
        var connection = (await GetDbContextAsync()).Database.GetDbConnection();

        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
    }
    ```

maliming created 2 years ago

Support Team Fullstack Developer

hi I think you don't need to call CloseConnectionAsync

We have a sample.

https://github.com/abpframework/abp-samples/blob/master/StoredProcedureDemo/src/StoredProcedureDemo.EntityFrameworkCore/EntityFrameworkCore/Users/AppUserRepository.cs#L16

bhasinp created 2 years ago

Hi If I remove CloseConnectionAsync on next database call after fetching data from SP, I get following error

{"error":{"code":null,"message":"There is already an open DataReader associated with this Connection which must be closed first.","details":"InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.\r\nSTACK TRACE: at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task1 result)\r\n at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()\r\n at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)\r\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n--- End of stack trace from previous location ---\r\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\r\n--- End of stack trace from previous location ---\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)\r\n at Volo.Abp.SettingManagement.EntityFrameworkCore.EfCoreSettingRepository.GetListAsync(String providerName, String providerKey, CancellationToken cancellationToken)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.SetCacheItemsAsync(String providerName, String providerKey, String currentName, SettingCacheItem currentCacheItem)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.GetCacheItemAsync(String name, String providerName, String providerKey)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.GetOrNullAsync(String name, String providerName, String providerKey)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Volo.Abp.SettingManagement.SettingManagementProvider.GetOrNullAsync(SettingDefinition setting, String providerKey)\r\n at Volo.Abp.SettingManagement.SettingManager.GetOrNullInternalAsync(String name, String providerName, String providerKey, Boolean fallback)\r\n at motr.SharedService.CommunicationService.SendCommunication(SendEmailViewModel model) in D:\Teams\Work\Initto\mot-r-V2\aspnet-core\src\motr.Application\SharedService\CommunicationService.cs:line 47\r\n at motr.Messages.MessageExtnAppService.BroadcastMessageAsync(SendBroadcastMessageViewModel input) in D:\Teams\Work\Initto\mot-r-V2\aspnet-core\src\motr.Application\Messages\MessageExtnAppService.cs:line 92\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.GlobalFeatures.GlobalFeatureInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Auditing.AuditingInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Validation.ValidationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at lambda_method4651(Closure , Object )\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.

maliming created 2 years ago

Support Team Fullstack Developer

Can you try the code that I shared?

https://github.com/abpframework/abp-samples/blob/master/StoredProcedureDemo/src/StoredProcedureDemo.EntityFrameworkCore/EntityFrameworkCore/Users/AppUserRepository.cs#L16

bhasinp created 2 years ago

Hi,

I tried above link code and the errors are same, no change in output.

Thanks

maliming created 2 years ago

Support Team Fullstack Developer

Please share a sample project with me, and I will check it. Thanks. liming.ma@volosoft.com

maliming created 2 years ago

Support Team Fullstack Developer

public async Task<List<string>> GetOrgUnitUsers(CancellationToken cancellationToken = default)
{
    await EnsureConnectionOpenAsync(cancellationToken);
    using (var command = await CreateCommand("GetOrgUnitUsers", CommandType.StoredProcedure, new SqlParameter("OrgUnitIds", "1")))
    {
        using (var dataReader = await command.ExecuteReaderAsync(cancellationToken))
        {
            var result = new List<string>();

            while (await dataReader.ReadAsync(cancellationToken))
            {
                result.Add(dataReader["Id"].ToString());

            }
            return result;
        }
    }
}

bhasinp created one year ago

You reviewed this method ?

public async Task<List<string>> GetOrgUnitsAsync() { try { var result = await _userExtAppService.GetOrgUnitsAsync(); var rr= await _efCoreUserRepository.GetOrgUnitUsers(); // I'm getting one error here return result; } catch(Exception exp) { return new List<string>() { "Failed" }; } }