How can I abort a running EF Core Query using GetAsyncEnumerator?

user_2582968 0
Viewed 683

I am using EF Core 5.0 and have the following code:

public async IAsyncEnumerable<Item> GetItems([EnumeratorCancellation] CancellationToken cancellationToken = default)
{
    await using var ctx = _DbContextFunc();
    //Isolationlevel is required to not cause any issues with parallel working on already read items
    await ctx.Database.BeginTransactionAsync(IsolationLevel.ReadUncommitted, cancellationToken).ConfigureAwait(false);
    await foreach (var item in ctx.Item.
        .AsSplitQuery()
        .Include(i => i.ItemDetail1)
        .Include(i => i.ItemDetail2)
        .OrderByDescending(i => i.ItemId)
        .AsNoTracking()
        .AsAsyncEnumerable()
        .WithCancellation(cancellationToken))
    {
        yield return item;
    }
}

It works as expected allowing me to populate a datagrid while more data is still loaded. If I cancel the provided CancelationToken, first I get a TaskCanceledException on the line MoveNextAsync() which is expected.

BUT: I can see in SQL Profiler that the SQL query itself is not aborted but always runs until all data is loaded and only then I get a second TaskCanceledException on that same line.

How do I abort the query itself?

Update

I added the AsSplitQuery() to the sample as it turned out to be the reason for the behavior I experienced (as Ivan rightly guessed). Had left it out to make the sample shorter...

entity-framework-core c#
1 Answers

The described behavior is EF Core implementation specific for some type of queries which internally use the so called buffered data reader - a DbDataReader implementation which initially fully consumes the underlying data reader and buffers the results, so the underlying data reader can be released earlier.

It's hard to say exactly which type of queries use that "by design", but definitely it is used by split queries when Multiple Active Result Sets (MARS) are disabled/not supported.

Why? Split queries execute more than one database query and consolidate their results as if they are single query. The consolidation requires having more than one data reader active at the same time. When MARS are not supported by the underlying database provider, or are disabled (by default), an attempt to execute second reader while there is an active reader leads to runtime exception. Hence to solve that problem, EF Core has to consume and buffer the active reader and release (close/dispose) it before executing the next.

Since this is demanded to make the feature work, there is no external way of controlling it. Except if the database provider supports MARS (for instance, SqlServer does), in which case you can enable it in the connection string by adding

MultipleActiveResultSets=True

and split queries will use the underlying data readers directly, thus will be able to be cancelled earlier without fully consuming them.

user_52025630
Related