ToAsyncEnumerable().Single() vs SingleAsync()

Question

I'm constructing and executing my queries in a way that's independent of EF-Core, so I'm relying on IQueryable<T> to obtain the required level of abstraction. I'm replacing awaited SingleAsync() calls with awaited ToAsyncEnumerable().Single() calls. I'm also replacing ToListAsync() calls with ToAsyncEnumerable().ToList() calls. But I just happened upon the ToAsyncEnumerable() method so I'm unsure I'm using it correctly or not.

To clarify which extension methods I'm referring to, they're defined as follows:

• SingleAsync and ToListAsync are defined on the EntityFrameworkQueryableExtensions class in the Microsoft.EntityFrameworkCore namespace and assembly.
• ToAsyncEnumerable is defined on the AsyncEnumerable class in the System.Linq namespace in the System.Interactive.Async assembly.

When the query runs against EF-Core, are the calls ToAsyncEnumerable().Single()/ToList() versus SingleAsync()/ToListAsync() equivalent in function and performance? If not then how do they differ?

Answer 1

For methods returning sequence (like ToListAsync, ToArrayAsync) I don't expect a difference.

However for single value returning methods (the async versions of First, FirstOrDefault, Single, Min, Max, Sum etc.) definitely there will be a difference. It's the same as the difference by executing those methods on IQueryable<T> vs IEnumerable<T>. In the former case they are processed by database query returning a single value to the client while in the later the whole result set will be returned to the client and processed in memory.

So, while in general the idea of abstracting EF Core is good, it will cause performance issues with IQueryable<T> because the async processing of queryables is not standartized, and converting to IEnumerable<T> changes the execution context, hence the implementation of single value returning LINQ methods.

P.S. By standardization I mean the following. The synchronous processing of IQueryable is provided by IQueryProvider (standard interface from System.Linq namespace in System.Core.dll assembly) Execute methods. Asynchronous processing would require introducing another standard interface similar to EF Core custom IAsyncQueryProvider (inside Microsoft.EntityFrameworkCore.Query.Internal namespace in Microsoft.EntityFrameworkCore.dll assembly). Which I guess requires cooperation/approval from the BCL team and takes time, that's why they decided to take a custom path for now.

Answer 2

When the original source is a DbSet, ToAsyncEnumerable().Single() is not as performant as SingleAsync() in the exceptional case where the database contains more than one matching row. But in in the more likely scenario, where you both expect and receive only one row, it's the same. Compare the generated SQL:

SingleAsync():
    SELECT TOP(2) [l].[ID]
    FROM [Ls] AS [l]

ToAsyncEnumerable().Single():
    SELECT [l].[ID]
    FROM [Ls] AS [l]

ToAsyncEnumerable() breaks the IQueryable call chain and enters LINQ-to-Objects land. Any downstream filtering occurs in memory. You can mitigate this problem by doing your filtering upstream. So instead of:

ToAsyncEnumerable().Single( l => l.Something == "foo" ):
    SELECT [l].[ID], [l].[Something]
    FROM [Ls] AS [l]

you can do:

Where( l => l.Something == "foo" ).ToAsyncEnumerable().Single():
    SELECT [l].[ID], [l].[Something]
    FROM [Ls] AS [l]
    WHERE [l].[Something] = N'foo'

If that approach still leaves you squirmish then, as an alternative, consider defining extension methods like this one:

using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Internal;

static class Extensions
{
    public static Task<T> SingleAsync<T>( this IQueryable<T> source ) =>
        source.Provider is IAsyncQueryProvider
            ? EntityFrameworkQueryableExtensions.SingleAsync( source )
            : Task.FromResult( source.Single() );
}