Fix Open DataReader Errors in Entity Framework LINQ (C#)

This error occurs because Entity Framework is still reading rows for the outer query when DateLastUpdated starts another database query using the same DbContext connection. You will learn how deferred execution causes this, when ToList() is appropriate, why it can lead to N+1 queries, and how to calculate related values in one database query instead.

Entity Framework LINQ queries are usually deferred: creating a query does not immediately retrieve all rows from SQL Server. Rows are fetched later, when code enumerates the query—for example, during Select, foreach, or ToList().

In this example, AsEnumerable() switches subsequent LINQ operations to in-memory LINQ, but it does not load all account rows. The outer query is still being streamed from a database DataReader.

While each outer account row is being processed, this code runs:

DateLastUpdated(account.CreditRegistryId, account.AccountNumber)

That method executes a second query through the same context. With a connection that does not support multiple active result sets, SQL Server cannot execute that second command until the first reader has finished and closed. Therefore, it throws the open DataReader exception.

The important idea is: do not execute a database query inside a projection that is currently enumerating another query from the same context.

This matters beyond avoiding an exception. Even if the connection is configured to permit multiple readers, one query per account creates an N+1 query pattern: one query for accounts plus one query for each account's history. That can become slow very quickly.

Think of a database connection as a single checkout counter.

• The outer account query is placing items on the counter one at a time.
• The DataReader is the cashier actively scanning those items.
• For every item, DateLastUpdated tries to start a second checkout transaction at the same counter.

The cashier cannot begin the second transaction until the first one is complete. You can either:

1. finish collecting the first set of items before starting more transactions (ToList()), or
2. ask for all necessary information in one combined transaction (a join, grouping, or aggregate query).

The second option is generally better for report-style queries.

A query remains deferred until it is enumerated:

IQueryable<Account> query = context.Accounts.Where(a => a.IsActive);

// SQL has not necessarily run yet.

List<Account> accounts = query.ToList();

// The rows have now been loaded and the reader is closed.

AsEnumerable() changes the LINQ provider, but it is not the same as materializing results:

var query = context.Accounts.AsEnumerable(); // Still enumerated lazily.
var list = context.Accounts.ToList();        // Rows are loaded now.

A minimal safety fix is to materialize the outer query before calling a method that issues additional queries:

var accountRows = (
    from account in context.Accounts
    select new
    {
        account.CreditRegistryId,
        AccountNumber = account.AccountNo,
        account.AccountType,
        account.DateOpened
    }
).ToList();

var report = accountRows
    .Select((account, index) => new AccountsReport
    {
        RecordNumber = FormattedRowNumber(account, index + 1),
        CreditRegistryId = account.CreditRegistryId,
        AccountNumber = FormattedAccountNumber(account.AccountType, account.AccountNumber),
        DateLastUpdated = DateLastUpdated(account.CreditRegistryId, account.AccountNumber)
    })
    .ToList();

This prevents overlapping readers because accountRows has already been loaded. However, DateLastUpdated still runs one SQL query per account, so it is a correctness fix rather than the most efficient design.

Consider this code:

var activeAccounts = context.Accounts.Where(a => a.IsActive);

var report = activeAccounts
    .AsEnumerable()
    .Select(account => new
    {
        account.AccountNo,
        LastUpdated = context.AccountHistory
            .Where(h => h.AccountNo == account.AccountNo)
            .Max(h => h.LastUpdated)
    })
    .ToList();

Execution proceeds as follows:

1. activeAccounts is created. It represents a SQL query but has not been fully read.
2. AsEnumerable() says that the following Select uses LINQ to Objects. It does not create a list.
3. ToList() begins enumerating the final sequence.
4. Entity Framework opens a DataReader and starts retrieving active account rows.
5. The first account reaches the Select lambda.
6. The Max expression attempts to execute a history query using the same context and connection.
7. The first reader is still open, so the second query cannot start. The exception is thrown.

Now compare it with materialization:

var accounts = context.Accounts
    .Where(a => a.IsActive)
    .ToList();

var report = accounts
    .Select(account => new
    {
        account.AccountNo,
        LastUpdated = context.AccountHistory
            .Where(h => h.AccountNo == account.AccountNo)
            .Max(h => h.LastUpdated)
    })
    .ToList();

Here, the first ToList() consumes the account reader completely. Only then does the projection execute history queries.

This issue commonly appears when an application calculates related values while looping over query results:

• Reports: Fetching each customer's latest order date while streaming customers.
• Admin dashboards: Looking up a status, count, or balance for every displayed row.
• API endpoints: Enriching each product with inventory, review counts, or discount details.
• Data exports: Building CSV or Excel rows and querying extra data for each record.
• Batch processing: Iterating through entities and loading related records one at a time.

For small, temporary scripts, materializing the outer list may be sufficient. For a production endpoint or report with many rows, calculate related data with joins, grouping, eager loading, or batched queries.

In real Entity Framework code, prefer a query shape that lets SQL Server compute related aggregates once.

For example, calculate the latest history timestamp per account, then join it to accounts:

var latestHistoryByAccount =
    from history in context.AccountHistory
    group history by new
    {
        history.CreditorRegistryId,
        history.AccountNo
    }
    into historyGroup
    select new
    {
        historyGroup.Key.CreditorRegistryId,
        historyGroup.Key.AccountNo,
        DateLastUpdated = historyGroup.Max(h => h.LastUpdated)
    };

var reportRows = (
    from account in context.Accounts
    join latestHistory in latestHistoryByAccount
        on new
        {
            account.CreditRegistryId,
            AccountNo = account.AccountNo
        }
        equals new
        {
            latestHistory.CreditorRegistryId,
            latestHistory.AccountNo
        }
        into historyJoin
    from latestHistory in historyJoin.DefaultIfEmpty()
    select new
    {
        account.CreditRegistryId,
        AccountNumber = account.AccountNo,
        account.AccountType,
        account.DateOpened,
        DateLastUpdated = (DateTime?)latestHistory.DateLastUpdated
    }
).ToList();

The ToList() occurs after the database work is expressed as one query. Formatting and row numbering can then happen in memory:

var report = reportRows
    .Select((account, index) => new AccountsReport
    {
        RecordNumber = FormattedRowNumber(account, index + 1),
        CreditRegistryId = account.CreditRegistryId,
        AccountNumber = FormattedAccountNumber(account.AccountType, account.AccountNumber),
        DateLastUpdated = account.DateLastUpdated
    })
    .OrderBy(account => account.FormattedRecordNumber)
    .ToList();

Other common patterns are:

• Use Include when you genuinely need related entity objects, not just an aggregate.
• Use a guard clause or nullable result when a related record may not exist.
• Keep database filtering, grouping, joining, and aggregation in IQueryable as long as possible.
• Switch to in-memory LINQ only for operations SQL cannot perform easily, such as custom C# formatting methods.

Assuming AsEnumerable() loads the query

This is a frequent misunderstanding:

var rows = context.Accounts.AsEnumerable();

rows is still lazy. It starts reading only when enumerated.

Use ToList() when you explicitly need to finish database enumeration:

var rows = context.Accounts.ToList();

Running a query inside Select

The following pattern can create an overlapping reader and always risks N+1 queries:

var result = context.Accounts.Select(account => new
{
    account.AccountNo,
    Count = context.AccountHistory.Count(h => h.AccountNo == account.AccountNo)
});

If Entity Framework cannot translate the whole expression to one SQL query, it may execute inner work repeatedly. Build a join or grouped aggregate instead.

Using Max() when no history exists

Max() on an empty sequence can fail, depending on the queried type and provider. Make the result nullable when an account may not have history:

DateTime? lastUpdated = context.AccountHistory
    .Where(h => h.CreditorRegistryId == creditorRegistryId && h.AccountNo == accountNo)
    .Select(h => (DateTime?)h.LastUpdated)
    .Max();

Enabling MARS as the primary solution

SQL Server's Multiple Active Result Sets (MARS) can allow more than one active reader on a connection. It may remove this specific exception, but it does not remove the N+1 performance problem. Prefer a better query design first.

Accidentally duplicating accounts

This query:

from account in context.Accounts
from guarantor in account.Gurantors
select account

returns one row for each account-guarantor combination. If the guarantor is not used for filtering or projection, accounts with multiple guarantors can appear multiple times. Remove that from clause or use Distinct() only when duplicates are truly intended and understood.

IQueryable<T> represents a query that can still be translated to SQL. IEnumerable<T> represents an in-memory sequence or a sequence being enumerated. Calling AsEnumerable() changes the interface; calling ToList() executes and stores the results.

• Entity Framework queries use deferred execution.
• A DataReader stays open while a query is being enumerated.
• AsEnumerable() does not load all rows.
• ToList() executes the query and closes its reader after loading the list.
• Avoid querying the same DbContext inside a Select over another active database query.
• A query-per-row pattern is called N+1 and is usually inefficient.
• Prefer group ... by, join, aggregates such as Max, and projections to calculate report values in SQL.
• Use nullable aggregates when a related row might not exist:

.Select(x => (DateTime?)x.LastUpdated).Max()

• MARS can mask the reader error but does not fix unnecessary query volume.
• Verify that navigation-property iteration does not accidentally duplicate parent rows.

What causes "There is already an open DataReader" in Entity Framework?

It usually means code starts a second command on the same database connection while the first query is still being read. Nested queries during lazy enumeration are a common cause.

Does AsEnumerable() close the DataReader?

No. It changes later operations from LINQ-to-Entities to LINQ-to-Objects, but the source may still be fetched lazily from the database.

Does ToList() fix the open DataReader error?

It can. Calling ToList() on the outer query loads its rows before later code starts another query. Be aware that this may still produce N+1 queries.

What is an N+1 query problem?

It is one initial query plus one additional query for every returned row. For 500 accounts, a report could execute 501 database queries.

Should I enable Multiple Active Result Sets (MARS)?

MARS may permit concurrent readers and avoid this exception, but it is usually better to restructure the query. It does not solve N+1 performance costs.

Why can Max() fail for an account with no history?

There may be no values to aggregate. Project to a nullable value and use DateTime? if no history is a valid case.

Why are there duplicate account rows in my report?

Iterating through account.Gurantors creates one output row per guarantor. If an account has several guarantors, it appears several times unless you intentionally group or remove that iteration.

• Deferred execution in LINQ — explains when a query actually runs.
• IQueryable vs IEnumerable — clarifies SQL translation versus in-memory processing.
• Entity Framework projections — selects only the fields a report or API needs.
• LINQ joins — combines related data without per-row queries.
• LINQ GroupBy and aggregates — calculates values such as latest dates and counts in SQL.
• N+1 query problem — identifies inefficient query-per-item code.
• Eager loading with Include — loads related entities when their full data is needed.
• Nullable value types in C# — handles missing related data safely.