FirstOrDefaultAsync performance issue when the db entity contains a very large BLOB

[NazarieCiprian]

When using FirstOrDefaultAsync there is a considerable performance drop if the entity that is loaded contains very large strings.
In our case the entity which we want to read from database contains a column of type NVARCHAR(max) which stores an xml formatted string. If the size of the xml goes above 10MB the reading time is about 1-2 even 3 minutes.

If the FirstOrDefault method is used the reading times is of a few seconds up to 1 minute.

This issue reproduces only for MSSQL database. We are also using Oracle and PSQL, but for these databases both the synchronous and asynchronous method work as expected.

 public async Task<Test> Get(int id)
{
     return await _context.Tests.FirstOrDefaultAsync(s => s.Id == id);
}

EF Core version: EFCore 7
Database provider: Microsoft.EntityFrameworkCore.SqlServer 7.0.9
Target framework: .NET 7.0
Operating system: Windows
IDE: Visual Studio 2022 17.6.5

[roji]

Duplicate of dotnet/SqlClient#593

[roji]

Assuming the synchronous variant (FirstOrDefault()) is fast, then this is the above issue - it's a well-known SqlClient performance issue with async that EF can't do anything about.