Why do Entity Framework Core calls in a loop throw InvalidOperationException?

Question

Why does the following use of Entity Framework Core 1.1.1 result in an exception?

// Arrange.
using(var context = new BasicContext())
{
   Author[] authors = new[]
   {
      new Author { Name = "Bob"},
      new Author { Name = "Fred" }
   };
   context.Authors.AddRange(authors);

   Book[] books = new[]
   {
      new Book { Title = "Book 1" },
      new Book { Title = "Book 2" }
   };

   context.SaveChanges();            
}

// Act.
bool exception = false;

using(var context = new BasicContext())
{
   foreach(Author a in context.Authors)
   {
      try
      {
         string title = context.Books.First().Title;
      }
      catch(Exception)
      {
         exception = true;
      }
   }
}

// Assert.
Assert.False(exception);

Exception raised using Entity connector for Postgress (Npgsql) is:

Npgsql.NpgsqlOperationInProgressException : A command is already in progress: SELECT "a"."AuthorId", "a"."Name"
FROM "Authors" AS "a"
Stack Trace:
   at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState, NpgsqlCommand command)
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String execute
Method, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass20_0`1.<CompileQueryCore>b__0(QueryContext qc
)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at BasicTesting.BasicTests.TestEnumerate() in C:\_Home\Development\Workspaces\DotnetCoreTesting\EntityTesting3\BasicTesting\BasicTests.cs:line 147If 'foreach(Author a in context.Authors)' is replaced with 'foreach(Author a in context.Authors.ToArray())' no exception occurs.

This is not what I would expect to happen.

When the foreach loop is reached context.Authors should be evaluated. when the subsequence 'Books.First' expression results in another database operation there should not be an existing operation in progress.

Possible duplicate of [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — Igor, Oct 09 '17 at 17:25
Possible duplicate of [Error “There is already an open DataReader associated with this Command which must be closed first” when using 2 distinct commands](https://stackoverflow.com/questions/18475195) — Igor, Oct 09 '17 at 17:27
Not sure if `postgressql` has the option to enable MARS (Multiple Active Result Sets) but that *could* fix your issue as well. — Igor, Oct 09 '17 at 17:27

score 1 · Accepted Answer · answered Oct 09 '17 at 17:17

Some providers don't support having multiple open readers. The best way to avoid this is to finish reading all of the authors before querying the books. Adding .ToList() is the simplest (but not necessarily the best) way to do this.

foreach (Author a in context.Authors.ToList())
{
    // ... context.Books ...
}

Why do Entity Framework Core calls in a loop throw InvalidOperationException?

1 Answers1