How to query a large DbSet with AsNoTracking and a CancellationToken

Question

I know a lot of work was done in EF6 to support async operations such as CountAsync, but I can't seem to cancel a simple query. Here's the story.

I have a query that returns 4.5 million rows. I need to process each row, but I can't hold all of them in memory. EF6 is kind enough to let me do this:

foreach (var row in context.TableX.AsNoTracking())
{
...process each row
}

This works great and uses very little memory, but it's not very easy to cancel. I tried this silliness:

foreach (var row in context.TableX.AsNoTracking().ToListAsync(token).Result)
{
...process each row
}

Of course, this tries to loads the entire query into a List<> which crashes long before all the rows are loaded. Thankfully, it is very responsive to a cancellation. :)

The closest I've gotten is to wrap the whole mess like this:

Task.Run(() => DoQuery(), token);

This doesn't chew up memory and I can cancel it, but the cancellation takes forever to respond and there's some nasty exceptions because I'm pulling the rug out.

What am I missing here?

Answer 1

You can do it like this:

public async Task DoQuery(CancellationToken token) {
    await ctx.TableX.AsNoTracking().ForEachAsync(row =>
    {
         // process here
    }, token);
}