EF Core and SQL Server: 'There is already an open DataReader associated with this Connection'

InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first. — an EF Core query ran inside a foreach that was still reading another query on the same SQL Server connection. Put .ToList() on the outer query so its rows are read to the end before the loop body runs.

Adding MultipleActiveResultSets=True to the connection string also made the same loop run in the reproduction. ToList() is the smaller change: it needs no connection-string edit and it shows in the code where the data is loaded.

The error

PS> dotnet run -- bad
...
Unhandled exception. System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   at Microsoft.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
...
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Program.<Main>$(String[] args) in ...\EfMars\Program.cs:line 25
Dropped: True

The cuts hide NuGet audit warnings printed by dotnet run and SqlClient and EF Core frames. Dropped: True comes from the finally block that deletes the test database.

Why it happens

foreach (var c in db.Customers)
{
    var count = db.Orders.Count(o => o.CustomerId == c.Id);
    Console.WriteLine($"{c.Name}: {count} orders");
}

foreach (var c in db.Customers) enumerated the customers query directly, so its reader was still open while the loop body ran. Line 25 is the Count() inside the loop: a second command on the same connection. The top frame of the trace is SqlClient's ValidateConnectionForExecute, and the message says what it found: a reader already open on that connection.

The fix

foreach (var c in db.Customers.ToList())
{
    var count = db.Orders.Count(o => o.CustomerId == c.Id);
    Console.WriteLine($"{c.Name}: {count} orders");
}

PS> dotnet run -- fix
...
Maria Garcia: 2 orders
David Chen: 1 orders
Dropped: True

ToList() read both customers into memory and closed the reader, so each Count() had the connection to itself. The alternative, run with the original loop and only the connection string changed:

if (mode == "mars") cs += ";MultipleActiveResultSets=True";

PS> dotnet run -- mars
...
Maria Garcia: 2 orders
David Chen: 1 orders
Dropped: True

Both gave the same output, two orders for Maria Garcia and one for David Chen. Both versions still run one Count() query per customer inside the loop; the fixes change when the customers are read, not how many queries the loop sends.

How it was reproduced

A console project from dotnet new console -n EfMars on .NET SDK 10.0.403 with Microsoft.EntityFrameworkCore.SqlServer 10.0.14 (which brought in Microsoft.Data.SqlClient 6.1.7), against SQL Server LocalDB (localdb)\MSSQLLocalDB with a test database fix8_ef that each run created, seeded with two fictional customers and three orders, and deleted. The connection string was Server=(localdb)\MSSQLLocalDB;Database=fix8_ef;Trusted_Connection=True. Every run, the failing one included, printed Dropped: True, and a sqlcmd query on sys.databases afterwards returned no row for fix8_ef, so the test left nothing behind on the LocalDB instance.

Frequently asked

How do I fix There is already an open DataReader associated with this Connection in EF Core?

Materialise the outer query with ToList before the loop, so its reader is closed before the queries inside the loop run. In the reproduction that alone fixed it.

Does MultipleActiveResultSets=True fix the open DataReader error?

In the reproduction it did: the same foreach with a Count query inside ran without error against LocalDB once MultipleActiveResultSets=True was added to the connection string.

Why does the open DataReader error point at a line inside my foreach?

Because the second query, inside the loop, is the one SqlClient refused. In the reproduction it was line 25, the Count call, while the outer foreach still held the first reader open.

More decoded errors in the Fixes category.