Search code examples
c#linqrepositoryiqueryabledatareader

Error when calling another repo - 'There is already an open DataReader associated with this Command which must be closed first'

 public IQueryable<Application> GetAppById(Guid Id)                     
 {
        return Repository.Query().Where(c => c.Id == Id).Select(c => c.App)
 }

I got this code above and it seems like it doesn't return right. If I call another repository in the DB such as Repository2.Query().Where(??code??). I got the error below.

ERROR: {"There is already an open DataReader associated with this Command which must be closed first."}

However, when I change the return to ICollection<> it responses right.

 public ICollection<Application> GetAppById(Guid Id)                     
 {
        return Repository.Query().Where(c => c.Id == Id).Select(c => c.App).ToList();
 }

I debug IQueryable return, there is no error but I couldn't find find the data that it retrieves. The error takes place when you call another to the repo.

Solution

  • The message is correct; you have allowed two commands to execute simultaneously. The first method shown returns a "query" - it doesn't actually execute anything until you iterate it - and is open until you stop iterating it. Indeed, the entire purpose of IQueryable<T> is to allow additional composition prior to execution. So assuming you are using foreach, the query is active for the duration of the foreach, not the call to GetAppById itself. So if you do the classic "N+1":

    foreach(var row in SomeOuterQuery(...)) { // N+1, not a great idea
    SomeInnerQuery(row, ...);
}

    then you are absolutely running multiple simultaneous commands.

    There are three main fixes:

    • run the first query in full, and then iterate the results - that is what adding ToList does: it moves the foreach to inside GetAppById
    • enable "MARS" (Multiple Active Result Sets) - note: this is not advice, simply a "this will work" (but: not everything that works is a good idea)
    • restructure the work to never need the "N" in the "N+1", for example by fetching the additional data at the same time as the outer list (multiple result grids from a single query, for example)