Understanding EFCore's `IAsyncEnumerable`

[Mike-E-angelo]

Ask a question

I am currently experiencing an issue with calling EF.CompileAsyncQuery(Expression<Func<DbContext, IQueryable<TOut>>>) and iterating through the IAsyncEnumerable<T> that is returned from it.

I am using "Microsoft.EntityFrameworkCore": "Information" to retrieve the query being sent to SQL Server.

In this case, the query I am using returns over 1500 rows, which, if I take the query emitted to the console and paste it into SSMS, takes about 16 seconds to process it.

The problem I am experiencing is that if I run the following by passing in the IAsyncEnumerable created from EF.CompileAsyncQuery to the Get method below:

sealed class ToFirstOrDefault<T> : IEvaluate<T, T?>
{
	public static ToFirstOrDefault<T> Default { get; } = new();

	ToFirstOrDefault() {}

	public async ValueTask<T?> Get(IAsyncEnumerable<T> parameter)
	{
		await foreach (var item in parameter.ConfigureAwait(false))
		{
			return item;
		}

		return default;
	}
}

... it also takes 16 seconds to execute, which is not expected. My understanding/expectation that when the EFCore IAsyncEnumerable is enumerated it opens a cursor on the server and processes each row one at a time each time the MoveNextAsync is called.

Is this understanding correct? If this is, I would not expect the above to take 16 seconds to process as there should only be one row being processed.

I would greatly appreciate any pointers to assist me in this area. Thank you for any assistance you can provide.

Include provider and version information

EF Core version: 8.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: (e.g. .NET 8.0)
Operating system: Windows 10
IDE: Visual Studio 2022 17.10 Preview 2

[Mike-E-angelo]

Thank you for any clarification you can provide on this issue.

[cincuranet]

Your assumption is not entirely correct. We do process rows as they arrive. But database engine does not have to (and sometimes even can't, i.e. in case of ORDER BY) return first row right away and keep processing to get more rows. Sometimes it would be even detrimental to performance to send one row only, that's why most protocols have logical and physical buffers (and because network roundtrips can be expensive as well) and rows arrive in multiple.

If you need only first row, it's better to use First[OrDefault][Async], instead of doing that on client. It might not change the overall time, but it might be easier on database.