How to avoid “There is already an open DataReader

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?

回答1:

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

回答2:

I assume MySql connector doesn't support MARS (Multiple active result sets). In such case you cannot do this:

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

Once you do this you are enumerating result of the first L2S query (= DataReader is still open) and you are executing second L2S query for each record from the first one (= you need second DataReader).

You must execute first query separately by calling ToList and after that iterate result and build dictionary.

回答3:

call .toList() in the first query

回答4:

I ran into this error today too. I was using a connection for more than one thing. Like this:

  MySqlConnection conn = new MySqlConnection(....);
  conn.Open();
  // Created a database here
  // Populdate the database there
  conn.Close();

But when I opened and closed the connection for each action, it worked:

  MySqlConnection conn = new MySqlConnection(....);
  conn.Open();
  // Created a database here
  conn.Close();
  conn.Open();
  // Populdate the database there
  conn.Close();