'AsyncEnumerableReader' reached the configured maximum size of the buffer when enumerating a value

Question

I am using an async/await method returning IAsyncEnumerable<> to retrieve rows from a SQL Server database and provide them via a Web API .Net Core 3.0 interface. It works fine until I exceed 8192 rows. It fails for rows beyond that point. I have tried 3 different tables, each with very different row sizes. Each time the failure occurs after yielding 8192 rows. Here is the error I see:

An unhandled exception occurred while processing the request.
InvalidOperationException: 'AsyncEnumerableReader' reached the configured maximum size of the buffer when enumerating a value of type 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]'. This limit is in place to prevent infinite streams of 'IAsyncEnumerable<>' from continuing indefinitely. If this is not a programming mistake, consider ways to reduce the collection size, or consider manually converting 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]' into a list rather than increasing the limit.
Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadTestDatabase<T>(IAsyncEnumerable<object> value)

Stack Query Cookies Headers Routing
InvalidOperationException: 'AsyncEnumerableReader' reached the configured maximum size of the buffer when enumerating a value of type 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]'. This limit is in place to prevent infinite streams of 'IAsyncEnumerable<>' from continuing indefinitely. If this is not a programming mistake, consider ways to reduce the collection size, or consider manually converting 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]' into a list rather than increasing the limit.
Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadTestDatabase<T>(IAsyncEnumerable<object> value)
Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadTestDatabase<T>(IAsyncEnumerable<object> value)
Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, IAsyncEnumerable<object> asyncEnumerable)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Show raw exception details
System.InvalidOperationException: 'AsyncEnumerableReader' reached the configured maximum size of the buffer when enumerating a value of type 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]'. This limit is in place to prevent infinite streams of 'IAsyncEnumerable<>' from continuing indefinitely. If this is not a programming mistake, consider ways to reduce the collection size, or consider manually converting 'Microsoft.EntityFrameworkCore.TestDatabase.TestDatabaseDbSet`1[TestNamespace.TestClass]' into a list rather than increasing the limit.
   at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadTestDatabase[T](IAsyncEnumerable`1 value)
   at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadTestDatabase[T](IAsyncEnumerable`1 value)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, IAsyncEnumerable`1 asyncEnumerable)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

reached the configured maximum size of the buffer when enumerating a value of type Microsoft.EntityFrameworkCore DbSet  This limit is in place to prevent infinite streams from continuing indefinitely. If this is not a programming mistake, consider ways to reduce the collection size, or consider manually converting Microsoft.EntityFrameworkCore DbSet  into a list rather than increasing the limit.

Microsoft.EntityFrameworkCore.DbSet AsyncEnumerable

Answer 1

What's happening

The limit comes from MvcOptions.MaxIAsyncEnumerableBufferLimit Property :

Gets or sets the most number of entries of an IAsyncEnumerable that that ObjectResultExecutor will buffer.

When Value is an instance of IAsyncEnumerable, ObjectResultExecutor will eagerly read the enumeration and add to a synchronous collection prior to invoking the selected formatter. This property determines the most number of entries that the executor is allowed to buffer.

(...)

Defaults to 8192.

Fix

From best to worst, in my opinion.

A. (Preferred) Don't return so many records

I'd recommend not to return so many records from a web api.

Paging is one way to deal with large number of records. You could check out Tutorial: Add sorting, filtering, and paging - ASP.NET MVC with EF Core.

B. Adjust the limit

MvcOptions.MaxIAsyncEnumerableBufferLimit can be set to another value.

C. Don't use IAsyncEnumerable

You could return IEnumerable - it will be able to handle the higher number of records.

Answer 2

In ASP.NET Core 5 indeed instances of the type IAsyncEnumerable have been processed by buffering the sequence in memory and formatting the buffered collection all at once. This explains, why you see that exception.

However, with ASP.NET Core 6.0 this will not happen anymore!

In ASP.NET Core 6, when formatting using System.Text.Json, MVC no longer buffers IAsyncEnumerable instances. Instead, MVC relies on the support that System.Text.Json added for these types ([reference][1])

See Clarification on how IAsyncEnumerable works with ASP.NET Web API for more details on how to use IAsyncEnumerable.