如何避免“已经有一个与此连接相关联的开放DataReader必须先关闭”。在MySql/net连接器中？

Question

我有以下代码：

public TimestampedRowStorage GetTimestampedRowStorage(string startTime, string endTime, long trendSettingID, int? period)
    {
        var timestampedList = (from t in dataContext.TrendRecords
                                     where t.TrendSetting_ID == trendSettingID
                                     select t).ToList();

        return new TimestampedRowStorage
        {
            TimestampedDictionary = timestampedList.ToDictionary(m => m.Timestamp,
                m => (from j in dataContext.TrendSignalRecords
                      where j.TrendRecord_ID == m.ID
                      select j).ToDictionary(p => p.TrendSignalSetting.Name,
                p => (double?)p.Value))
        };
    }

但我总是得到以下例外：

已经有一个与此连接相关联的开放DataReader，必须先关闭该连接。

下面是堆栈跟踪：

MySqlException (0x80004005)：已经有一个与此连接相关的开放DataReader，必须先关闭该连接。

MySql.Data.MySqlClient.MySqlCommand.CheckState() +237 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior行为) +146

MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior行为) +47

System.Data.Common.DbCommand.ExecuteReader(CommandBehavior行为) +10

System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand，CommandBehavior behavior) +443

EntityCommandExecutionException:执行命令定义时发生错误。有关详细信息，请参阅内部异常。

System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand，CommandBehavior behavior) +479

System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context，ObjectParameterCollection parameterValues) +736

System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) +149

System.Data.Objects.ObjectQuery1.Execute(MergeOption mergeOption) +31

System.Data.Objects.DataClasses.EntityReference1.负载(MergeOption mergeOption) +148

System.Data.Objects.DataClasses.RelatedEnd.Load() +37 System.Data.Objects.DataClasses.RelatedEnd.DeferredLoad() +8032198 System.Data.Objects.DataClasses.EntityReference1.get_Value() +12 Nebula.Models.TrendSignalRecord.get_TrendSignalSetting() in C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\SmgerDataModel.Designer.cs:2528 Nebula.Models.Trends.TrendRepository.<GetTimestampedRowStorage>b__b(TrendSignalRecord p) in C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\Trends\TrendRepository.cs:229 System.Linq.Enumerable.ToDictionary(IEnumerable1 source，Func2 keySelector, Func2 elementSelector，IEqualityComparer1 comparer) +226

System.Linq.Enumerable.ToDictionary(IEnumerable1源，Func2 keySelector, Func2 elementSelector) +54

Nebula.Models.Trends.TrendRepository.b__a(TrendRecord m) ( C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\Trends\TrendRepository.cs:227 System.Linq.Enumerable.ToDictionary(IEnumerable1 source, Func2 keySelector，Func2 elementSelector, IEqualityComparer1 comparer) +240

System.Linq.Enumerable.ToDictionary(IEnumerable1 source, Func2 keySelector，Func2 elementSelector) +53

( C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\Trends\TrendRepository.cs:224 Nebula.Models.Trends.TrendRepository.GetTrendSettingContainer(String startTime，String endTime，Int64 unitID，Int64 plantID，Int64 trendSettingID，GridSortOptions gridSortOptions，Nullable1 page, Nullable1 recordsPerPage，Nullable1 page, Nullable1 recordsPerPage)中的Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable1 Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable1Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable))中的C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\Trends\TrendRepository.cs:206 Nebula.Controllers.GeneratingUnitController.TrendSettings(Int64 id，in 20#，C中的Int64 trendSettingID，String startTime，String endTime，Nullable1 page, Nullable1 recordsPerPage，GridSortOptions options，Nullable1 period, Int64[] trends, Nullable1 allTrends)：\Int64\Bruno Leonardo\documents\visual studio startTime lambda_method(闭包，ControllerBase，Object[] ) +543

你们能帮帮我吗？

Answer 1

此错误可能是因为您在访问数据库时试图访问数据库。您应该尝试分离这两个Linq表达式。也许可以放点这样的东西：

var TimestampedList = (from t in dataContext.TrendRecords
                                         where t.TrendSetting_ID == trendSettingID
                                         select t).ToList();
TimestampedDictionary = timestampedList.ToDictionary(m => m.Timestamp,
                    m => (from j in dataContext.TrendSignalRecords
                          where j.TrendRecord_ID == m.ID
                          select j).ToDictionary(p => p.TrendSignalSetting.Name,
                    p => (double?)p.Value))

Answer 2

在第一个查询中调用.toList()

Answer 3

我假设MySql连接器不支持MARS (多个活动结果集)。在这种情况下，您不能这样做：

L2SQuery.ToDictionary(m => m.Timestamp, m => AnotherL2SQuery)

完成此操作后，您将枚举第一个L2S查询的结果(= DataReader仍处于打开状态)，并对第一个记录中的每个记录执行第二个L2S查询(=需要第二个DataReader)。

您必须通过调用ToList单独执行第一个查询，并在此之后迭代结果和构建字典。