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

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    gt  new AccountsReport                                                 RecordNumber   FormattedRowNumber account  index   1                           CreditRegistryId   account CreditRegistryId                          DateLastUpdated   DateLastUpdated account CreditRegistryId  account AccountNumber                           AccountNumber   FormattedAccountNumber account AccountType  account AccountNumber                                          OrderBy c  gt c FormattedRecordNumber                   ThenByDescending c   gt  c StateChangeDate     public DateTime DateLastUpdated long creditorRegistryId  string accountNo        return  from h in context AccountHistory             where h CreditorRegistryId    creditorRegistryId  amp  amp  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 lt T gt  GetEnumerator    38    System Linq Enumerable Single IEnumerable 1 source   114    System Data Objects ELinq ObjectQueryProvider  lt GetElementFunction gt 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  lt AccountDetails gt b  88 AccountsReport account  Int32 index  in D  Freelance Work SuperExpert CreditRegistry CreditRegistry Repositories CreditRegistryRepository cs 1250    System Linq  lt SelectIterator gt d  7 2 MoveNext    198    System Linq Buffer 1  ctor IEnumerable 1 source   217    System Linq  lt GetEnumerator gt d  0 MoveNext    96

User · Answer

In my case  I had opened a query from data context  like      Dim stores   DataContext Stores            Where Function d  filter Contains d code           and then subsequently queried the same         Dim stores   DataContext Stores            Where Function d  filter Contains d code   ToList   Adding the  ToList to the first resolved my issue   I think it makes sense to wrap this in a property like   Public ReadOnly Property Stores As List Of Store      Get         If  stores Is Nothing Then              stores   DataContext Stores                    Where Function d  Filters Contains d code   ToList         End If         Return  stores     End Get End Property   Where  stores is a private variable  and Filters is also a readonly property that reads from AppSettings

User · Answer

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

User · Answer

I solved this problem by changing await  accountSessionDataModel SaveChangesAsync    to  accountSessionDataModel SaveChanges    in my Repository class    public async Task lt Session gt  CreateSession                 var session   new Session              accountSessionDataModel Sessions Add session           await  accountSessionDataModel SaveChangesAsync             Changed it to    public Session CreateSession                 var session   new Session              accountSessionDataModel Sessions Add session            accountSessionDataModel SaveChanges             The problem was that I updated the Sessions in the frontend after creating a session  in code   but because SaveChangesAsync happens asynchronously  fetching the sessions caused this error because apparently the SaveChangesAsync operation was not yet ready

User · Answer

This can happen if you execute a query while iterating over the results from another query  It is not clear from your example where this happens because the example is not complete   One thing that can cause this is lazy loading triggered when iterating over the results of some query   This can be easily solved by allowing MARS in your connection string  Add MultipleActiveResultSets true to the provider part of your connection string  where Data Source  Initial Catalog  etc  are specified

User · Answer

In my case  using Include   solved this error and depending on the situation can be a lot more efficient then issuing multiple queries when it can all be queried at once with a join    IEnumerable lt User gt  users   db Users Include  Projects Tasks Messages     foreach  User user in users        Console WriteLine user Name       foreach  Project project in user Projects                Console WriteLine   t  project Name           foreach  Task task in project Tasks                        Console WriteLine   t t    task Subject               foreach  Message message in task Messages                                Console WriteLine   t t t    message Text

User · Answer

In my case  I had to set the MultipleActiveResultSets to True in the connection string  Then it appeared another error  the real one  about not being able to run 2  SQL  commands at the same time over the same data context   EF Core  Code first  So the solution for me was to look for any other asynchronous command execution and turn them to synchronous  as I had just one DbContext for both commands   I hope it helps you

User · Answer

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

User · Answer

For those finding this via Google  I was getting this error because  as suggested by the error  I failed to close a SqlDataReader prior to creating another on the same SqlCommand  mistakenly assuming that it would be garbage collected when leaving the method it was created in     I solved the issue by calling sqlDataReader Close    before creating the second reader

User · Answer

Here is a working connection string for someone who needs reference       lt connectionStrings gt       lt add name  IdentityConnection  connectionString  Data Source  LocalDb  v11 0 AttachDbFilename  DataDirectory  IdentityDb mdf Integrated Security True MultipleActiveResultSets true   providerName  System Data SqlClient    gt     lt  connectionStrings gt

User · Answer

Well for me it was my own bug   I was trying to run an INSERT using SqlCommand executeReader   when I should have been using SqlCommand ExecuteNonQuery     It was opened and never closed  causing the error   Watch out for this oversight

User · Answer

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

User · Answer

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    gt  new AccountsReport                       RecordNumber   FormattedRowNumber account  index   1             CreditRegistryId   account CreditRegistryId            DateLastUpdated                                                     from h in context AccountHistory                                                  where h CreditorRegistryId    creditorRegistryId                                 amp  amp  h AccountNo    accountNo                                                  select h LastUpdated  Max              AccountNumber   FormattedAccountNumber account AccountType  account AccountNumber                      OrderBy c  gt c FormattedRecordNumber           ThenByDescending c   gt  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

User · Answer

I dont know whether this is duplicate answer or not  If it is I am sorry  I just want to let the needy know how I solved my issue using ToList    In my case I got same exception for below query  int id   adjustmentContext InformationRequestOrderLinks Where               item   gt  item OrderNumber    irOrderLinkVO OrderNumber                     amp  amp  item InformationRequestId    irOrderLinkVO InformationRequestId                Max item   gt  item Id    I solved like below List lt Entities InformationRequestOrderLink gt  links          adjustmentContext InformationRequestOrderLinks             Where item   gt  item OrderNumber    irOrderLinkVO OrderNumber                          amp  amp  item InformationRequestId    irOrderLinkVO InformationRequestId              ToList     int id   0   if  links Any        id   links Max x   gt  x Id     if  id    0      do something here

User · Answer

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

User · Answer

You can use the ToList   method before the return statement   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    gt  new AccountsReport                                                       RecordNumber   FormattedRowNumber account  index   1                              CreditRegistryId   account CreditRegistryId                                DateLastUpdated   DateLastUpdated account CreditRegistryId  account AccountNumber                              AccountNumber   FormattedAccountNumber account AccountType  account AccountNumber    OrderBy c  gt c FormattedRecordNumber  ThenByDescending c   gt  c StateChangeDate  ToList       public DateTime DateLastUpdated long creditorRegistryId  string accountNo                var dateReported    from h in context AccountHistory                             where h CreditorRegistryId    creditorRegistryId  amp  amp  h AccountNo    accountNo                             select h LastUpdated  Max            return dateReported

User · Answer

I am using web service in my tool  where those service fetch the stored procedure  while more number of client tool fetches the web service  this problem arises  I have fixed by specifying the Synchronized attribute for those function fetches the  stored procedure  now it is working fine  the error never showed up in my tool     MethodImpl MethodImplOptions Synchronized    public static List lt t gt  MyDBFunction string parameter1            This attribute allows to process one request at a time   so this solves the Issue

User · Answer

Most likely this issue happens because of  lazy loading  feature of Entity Framework  Usually  unless explicitly required during initial fetch  all joined data  anything that stored in other database tables  is fetched only when required  In many cases that is a good thing  since it prevents from fetching unnecessary data and thus improve query performance  no joins  and saves bandwidth   In the situation described in the question  initial fetch is performed  and during  select  phase missing lazy loading data is requested  additional queries are issued and then EF is complaining about  open DataReader    Workaround proposed in the accepted answer will allow execution of these queries  and indeed the whole request will succeed   However  if you will examine requests sent to the database  you will notice multiple requests - additional request for each missing  lazy loaded  data  This might be a performance killer   A better approach is to tell to EF to preload all needed lazy loaded data during the initial query  This can be done using  Include  statement   using System Data Entity   query   query Include a   gt  a LazyLoadedProperty     This way  all needed joins will be performed and all needed data will be returned as a single query  The issue described in the question will be solved

User · Answer

use the syntax  ToList   to convert object read from db to list to avoid being re-read again Hope this would work for it  Thanks

[c#] There is already an open DataReader associated with this Command which must be closed first

Examples related to c#

Examples related to entity-framework

Examples related to entity-framework-4