CODEWITHSUNDEEP
c#mysqllinq
Vadim Muratov
Vadim Muratov

Reputation: 215

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

The exception occurs when a nested SELECT. If you comment out properties Photo, Birthday and Residence, then everything works. How do I rewrite the query to make it work?

  var predicate = PredicateBuilder.True<Persons>();
    var query = this._entity.Persons.AsExpandable();

                    #region 
                    if (!String.IsNullOrEmpty(currentPerson.PersonName))
                    {
                        predicate = predicate.And(i => i.PersonName.Contains(currentPerson.PersonName.Trim()));
                    }

                    if (!String.IsNullOrEmpty(currentPerson.PersonLastName))
                    {
                        predicate = predicate.And(i => i.PersonLastName.Contains(currentPerson.PersonLastName.Trim()));
                    }

                    if (!String.IsNullOrEmpty(currentPerson.PersonPatronymic))
                    {
                        predicate = predicate.And(i => i.PersonPatronymic.Contains(currentPerson.PersonPatronymic.Trim()));
                    }
    ...........

    var result = query.Where(predicate).AsEnumerable().Select(o => new POCO.PersonResult
  {
    Id        = (int)o.Id,
    Photo     = o.persons_photos.Select(s => s.PersonFrontView).FirstOrDefault(),
    FullName  = String.Format("{0} {1} {2}", o.PersonLastName, o.PersonName, o.PersonPatronymic),
    Birthday  = o.persons_passport_data.Select(s => s.PersonBirthday).FirstOrDefault().ToString()
    Residence = o.persons_registration
                                    .Select(s =>
                                            String.Join(", ", ListModel.GetCountry(s.PersonCountryId),
                                                              ListModel.GetRegion(s.PersonRegionId),
                                                              ListModel.GetCity(s.PersonCityId))).FirstOrDefault()
                    }).ToList();

Upvotes: 0

Views: 371

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

Looks like MySql connector does not support MARS (Multiple active result sets) and your LINQ to Objects query (after AsEnumerable() call) is involving lazy loading (o.persons_photos, o.persons_passport_data and o.persons_registration navigation properties) which require additional readers while the main data reader is still executing.

The best would be to let the whole query execute as single SQL query by removing the AsEnumerable() call, but since the projection is using unsupported methods, I guess the only option is to replace the AsEnumerable() call with ToList(), which will ensure the main data reader is complete at the time navigation properties are lazy loaded.

Another thing you can try is to eager load them (assuming you are using EF) by adding a couple Include calls:

var query = this._entity.Persons
    .Include(o => o.persons_photos)
    .Include(o => o.persons_passport_data)
    .Include(o => o.persons_registration)
    .AsExpandable();

// the rest (same as yours) ...

Upvotes: 2

Related Questions