ToListAsync is fast again in Entity Framework (kind of)

Last year I wrote an article about how ToListAsync was slow in Entity Framework titled: "Be careful with ToListAsync and ToArrayAsync in Entity Framework Core". Things have evolved since then, so let's celebrate!

The problem

Here a very short recap of the issue: If you are fetching a very large NVARCHAR(MAX) string via await ToListAsync(), Entity Framework will do some crazy stuff leading to a slow query and large amounts of allocation. No when I say Entity Framework then I am lying because it is the underlying Microsoft.Data.SqlClient that is doing the heavy lifting. The problem is that it is not using the SqlDataReader "correctly", which leads to a lot of allocations and a slow query.

Update: To clarify, this concerns mainly the SqlServer package / implementation as it uses the Microsoft.Data.SqlClient package. To know if you are impacted, check the dependency graph for exactly that package.

Here a benchmark taken from @Wraith2:

BenchmarkDotNet v0.14.0, Windows 11 (10.0.26100.2314)
.NET SDK 9.0.200
  [Host]     : .NET 8.0.13 (8.0.1325.6609), X64 RyuJIT
  DefaultJob : .NET 8.0.13 (8.0.1325.6609), X64 RyuJIT


| Method | UseContinue | Mean      | Error     | StdDev    | Gen0      | Gen1      | Gen2      | Allocated |
|------- |------------ |----------:|----------:|----------:|----------:|----------:|----------:|----------:|
| Async  | False       | 757.51 ms | 15.053 ms | 36.642 ms | 2000.0000 | 1000.0000 |         - | 101.49 MB |
| Sync   | False       |  39.40 ms |  0.543 ms |  0.508 ms | 2000.0000 |  888.8889 |  777.7778 |  80.14 MB |

^{Source: https://github.com/dotnet/SqlClient/pull/3161}

Microsoft.Data.SqlClient 6.1.0

@Wraith2 has fixed the issue in a series of pull requests, that spans over 5 years!!! That is amazing and crazy. Of course this isn't 5 years straight, but very remarkable nonetheless.

The last release notes can be read here: https://github.com/dotnet/SqlClient/blob/main/release-notes/6.1/6.1.0-preview1.md

• Here you can check out the "Added packet multiplexing support to improve large data read performance" stuff.

And the pull request that fixes the issue is here (well technically the last one that minimizes the gap by a lot): https://github.com/dotnet/SqlClient/pull/2714

The results:

| Async  | True        |  49.45 ms |  0.901 ms |  1.376 ms | 4333.3333 | 3555.5556 | 1111.1111 | 101.51 MB |
| Sync   | True        |  40.09 ms |  0.476 ms |  0.445 ms | 2000.0000 |  888.8889 |  777.7778 |  80.14 MB |

Still there is the 20% gap in allocations, but we are closing the gap! Really amazing work by Wraith2 and the team. You can test the package even if you are using EF, as direct dependencies override transient dependencies. Well or you wait for the next EF release that includes the new Microsoft.Data.SqlClient version.

The final (as in stable) 6.1 should be out soon!!