ToListAsync() vs AsEnumerable() in terms of synchronicity

Question

Let's say I have the following method:

var result = (await db.Students
                      .Where(s => s.Name == "Foo")
                      .ToListAsync())
                      .Select(s => MySuperSmartMethod(s));

MySuperSmartMethod here is a method that cannot be translated to SQL.

As I understood from this answer, calling the ToList() (and ToListAsync() as well) method to evaluate future methods on the client side has a problem: it immediately queries the database, iterates the result and puts it into memory.

So, it is better to call AsEnumerable() instead of ToList(), cause it does not create an unnecessary intermediate list. AsEnumerable() returns an IEnumerable, so when it comes to executing future methods, they will iterate over objects directly in the IEnumerable.

Therefore I conclude that I should rewrite the previous method to something something like this:

var result = db.Students
               .Where(s => s.Name == "Foo")
               .AsEnumerable()
               .Select(s => MySuperSmartMethod(s));

Ok, but now I have another problem: my method is not asynchronous anymore.

So, what should i do? Are there any other approaches? Does asynchronous quering database lead to any performance benefits in an ASP.NET Core application? Or should I rewrite my asynchronous MediatR queries and commands to synchronous replacing ToListAsync() with AsEnumerable() wherever it's possible?

Answer 1

What you can do is keep it as a IAsyncEnumerable. This means that the query is not actually executed until you enumerate it using ToListAsync

var result = db.Students
               .Where(s => s.Name == "Foo")
               .AsAsyncEnumerable()
               .Select(s => MySuperSmartMethod(s));
// much later
var list = await result.ToListAsync(someCancellationToken);

For this to work, you need to install the System.Linq.Async package.

You can in theory roll your own Select extension, but be aware that simply doing await (foreach will begin the enumeration immediately.

Answer 2

Using an asynchronous method in EF is static "hey, this might take a while, so if anyone else is waiting, you can go ahead". It does nothing (significant or positive) for execution time of the code that is in the queue awaiting it. So depending on whether this code can actually take a while or not it may be no issue at all to leave it synchronous. async isn't a silver bullet for performance, if anything it makes every query that slight bit slower than if they were left synchronous. It's great for stuff that might take a second or more to execute (typically I set a threshold of around 300-500ms) or are called by methods I expect to be executed very frequently.

The best solution IMHO is to avoid situations where you feel you need "SuperSmartMethod" in query expressions. Projection can help with this where "SuperSmartMethod" can be computational within the view model rather than the query, when "SuperSmartMethod" appears in a Select;

For instance if SuperSmartMethod requires values A, B, and C from one or more entities, load A, B, and C into your ViewModel and expose "SuperSmart" property or function in the ViewModel. The query remains pure, and async compatible.

Another solution if you need to keep "SuperSmartMethod" in the query expression is to wrap your querying method within a method using an async IAsyncEnumerable /w yield. Edit Removed this example until I can have a play with it. It doesn't look like that would work out of the box since it would expect something awaited inside the querying method...