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
– Pierrick Martellière, Commented Aug 10, 2018 at 10:42

Stefan · Accepted Answer · 2018-08-10 11:01:02Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related