How to avoid "There is already an open DataReader associated with this Connection which must be closed first." in MySql/net connector?

Question

I have this following piece of code:

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

But I always get the following exception:

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

Here is the stack trace:

[MySqlException (0x80004005): There is already an open DataReader associated with this Connection which must be closed first.]
MySql.Data.MySqlClient.MySqlCommand.CheckState() +237 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +146
MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +47
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
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.Load(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 source, Func2 keySelector, Func2 elementSelector) +54
Nebula.Models.Trends.TrendRepository.b__a(TrendRecord m) in 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
Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable1 period) in 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 period, Int64[] trends, Nullable1 allTrends) in C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Models\Trends\TrendRepository.cs:206 Nebula.Controllers.GeneratingUnitController.TrendSettings(Int64 id, Int64 plantID, Int64 trendSettingID, String startTime, String endTime, Nullable1 page, Nullable1 recordsPerPage, GridSortOptions options, Nullable1 period, Int64[] trends, Nullable1 allTrends) in C:\Users\Bruno Leonardo\documents\visual studio 2010\Projects\Nebula\Nebula\Controllers\GeneratingUnitController.cs:148 lambda_method(Closure , ControllerBase , Object[] ) +543

Can you guys help me out?

Answer 1

call .toList() in the first query

Answer 2

The error is probably because you are trying to access the database while you are accessing the database. You should try to separate the two Linq expressions. Maybe put something like this:

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