What leads to mySQL command concurrency issue with DataReader in ASP.Net Core, and how do I resolve it?

Question

I just migrated my application from ASP.Net Core 2.2 to 3.1, and I have a bug I don't understand. It's a fairly straightforward MVC app with a mySQL DB, and I'm using EF Core - Identity to manage what they call "policy-based" authorization, aka 'claims-based' auth.

The main Class/Model is a Family Profile. A given User can have 2 levels of claims to different Families - they can be either a Parent or a Caregiver. The User dashboard View shows a few lists: A list of children in any Parent-Family, a list of Caregivers authorized for Parent-Families, and a list of Caregiver-Families.

This is the Controller code that sends all that info to the view. The problem is that the foreach loop is generating an error in the browser:

"InvalidOperationException: Cannot set MySqlCommand.CommandText when there is an open DataReader for this command; it must be closed first."

It only happens when the User has a Caregiver relationship with a Family, since the conditional prevents you from entering the foreach, but part of my confusion is why this is an issue inside this loop when it seems like I am making very similar requests to the DB in other places.

I looked at this question and this one, and it seems like maybe the 2nd one is more relevant, so I tried adding the await piece and making the query method Async inside the foreach loop (it had been just 1 line in there), but it didn't fix the problem. Maybe I put it in the wrong place? The first question helped me understand what's going on under the hood, but I don't know where in my code I would actually implement any of it.

Thanks in advance for any help!

   public async Task<ActionResult> Index()
{
  var userId = this.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
  var currentUser = await _userManager.FindByIdAsync(userId);
  var userFamilies = (_db.Families.Where(entry => (entry.ParentId == currentUser.Id))).ToList();

  if (_db.Caregivers.Select(entry => (entry.CaregiverId == currentUser.Id)) != null)
  {

    var userCareFamilies = new List<Family>();
    var careFamilies = _db.CaregiverFamilies
    .Where(c => c.CaregiverId == currentUser.Id);
    if (careFamilies.Count() != 0)
    {
      foreach (CaregiverFamily cf in careFamilies)
      {
        var thisFamily = await _db.Families.FirstOrDefaultAsync(f => f.FamilyId == cf.FamilyId);
        userCareFamilies.Add(thisFamily);
      }
    }
    ViewBag.CareFamilies = userCareFamilies;
  }

Answer 1

The problem is with this line:

var careFamilies = _db.CaregiverFamilies
.Where(c => c.CaregiverId == currentUser.Id);

Because you are not materializing the query (via ToList etc), it keeps the connection open while you're iterating through the results.

Try using

var careFamilies = _db.CaregiverFamilies
.Where(c => c.CaregiverId == currentUser.Id).ToList();

On a side note, you're mixing Async (FirstOrDefaultAsync) and Sync (ToList) methods. I'd suggest sticking to one or the other.