Mysql Linq已经有一个与此连接相关的打开的DataReader，必须首先关闭 [英] Mysql Linq There is already an open DataReader associated with this Connection which must be closed first

问题描述

当嵌套的SELECT发生异常.如果您注释掉照片"，生日"和住所"属性，则一切正常.如何重写查询以使其正常工作?

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();

推荐答案

类似MySql连接器不支持MARS(多个活动结果集)，并且您的LINQ to Objects查询(在AsEnumerable()调用之后)涉及延迟加载(o.persons_photos，o.persons_passport_data和o.persons_registration导航属性)，它们在主数据读取器仍在执行时需要其他读取器.

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.

最好是通过删除AsEnumerable()调用使整个查询作为单个SQL查询执行，但是由于投影使用的是不受支持的方法，我想唯一的选择是将AsEnumerable()调用替换为，这将确保在延迟加载导航属性时主数据读取器是完整的.

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.

您可以尝试的另一件事是通过添加几个Include调用来渴望加载它们(假设您正在使用EF):

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) ...

这篇关于Mysql Linq已经有一个与此连接相关的打开的DataReader，必须首先关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！