Avoiding "There is already an open DataReader associated with this connection that should be closed first." in the MySql / net connector?

Question

I have the following code snippet:

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 should be closed first.

Here is the stack trace:

[MySqlException (0x80004005): there is already an open DataReader associated with this connection, which should 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: Error executing command definition. See Internal 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.ObjectQuery 1.GetResults(Nullable 1 forMergeOption) +149
System.Data.Objects.ObjectQuery 1.Execute(MergeOption mergeOption) +31
System.Data.Objects.DataClasses.EntityReference 1.Execute(MergeOption mergeOption) +31
System.Data.Objects.DataClasses.EntityReference 1.Execute(MergeOption mergeOption) +31
System.Data.Objects.DataClasses.EntityReference 1.Load (MergeOption mergeOption) +148
System.Data.Objects.DataClasses.RelatedEnd.Load () +37. System.Data.Objects.DataClasses.RelatedEnd.DeferredLoad () +8032198 System.Data.Objects.DataClasses.EntityReference 1.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(IEnumerable 1 source, Func 2 keySelector, Func 2 elementSelector, IEqualityComparer 1 comparer) +226
System.Linq.Enumerable.ToDictionary(IEnumerable 1 comparer) +226
System.Linq.Enumerable.ToDictionary(IEnumerable 1 comparer) +226
System.Linq.Enumerable.ToDictionary(IEnumerable 1 source, Func 2 keySelector, Func 2 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 (IEnumerable 1 source, Func 2 keySelector, Func 2 elementSelector, IEqualityComparer 1 comparative) +240
System.Linq.Enumerable.ToDictionary (IEnumerable 1 source, Func 2 keySelector, Func 2 elementSelector) +53
Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable 2 elementSelector) +53
Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable 2 elementSelector) +53
Nebula.Models.Trends.TrendRepository.GetTimestampedRowStorage(String startTime, String endTime, Int64 trendSettingID, Nullable 1 period) in C: \ Users \ Bruno Leonardo \ documents \ visual studio 2010 \ Projects \ Nebula \ Nebula \ Models \ Trends \ TrendReposit. cs: 224 Nebula.Models.Trends.TrendRepository.GetTrendSettingContainer (String startTime, String endTime, Int64 unitID, Int64 plantID, Int64 trendSettingID, GridSortOptions gridSortOptions, Nullable 1 page, Nullable 1 recordsPerPage, Nullable 1 period, Int64[] trends, Nullable recordsPerPage, Nullable 1 period, Int64[] trends, Nullable recordsPerPages, Nullable 1 period, Int64[] trends, Nullable recordsPerPages, Nullable 1 period, Int64[] trends, Nullable recordsPerPages, Nullable 1 period, Int64[] trends, Nullable recordsPerPage, 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, Nullable 1 page, Nullable 1 recordsPerPage, Parameters GridSortOptions, Nullable 1 period, Int64[] trends, Nullable 1 all Trends) 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?

Answer 1

The error is probably due to the fact that you are trying to access the database while accessing the database. You should try to separate the two Linq expressions. Maybe 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)) 

Answer 2

My guess is that the MySql connector does not support MARS (multiple active result sets). In this case, you cannot do this:

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

After that, you list the result of the first L2S request (= DataReader is still open), and you execute the second L2S request for each record from the first (= you need a second DataReader ).

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

Answer 3

calling .toList () in the first request

Answer 4

Today I ran into this error. I used the connection for more than one. 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();