locked
Async/Await and Error 500 with Exception Invalid attempt to call Read when reader is closed RRS feed

  • Question

  • User-711501689 posted

    In my learning project:

    //From Startup.cs
    
    public void ConfigureServices(IServiceCollection services)
    {
       services.Configure<CookiePolicyOptions>(options =>
       {
          // This lambda determines whether user consent for non-essential cookies is needed for a given request.
          options.CheckConsentNeeded = context => true;
          options.MinimumSameSitePolicy = SameSiteMode.None;
       });
    
       var conn = new SqlConnection();
       conn.ConnectionString = @"Server=MyServer; Database=MyDb;Trusted_Connection=True;";
    
    services.AddDbContext<UserDataDbContext>(options => options.UseSqlServer(conn)); services.AddAuthentication(IISDefaults.AuthenticationScheme); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2); services.AddSingleton(provider => Configuration); }

    I have this action in a controller which is called from jQuery (via $.get(url) method)

    [Route("GetBookmarks")]
    public List<UserData> GetBookmarks(string user)
    {
       var data = _context.UserDataItems.FromSql("SELECT * FROM dbo.UserDataItems WHERE UserName = {0} AND Bookmark = 1", user).ToList();
    
       if (data.Count > 0)
       {
          return data;
       }
       else
       {
          return null;
       }
    }
    
    

    It works fine during testing when I upload this to a server, it randomly gives me Error 500; especially when I click on links in rapid succession which repeatedly calls the above action. The log shows two types of errors:

    System.InvalidOperationException: Invalid attempt to call Read when reader is closed.
    
    System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
    

    So I made a minor change and it seems that the error has now disappeared:

    [Route("GetBookmarks")]
    public List<UserData> GetBookmarks(user)
    {
       var data = GetBookmarksAsync(user);
       var data1 = data.Result;
    
       if (data1.Count > 0)
       {
          return data1;
       }
       else
       {
          return null;
       }
    }
    
    public async Task<List<UserData>> GetBookmarksAsync(string user)
    {
       var data = await _context.UserDataItems.FromSql("SELECT * FROM dbo.UserDataItems WHERE UserName = {0} AND Bookmark = 1", user).ToListAsync();
       return data;
    }

    If my observation is correct, I still don't understand why this would happen...I believe there is something wrong with my code but I don't know what it is.

    Monday, May 20, 2019 11:48 AM

Answers

  • User753101303 posted

    Ah. What if you pass a connection string rather than a connection to UseSqlServer? Currently you likely end up with all your DbContext instances sharing the same Sql connection causing problems as soon as you start to have multiple http requests at the same time.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 20, 2019 3:50 PM

All replies

  • User753101303 posted

    Hi,

    And you had no async calls at all previously ? The call stack could help to better understand where the problem happened. _context is injected correctly and declared at the instance level ?

    For the current code rather than using data.Result I would just go fully async ie :

    public async Task<List<UserData>> GetBookmarks(user)
    {
       var data = await GetBookmarksAsync(user);

    Monday, May 20, 2019 11:59 AM
  • User-711501689 posted

    I will try what you have suggested...but in that case how should I deal with the Task<> type return value in jQuery?

    Thanks

    Monday, May 20, 2019 12:20 PM
  • User753101303 posted

    You won't have anything to change on the client side ie jQuery will still get the response as an array of UserData objects. It just alllows to use *server side* async code all the way down but it doesn't change anything for the client side.

    Having a client side async http request and using server side side async code to process the incoming http request is just unrelated ie you could have all 4 combinations if you really want what being done on one side having no effect at all on which options is used on the other side.

    Monday, May 20, 2019 1:02 PM
  • User-711501689 posted

    Thanks. I did just that...the earlier errors have gone but there is still one error...

    System.InvalidOperationException: Invalid operation. The connection is closed.
       at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
       at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
    --- End of stack trace from previous location where exception was thrown ---
       at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
    --- End of stack trace from previous location where exception was thrown ---
       at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
       at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106
       at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98
       at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
       at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 120
       at MyFirst.Controllers.UserManagerController.GetBookmarksAsync(String userX) in E:\Practice\ASP\MyFirst\MyFirst\Controllers\UserManagerController.cs:line 178
       at MyFirst.Controllers.UserManagerController.GetBookmarks1Async(String userX) in E:\Practice\ASP\MyFirst\MyFirst\Controllers\UserManagerController.cs:line 163
       at lambda_method(Closure , Object )
       at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
       at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
       at System.Threading.Tasks.ValueTask`1.get_Result()
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
       at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
       at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
       at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
       at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
       at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
       at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
       at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)
       at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
       at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
    

    This is my DbContext Class:

    public class UserDataDbContext : DbContext
    {
        public DbSet<UserData> UserDataItems { get; set; }
    
        public UserDataDbContext(DbContextOptions<UserDataDbContext> options) : base(options)
        {
                
        }
            
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }
    }

    Monday, May 20, 2019 2:53 PM
  • User753101303 posted

    Shows us how you handle _context. It is not static ???

    Monday, May 20, 2019 3:09 PM
  • User-711501689 posted

    In my Startup.cs:

       var conn = new SqlConnection();
       conn.ConnectionString = @"Server=MyServer; Database=MyDb;Trusted_Connection=True;";
    
       services.AddDbContext<UserDataDbContext>(options => options.UseSqlServer(conn));

    In my Controller, I have used the _context as under:

            private readonly IConfiguration _configuration;
            private readonly UserDataDbContext _context;
    
            public UserManagerController(IConfiguration config, UserDataDbContext context)
            {
                _configuration = config;
                _context = context;
            }

    So my _context is not static but it is declared in the controller where I am using it.

    Monday, May 20, 2019 3:26 PM
  • User753101303 posted

    Ah. What if you pass a connection string rather than a connection to UseSqlServer? Currently you likely end up with all your DbContext instances sharing the same Sql connection causing problems as soon as you start to have multiple http requests at the same time.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 20, 2019 3:50 PM
  • User-711501689 posted

    You mean something like this?

    services.AddDbContext<UserDataDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("TestServer")));
    
    //"TestServer" in appsettings.json is:
    {
        "ConnectionStrings": {
          "TestServer": "Server=MyServer; Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=True",
        
    }

    Tuesday, May 21, 2019 4:41 AM
  • User585649674 posted

    should the _dbcontext be singleton or perrequest.  Singleton means there is only one context through out the app cycle from start to closing of the application. I know transient wont work as transient will create one db context for each class. you should definitely use per request. So that for each request a separate dbcontext object is created. The reader used to read data records in one request should not affect another request. It will also help in maintaining transaction. So that if there are 10 insert statement from 10 different repository. class. it will give a single commit at the end of your business method.

    Tuesday, May 21, 2019 5:40 AM
  • User-711501689 posted

    Thanks a lot...this just worked!!!

    Edit: I am trying to mark this https://forums.asp.net/post/6262623.aspx as answer but it seems the "Mark As Answer" link is not responding.

    Tuesday, May 21, 2019 5:56 AM
  • User753101303 posted

    Yes else rather than having each DbContext creating its own SQL connection, they all share the connection you provided (and so as a soon as you start to have multiple http requests "at the same time" you start to have issues about this connection).

    From other posts it seems there is currently some issues with the site which is likely why can't currently use "Mark as answer".

    Tuesday, May 21, 2019 12:18 PM