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

Divergent

I've got this error when having one query for filling an ASP.NET MVC Kendo UI data grid where I accidentially had additional queries for each row of the initial query. See my similar answer here. Solution was to get rid of that additional query per row. This also enhanced performance a lot. – Botryoidal

Answer 1

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

Selemas

This worked for me. If you want to read more about Enabling Multiple Active Result Sets (MARS) see msdn.microsoft.com/en-us/library/h32h3abf(v=vs.100).aspx. Consider reading up on Disadvantages of MARS too #374944 – Lamrert

Taking performance into account you can also resolve this by including System.Data.Entity and then using Include statements to ensure this secondary data is loaded in the original query. If you enable MARS, turning it off to check for these repeated data loads can help speed up your data processing calls by reducing round trips. – Branch

Thanks! This fixed my issue. It is worth noting that when using LocalDB MultipleActiveResultSets seems to be false by default (as opposed to regular SQL Server) which is the problem I was having. – Ronald

Enabling MARS should only be done for a very small subset of problems/use-cases. In most cases, the error in question is caused by BAD CODE within the calling application. More details here: devproconnections.com/development/… – Roslyn

Adding .ToList() after your.Include().Where() will likely solve the problem. – Nona

Great one. Though I am really not sure why this never seemed to occur during development but was suddenly a problem as soon as I deployed the changes to the live environment. EDIT: Actually never mind. The statemenet was missing entirely in the live environment. – Zambia

"The 'MultipleActiveResultSets' attribute is not allowed." for me ? – Crosson

I agree with MichaelK.Campbell and Tom Stickel. There is some code smell here. – Tran

My problem: calling .save() inside of a foreach that was iterating on a Where() Queryable. Solution: add .ToList() before foreach. – Minim

To make a global SQL connection wide change for one query is ridiculous. The correct answer should be the ToList one below. A local fix (i.e. just change the query) for a localised problem! – Upanchor

I retract my first comment, read: devproconnections.com/development/… – Lesslie

Definitely hacky...ToList() does fix it. If it does not then look at your code again. – Rebarebah

Data Source=localhost; Initial Catalog=dbname; User Id=usename; Password=password; Connection Timeout=300; Persist Security Info=True; MultipleActiveResultSets=true; – Fragonard

this has become necessary for EF 6.2.0 – Mathura

this is only for SQL Server or is the same for MySQL/MariaDB? – Vacua

In my case I was using forEachAsync instead of foreach. – Adamson

Worked for me. :) Though, I am not clear if this might open any other potential issues to the application. – Pangaro

This should not be the recommended answer, you should track down the IQueryable / IEnumerable query, that is not enumerated, and enumerate it by a foreach or tolist. – Unimpeachable

that devproconnections article linked by Gareth & Michael moved to itprotoday.com/microsoft-visual-studio/… , archive.org also has it. – Ancestress

Adding the MultipleActiveResultsSet=true fixed my problem instantly, thanks. – Stocking

man i have been coding for 13 years ! and its the first time to know that this have a real and active solution thank you, you really saved me – Obovate

Answer 2

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) => 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)
       .ToList();


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

Elegant

I have had this error so many times now... and every time I forget! The answer to the question is always to use ToList(). – Makeup

Are there any downsides to this? If you have 100k rows I doubt this can be good. – Spotlight

@MartinMazzaDawson , You really need 100K records at once query execution?? i think that , using pagination is a good idea for this situation – Elegant

sorry to raise an old topic but i came into the same error while developing a RepositoryPattern and i solved it by adding ".ToList() or Single() or Count()" to every method of the Repository. While in the beginning i was just returning ".AsEnumerable()". Now my question is: should the repository returning the "ToList()" , or is this something that shold be demanded to the final consumer (i.e.: the service/business logic) – Bligh

Works for me. Adding .ToList solves the issue of Decimal support issue in JetEntityFrameworkProvider. Total = storeDb.OF_Carts.Where(x => x.CartId == ShoppingCartId).ToList().Sum(t => t.Quantity * t.Item.UnitPrice); – Lecroy

Because .ToList() causes the iEnumerable to actually go and fill itself from the DB, which iterates over the entire recordset, and (I believe) closes it. Because MARS is not set in the connection string, then only 1 recordset can be "open" at a time. Meaning, "go get the data and store it in my object, then close so that other bits of my code can hit the DB if necessary." This is obviously a very high level, and possibly semantically flawed description. But it's accurate as far as a human friendly definition. – Particolored

This is the solution for databases like mysql since MARS is not possible there – Endurant

Answer 3

Use the syntax .ToList() to convert object read from db to list to avoid being re-read again.

Hysteria

Answer 4

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>

Youthen

Enabling MARS is a workaround, NOT a solution to the problem. – Faculty

From the MARS Documentation page: "MARS operations are not thread-safe." That means, if the problem arises from multiple threads accessing the Context, MARS is (probably) not the solution. – Hyrax

Answer 5

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

Arawakan

This is the best solution if your application otherwise does't require MARS. – Recoup

Answer 6

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 => item.OrderNumber == irOrderLinkVO.OrderNumber 
                  && item.InformationRequestId == irOrderLinkVO.InformationRequestId)
             .Max(item => item.Id);

I solved like below

List<Entities.InformationRequestOrderLink> links = 
      adjustmentContext.InformationRequestOrderLinks
           .Where(item => item.OrderNumber == irOrderLinkVO.OrderNumber 
                       && item.InformationRequestId == irOrderLinkVO.InformationRequestId)
           .ToList();

int id = 0;

if (links.Any())
{
  id = links.Max(x => x.Id);
}
if (id == 0)
{
//do something here
}

Backhouse

I recommend this approach if you already have MARS give issues. – Lox

Answer 7

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.

Gemma

If you want you can refactor the query you're performing to get the DateLastUpdated directy into your Select projection for the Accounts Report query and get the desired effect without the error. – Gemma

I am getting the same error after putting code of function inside main query – Divergent

Answer 8

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.

Sensualism

Answer 9

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

Verbid

Answer 10

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 => 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.

Leges

This is a valid answer, because I went from using Include to using EntityEntry.Collection().Load(), and my solution went from working to broken. Unfortunately, include for a generic can't "ThenInclude" another generic, so I'm still trying to make EntityEntry.Collection().Load() work. – Earle

Answer 11

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.

Pokeweed

Answer 12

I solved this problem by changing await _accountSessionDataModel.SaveChangesAsync(); to _accountSessionDataModel.SaveChanges(); in my Repository class.

 public async Task<Session> 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.

Fronton

Answer 13

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.

Zarathustra

Answer 14

The same error happened to me when I was looping and updating data on IEnumerable<MyClass> When I changed the looped-on collection to be List<MyClass>, and filled it by converting by .ToList(), it solved and updated without any errors.

Microbiology

Answer 15

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

Navicular

Answer 16

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.

Fuentes

It was same issue from my side. I needed SqlCommand.executeReader() because I am getting Inserted rows ID. So: I used SqlDataReader.Close(); Sql Command.Dispose(); Thanks @Andrew Taylor – Polynices

Answer 17

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.

Irreconcilable

Answer 18

I was iterating, already through a query. I resolved mine by placing a .ToList() in my @foreach statement.

foreach (var user in report.myUsers.ToList())
{
}

Emplane

Answer 19

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<t> MyDBFunction(string parameter1)
  {
  }

This attribute allows to process one request at a time. so this solves the Issue.

Glynda

Answer 20

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

Pallaten

Answer 21

This can also happen if you use Transaction scope with TransactionScopeAsyncFlowOption enabled, but forget to await some of the repository methods.

Moorhead