Entity Framework has two methods for converting a query to a list or an array: ToListAsync and ToArrayAsync. Of course there is also the sync version of these methods: ToList and ToArray. And sometimes, you should use the latter one!
The bug#
In one of my projects, I have the following code:
public static async Task<IPagedList<T>> ToPagedListAsync<T>(this IQueryable<T> source, int page, int pageSize, CancellationToken token = default)
{
var count = await source.CountAsync(token);
if (count > 0)
{
var items = await source
.Skip((page - 1) * pageSize)
.Take(pageSize)
.ToListAsync(token);
return new PagedList<T>(items, count, page, pageSize);
}
return PagedList<T>.Empty;
}
That looks fairly trivial - and of course we want ToListAsync here, or do we? Well: Not necessarily! To give a bigger picture, this call is used in that very blog post you are reading right now. And the datamodel of a blog post has the following column: Content NVARCHAR(MAX) NOT NULL. NVARCHAR(MAX) (as well as VARBINARY(MAX) and friends) cause a known issue with Entity Framework since almost 10 years: Performance degradation. The async version spawns a whole lot of tasks, see the stackoverflow article.
If you want to follow this in more depth: Reading large data (binary, text) asynchronously is extremely slow .
The fix#
The fix is fairly simple: Just use the synchronous version:
public static async Task<IPagedList<T>> ToPagedListAsync<T>(this IQueryable<T> source, int page, int pageSize, CancellationToken token = default)
{
var count = await source.CountAsync(token);
if (count > 0)
{
// I tried ToListAsync and it performed just poorly!
// Mainly because we have a VARCHAR(max) column
// See here: https://stackoverflow.com/questions/28543293/entity-framework-async-operation-takes-ten-times-as-long-to-complete/28619983
var items = source
.Skip((page - 1) * pageSize)
.Take(pageSize)
.ToList();
return new PagedList<T>(items, count, page, pageSize);
}
return PagedList<T>.Empty;
}
And yes, adding a comment here is absolutely necessary and okay! Especially because CountAsync is still async and the whole method is marked as async.
Update 10th November#
I made a small sample to demonstrate this behavior. And as you can see, this only happens if you really have large binary columns and many rows. In the given example I retrieve 40000 records where each record has a string of length 40000:
using System.Diagnostics;
using Microsoft.EntityFrameworkCore;
await CreateSeedDataAsync();
Console.WriteLine("Done Writing");
await using (var context = new SomeDataContext())
{
var sw = Stopwatch.StartNew();
var syncData = context.Data.Select(s => s.Content).ToList();
sw.Stop();
Console.WriteLine($"Sync read time: {sw.ElapsedMilliseconds}ms");
Console.WriteLine($"Retrieved {syncData.Count} records");
}
// Read data asynchronously
await using (var context = new SomeDataContext())
{
var sw = Stopwatch.StartNew();
var asyncData = await context.Data.Select(s => s.Content).ToListAsync();
sw.Stop();
Console.WriteLine($"Async read time: {sw.ElapsedMilliseconds}ms");
Console.WriteLine($"Retrieved {asyncData.Count} records");
}
// Read data synchronously
async Task CreateSeedDataAsync()
{
await using var context = new SomeDataContext();
await context.Database.OpenConnectionAsync();
await context.Database.EnsureCreatedAsync();
var data = Enumerable.Range(1, 40000).Select(i => new SomeData
{
Content = new string('X', 40000),
}).ToList();
await context.Data.AddRangeAsync(data);
await context.SaveChangesAsync();
}
public class SomeData
{
public int Id { get; set; }
public required string Content { get; set; }
}
public sealed class SomeDataContext : DbContext
{
public DbSet<SomeData> Data { get; set; }
public SomeDataContext()
{
Database.EnsureCreated();
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("DataSource=file::memory:?cache=shared");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SomeData>().HasKey(x => x.Id);
modelBuilder.Entity<SomeData>()
.Property(x => x.Id)
.ValueGeneratedOnAdd();
modelBuilder.Entity<SomeData>()
.Property(x => x.Content)
.IsRequired();
}
}
The result?
Sync read time: 2497ms
Retrieved 40000 records
Async read time: 3215ms
Retrieved 40000 records
So while there is a difference of roughly 700ms for this example - you have to decide for yourself if this is a lot (because with the sync version there is no cancellation support anymore).
But what happens if we upgrade to Entity Framework 9.0.0-rc.2? Well the results will look like this:
Sync read time: 1750ms
Retrieved 40000 records
Async read time: 2223ms
Retrieved 40000 records
There is just so much of an overhaul improvement! It is incredible! But still the async version is still slower. Thanks to @ryanmendoza for the discussion on BlueSkyThis link will lead you to my page where you can follow me.
What is the takeaway?#
Well, check if you are handling large binary data via ToListAsync in your selector? If so, maybe you are suffering from the issue!
