MARS - List of a list stopping data reader from closing?

Question

I'm playing around with an ASP Core Identity project.

For the admin section, to add users to roles I want a list of current members of that role and a list of non-members. Then I can move users between them using checkboxes or arrow buttons.

My code gets the error:

**InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.**

It works when I enable MARS in the database connection string (I'm using entity):

MultipleActiveResultSets=True

My code:

    private RoleManager<IdentityRole> roleManager;
    private UserManager<User> userManager;

    public RoleController(RoleManager<IdentityRole> roleMgr, UserManager<User> userMrg)
    {
        // Constructor
        // Role Manager, UserManager Dependancy Injection
        roleManager = roleMgr;
        userManager = userMrg;
    }

    public async Task<IActionResult> Update(string id)
    {
        IdentityRole role = await roleManager.FindByIdAsync(id);
        List<User> members = new List<User>();
        List<User> nonMembers = new List<User>();
        foreach (User user in userManager.Users)
        {
            // We need MARS enabled in connection string
            // MultipleActiveResultSets=True
            // We are creating a list of a list and not closing the data reader?
            var list = await userManager.IsInRoleAsync(user, role.Name) ? members : nonMembers;
            list.Add(user);
        }
        return View(new RoleEdit
        {
            Role = role,
            Members = members,
            NonMembers = nonMembers
        });
    }

So you would send an id to the Update() module

mysite.com/controller/update/1

and see a list like this:

How can I rework this so I don't have to use MARS ? I'm told it is a workaround and not a solution to use it.

Any help is appreciated.

Can you explain what your controller is doing? It looks like your code is getting a response from a webpage and then storing results in a database. I'm not sure why you have the line "userManager = userMrg" in the RoleController. The entity object is usually initialized when the project starts. Here you are creating the entity database when you get the response from server which may occur multiple times. — jdweng, May 21 '21 at 07:19
Try to change the `foreach` statement as below: `foreach (var user in userManager.Users.ToList()) { var list = new List(); if (await userManager.IsInRoleAsync(user, role.Name)) { list = members; } else { list = nonMembers; } list.Add(user); }` Or `var userList = await userManager.Users.ToListAsync(); foreach (var user in userList) { var list = new List(); if (await userManager.IsInRoleAsync(user, role.Name)) { list = members; } else { list = nonMembers; } list.Add(user); }`. — Zhi Lv, May 21 '21 at 09:06
Please show the code which actually does the database access. I strongly suspect it is not disposing the connection, please see [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) — Charlieface, May 21 '21 at 12:13

score 0 · Accepted Answer · answered May 24 '21 at 02:12

Thanks Zhi Lv, you're idea of adding the .ToList() in the foreach parameters worked. I kept most of my other code the same.

public async Task<IActionResult> Update(string id)
    {
        IdentityRole role = await roleManager.FindByIdAsync(id);
        List<User> members = new List<User>();
        List<User> nonMembers = new List<User>();
        // Add .ToList() here
        foreach (User user in userManager.Users.ToList())
           {
            var list = await userManager.IsInRoleAsync(user, role.Name) ? members : nonMembers;
            list.Add(user);
        }
        return View(new RoleEdit
        {
            Role = role,
            Members = members,
            NonMembers = nonMembers
        });
    }

MARS - List of a list stopping data reader from closing?

1 Answers1