Helping solving 'There is already an open DataReader associated with this Command which must be closed first' in ASP.NET Core 3.1 Application

user_2310466 0
Viewed 1610

I need help fixing the error message:

There is already an open DataReader associated with this Command which must be closed first.

The problem is stemming from the line:

foreach (var user in _userManager.Users) {

I understand that basically, there is another reader open and the other hasn't been closed but I don't know how to fix this based on my code. I searched around for similar problems and there are lots of them, some suggest adding no tracking, others to return .ToList. I'm a bit lost with what to do about it and could use some help.

Here is my code so far:

Controller

Both the role manager and the UserManager are injected, in this instance, I've extended the identity class using my own ApplicationUser. It was my understanding that, upon injection, declaring items with _ would apply no tracking, that might be wrong.

  private readonly RoleManager<IdentityRole> _roleManager;
  private readonly UserManager<ApplicationUser> _userManager;

  public AdministrationController(RoleManager<IdentityRole> roleManager,
         UserManager<ApplicationUser> userManager) 
  {
      _roleManager = roleManager;
      _userManager = userManager;
  }

[HttpGet]
public async Task<IActionResult> EditRole(string id)
{
    var role = await _roleManager.FindByIdAsync(id);

    if (role == null) {
        ViewBag.ErrorMessage = $"Role with Id = {id} cannot be found";
        return View("NotFound");
    }
    var model = new EditRoleViewModel
    {
        Id = role.Id,
        RoleName = role.Name
    };
    foreach (var user in _userManager.Users) {
        if (await _userManager.IsInRoleAsync(user, role.Name))
        {
            model.Users.Add(user.UserName);
        }
     }
     return View(model);
 }

Model

To avoid errors with the @if (Model.Users.Any()) of the view, the Users collection is instantiated in the ViewModel.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication1
{
    public class EditRoleViewModel
    {
        public EditRoleViewModel() {
            Users = new List<string>();
        }
        public string Id { get; set; }
        [Required(ErrorMessage = "Role name is required")]
        public string RoleName { get; set; }
        public List<string> Users { get; set; }
    }
}

View

@model EditRoleViewModel
@{ 
    ViewBag.Title = "Edit Role";
}
<h1>Edit Role</h1>

<form method="post" class="mt-3">
    <div class="form-group row">
        <label asp-for="Id" class="col-sm-2 col-form-label"></label>
        <div class="col-sm-10">
            <input asp-for="Id" disabled class="form-control" />
        </div>
    </div>
    <div class="form-group row">
        <label asp-for="RoleName" class="col-sm-2 col-form-label"></label>
        <div class="col-sm-10">
            <input asp-for="RoleName" class="form-control" />
            <span asp-validation-for="RoleName" class="text-danger"></span>
        </div>
    </div>

    <div asp-validation-summary="All" class="text-danger"></div>

    <div class="form-group row">
        <div class="col-sm-10">
            <button type="submit" class="btn btn-parimary">Update</button>
            <a asp-action="ListRoles" class="btn btn-danger">Cancel</a>
        </div>
    </div> 

    <hr />
    <div class="card">
        <div class="card-header">
            <h3>Users in this role</h3>
        </div>
        <div class="card-body">
            @if (Model.Users.Any())
            {
                foreach (var user in Model.Users)
                {
                    <h5 class="card-title">@user</h5>
                }
            }
            else { 
                <h5 class="card-title">None at the moment</h5>
            }
        </div>
        <div class="card-footer">
            <a href="#" class="btn btn-primary" style="width: auto">Add Users</a>
            <a href="#" class="btn btn-primary" style="width: auto">Remove Users</a>
        </div>
    </div>    
</form>

Any help with this is appreciated.

entity-framework-core asp.net-core asp.net c#
1 Answers

It seems you're trying to use the same _userManager object from within a loop that is also using that object to iterate over your Users. I believe the UserManager has an open connection, which uses a DataReader, to query for a list of your users.

Either modify your foreach loop to iterate over a completed list of users, or assign the list of users to a local variable and then iterate over that. Either way, you need to call _userManager.Users.ToList() before iterating over that result set. Then you should be able to access the _userManager from within the loop.

I believe there is a way to enable multiple active result sets but in your case, I don't see the need for that. Also, no tracking only applies to EF Core when using the DbContext to make queries. No tracking instructs the DbContext to not track the entity within it's change tracker, which can cause issues when it comes to updating an entity.

foreach (var user in _userManager.Users.ToList()) 
{
    if (await _userManager.IsInRoleAsync(user, role.Name))
    {
        model.Users.Add(user.UserName);
    }
}

Or you can use the async method and assign it to a variable.

var userList = await _userManager.Users.ToListAsync();

foreach (var user in userList) 
{
    if (await _userManager.IsInRoleAsync(user, role.Name))
    {
        model.Users.Add(user.UserName);
    }
}

I believe this refers to deferred execution in LINQ queries. You can read up on it in the docs if you want to get a better understanding.

user_80680520
Related