MySql.Data.MySqlClient.MySqlException - There is already an open DataReader associated with this Connection which must be closed first

Question

I'm new to Entity Framework and asp.net-mvc 4, I'm getting a Mysql.Data Exception and I couldn't find any related thread on google.

Here's the error:

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

Here's my controller's method:

// GET: api/Partenaires_prestations
[Authorize]
[Route("api/Partenaires_prestations")]
public List<PartenaireMapItem> GetPartenairesWithPrestations()
{
     Random rnd = new Random();

     var queryString = Request.GetQueryNameValuePairs();

     var prestation = queryString.FirstOrDefault();

     return db.Partenaires
         .Where(p => p.PartenairePrestations.Any(pp => pp.Prestation.NomPrestation == prestation.Value))
         .AsEnumerable()
         .Select(p => new PartenaireMapItem {
             IdPartenaire = p.IdPartenaire,
             FirstName = p.FirstName,
             LastName = p.LastName,
             NomComplet = p.LastName.Substring(0,1).ToUpper() + ". " + p.FirstName,
             Type = p.Type,
             DureeMin = 50,
             Lat = p.Lat,
             Lng = p.Lng,
             ImageUrl = p.ImageUrl,
             SeDeplace = p.SeDeplace,
             ADomicile = p.ADomicile,

             Notes = p.NoteClientPartenaires,
             Prestations = new List<string>(p.PartenairePrestations.Select(y => y.Prestation.NomPrestation))
         }).ToList();
}

Thanks to anyone who will take the time to read/answer my post.

@Stefan ```private UphairDbEntities db = new UphairDbEntities();``` — Pierrick Martellière, Aug 10 '18 at 10:42
@PierrickMartellière: are you calling `Dispose()` on it anywhere? — Stefan, Aug 10 '18 at 10:45
I tried to add it already on my connection string but got an error: "The underlying provider failed on ConnectionString. - Keyword not supported. ↵Parameter name: multipleactiveresultsets" I'm adding my connection String to my orginal post. — Pierrick Martellière, Aug 10 '18 at 10:49
@Stefan yes it is called in ```protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); }``` — Pierrick Martellière, Aug 10 '18 at 10:52
MySQL may not support MARS. It certainly has a history of not supporting it, not sure about new versions. If you remove the assignments to `Notes` and `Prestations` does it work? — Crowcoder, Aug 10 '18 at 10:53
If your context were disposed you would not get the error you are getting. — Crowcoder, Aug 10 '18 at 10:54
I did get an Out of Memory error, now the method is not returning anymore... I'm quite lost — Pierrick Martellière, Aug 10 '18 at 11:21

Stefan · Answer 1 · 2018-08-10T11:01:02.300

The issue is that you are running multiple queries, on the same connection, simultaneously.

The error is a result of that. You can choose to allow it, or fix it.

Lets fix it (disclaimer; it's a bit of a guess here):

In your code, you have 2 queries actively running on your connection. You can easely fix it by changing the AsEnumerable to ToList

db.Partenaires
         .Where(p => p.PartenairePrestations.Any(pp => pp.Prestation.NomPrestation == prestation.Value))
         //note: the change is here
         .ToList()
         .Select(p => new PartenaireMapItem {
             IdPartenaire = p.IdPartenaire,
             FirstName = p.FirstName,
             LastName = p.LastName,
             NomComplet = p.LastName.Substring(0,1).ToUpper() + ". " + p.FirstName,
             Type = p.Type,
             DureeMin = 50,
             Lat = p.Lat,
             Lng = p.Lng,
             ImageUrl = p.ImageUrl,
             SeDeplace = p.SeDeplace,
             ADomicile = p.ADomicile,

             Notes = p.NoteClientPartenaires,
             Prestations = new List<string>(p.PartenairePrestations.Select(y => y.Prestation.NomPrestation))
         }).ToList();

Be advised, the ToList will force the query to run against the DB directly. The AsEnumerable will deffer execution until it's being enumerated. So the ToList will prevent the "open data reader error" at this specific location.

While enumerating, my guess is, that your code triggers another query, which results in the given error.

As an alternative you could drop the AsEnumerable altogether so a single query will be build. But note, that in that scenario, all functions used in the query must be translated to SQL.

Here's a related one; about a datareader, which is basically the same:

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

MySql.Data.MySqlClient.MySqlException - There is already an open DataReader associated with this Connection which must be closed first

1 Answers1