There is already an open DataReader associated wit

Question

I have this query and I get the error in this function:

var accounts = from account in context.Accounts
               from guranteer in account.Gurantors
               select new AccountsReport
               {
                   CreditRegistryId = account.CreditRegistryId,
                   AccountNumber = account.AccountNo,
                   DateOpened = account.DateOpened,
               };

 return accounts.AsEnumerable()
                .Select((account, index) => new AccountsReport()
                    {
                        RecordNumber = FormattedRowNumber(account, index + 1),
                        CreditRegistryId = account.CreditRegistryId,
                        DateLastUpdated = DateLastUpdated(account.CreditRegistryId, account.AccountNumber),
                        AccountNumber = FormattedAccountNumber(account.AccountType, account.AccountNumber)
                    })
                .OrderBy(c=>c.FormattedRecordNumber)
                .ThenByDescending(c => c.StateChangeDate);


public DateTime DateLastUpdated(long creditorRegistryId, string accountNo)
{
    return (from h in context.AccountHistory
            where h.CreditorRegistryId == creditorRegistryId && h.AccountNo == accountNo
            select h.LastUpdated).Max();
}

Error is:

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

Update:

stack trace added:

InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.]
   System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) +5008639
   System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +23
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +144
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +87
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   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) +683
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +119
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +38
   System.Linq.Enumerable.Single(IEnumerable`1 source) +114
   System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3(IEnumerable`1 sequence) +4
   System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle(IEnumerable`1 query, Expression queryRoot) +29
   System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +91
   System.Data.Entity.Internal.Linq.DbQueryProvider.Execute(Expression expression) +69
   System.Linq.Queryable.Max(IQueryable`1 source) +216
   CreditRegistry.Repositories.CreditRegistryRepository.DateLastUpdated(Int64 creditorRegistryId, String accountNo) in D:\Freelance Work\SuperExpert\CreditRegistry\CreditRegistry\Repositories\CreditRegistryRepository.cs:1497
   CreditRegistry.Repositories.CreditRegistryRepository.<AccountDetails>b__88(AccountsReport account, Int32 index) in D:\Freelance Work\SuperExpert\CreditRegistry\CreditRegistry\Repositories\CreditRegistryRepository.cs:1250
   System.Linq.<SelectIterator>d__7`2.MoveNext() +198
   System.Linq.Buffer`1..ctor(IEnumerable`1 source) +217
   System.Linq.<GetEnumerator>d__0.MoveNext() +96

Answer 1

In addition to Ladislav Mrnka's answer:

If you are publishing and overriding container on Settings tab, you can set MultipleActiveResultSet to True. You can find this option by clicking Advanced... and it's going to be under Advanced group.

Answer 2

I had the same error, when I tried to update some records within read loop. I've tried the most voted answer MultipleActiveResultSets=true and found, that it's just workaround to get the next error 

New transaction is not allowed because there are other threads running in the session

The best approach, that will work for huge ResultSets is to use chunks and open separate context for each chunk as described in  SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

Answer 3

Here is a working connection string for someone who needs reference.

  <connectionStrings>
    <add name="IdentityConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\IdentityDb.mdf;Integrated Security=True;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient" />
  </connectionStrings>

Answer 4

It appears that you're calling DateLastUpdated from within an active query using the same EF context and DateLastUpdate issues a command to the data store itself. Entity Framework only supports one active command per context at a time.

You can refactor your above two queries into one like this:

return accounts.AsEnumerable()
        .Select((account, index) => new AccountsReport()
        {
          RecordNumber = FormattedRowNumber(account, index + 1),
          CreditRegistryId = account.CreditRegistryId,
          DateLastUpdated = (
                                                from h in context.AccountHistory 
                                                where h.CreditorRegistryId == creditorRegistryId 
                              && h.AccountNo == accountNo 
                                                select h.LastUpdated).Max(),
          AccountNumber = FormattedAccountNumber(account.AccountType, account.AccountNumber)
        })
        .OrderBy(c=>c.FormattedRecordNumber)
        .ThenByDescending(c => c.StateChangeDate);

I also noticed you're calling functions like FormattedAccountNumber and FormattedRecordNumber in the queries. Unless these are stored procs or functions you've imported from your database into the entity data model and mapped correct, these will also throw excepts as EF will not know how to translate those functions in to statements it can send to the data store.

Also note, calling AsEnumerable doesn't force the query to execute. Until the query execution is deferred until enumerated. You can force enumeration with ToList or ToArray if you so desire.

Answer 5

This is extracted from a real world scenario:

• Code works well in a Stage environment with MultipleActiveResultSets is set in the connection string
• Code published to Production environment without MultipleActiveResultSets=true
• So many pages/calls work while a single one is failing
• Looking closer at the call, there is an unnecessary call made to the db and needs to be removed
• Set MultipleActiveResultSets=true in Production and publish cleaned up code, everything works well and, efficiently

In conclusion, without forgetting about MultipleActiveResultSets, the code might have run for a long time before discovering a redundant db call that could be very costly, and I suggest not to fully depend on setting the MultipleActiveResultSets attribute but also find out why the code needs it where it failed.

Answer 6

As a side-note...this can also happen when there is a problem with (internal) data-mapping from SQL Objects.

For instance...

I created a SQL Scalar Function that accidentally returned a VARCHAR...and then...used it to generate a column in a VIEW. The VIEW was correctly mapped in the DbContext...so Linq was calling it just fine. However, the Entity expected DateTime? and the VIEW was returning String.

Which ODDLY throws...

"There is already an open DataReader associated with this Command which must be closed first"

It was hard to figure out...but after I corrected the return parameters...all was well