---
title: "Be careful with ToListAsync and ToArrayAsync in Entity Framework Core"
description: "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!"
updated: 2024-11-04
tags: ["EF", "Performance"]
canonical: "https://steven-giesel.com/blogPost/70cebcf5-d171-4852-ae73-4950accfe999/be-careful-with-tolistasync-and-toarrayasync-in-entity-framework-core"
---

# Be careful with ToListAsync and ToArrayAsync in Entity Framework Core

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:
```csharp
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](https://stackoverflow.com/questions/28543293/entity-framework-async-operation-takes-ten-times-as-long-to-complete/28619983).

If you want to follow this in more depth: [Reading large data (binary, text) asynchronously is extremely slow ](https://github.com/dotnet/SqlClient/issues/593).

## The fix
The fix is fairly simple: Just use the synchronous version:
```csharp
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:
```csharp
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?
```no-class
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:
```no-class
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](https://bsky.app/profile/ryanmendoza.bsky.social) for the discussion on [BlueSky](https://bsky.app/profile/steven-giesel.com)<sup>This link will lead you to my page where you can follow me</sup>.
## 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!