Does FirstOrDefaultAsync Pull The Entire ResultSet Into Memory?

[Mike-E-angelo]

Hello,

I am making use of FirstOrDefaultAsync on EFCore queries and I was noticing that one of the calls I am using this on is taking 16 seconds to complete. Upon closer inspection, the query generated by EFCore takes about 16 seconds to complete without a TOP(1) (or other filter) applied to it.

This has me worried. I poked into the code for FirstOrDefaultAsync and it seems that only the first row is pulled from the IAsyncEnumerable but I wanted to be sure this is operating as expected.

Can someone please confirm my understanding that only one row is pulled from the database when this call is made, and not the entire dataset?

Thank you for any assistance you can provide.

[glopesdev]

@Mike-E-angelo In older versions of Rx I did observe a difference between First() and Take(1) for cold observables that evaluate their results on subscription.

Not sure if this is still there in the newer versions, but you might want to try adding Take(1).FirstOrDefault() to test if there are speedups.

[Mike-E-angelo]

Thank you for that suggestion @glopesdev. I thought for sure that might address it but unfortunately I am still getting consistent 16-second execution times when using .Take(1).FirstOrderDefault().

For clarity, I am using the extension method on IAsyncEnumerable<T>:
https://github.com/DragonSpark/Framework/blob/15f3009f59a9548e60a21612fe3c197165233a86/DragonSpark.Application/Entities/Queries/Compiled/Evaluation/ToFirstOrDefault.cs

Are you aware by chance of any way to see actual data being pulled into a .NET process from a Sql command?

[glopesdev]

@Mike-E-angelo ah, apologies for the confusion. In the case of Sql queries the most common source of eager pulling of data comes from transitioning from IQueryiable to IEnumerable too soon. If Take(1) is applied to a queryiable it should cap the stream at the level of the SQL cursor itself.

I am not familiar with the IAsyncEnumerable API however, so this may be of limited help.

[Mike-E-angelo]

Thank you for the discussion @glopesdev it is appreciated. FWIW I was able to reproduce the problem without using System.Linq.Async at all. This appears to be an issue with EFCore and I have created an issue to track: dotnet/efcore#33338

I appreciate you taking the time to help get me oriented.