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

Question

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...

To know which item to provide in an `OrderByDescending` query, wouldn't it have to query the whole table anyway to provide just one result? — codeMonkey, Dec 15 '20 at 16:39
I mean, returning one at a time using `yield` keyword. I would imagine this would allow you to stop the enumerator from enumerating, but doesn't the enumerator need to perform the whole SQL transaction to yield even one item? — codeMonkey, Dec 15 '20 at 16:47
If nothing else, maybe you could wrap your transaction in `using(var tran = await ctx.Database.BeginTransactionAsync(IsolationLevel.ReadUncommitted, cancellationToken).ConfigureAwait(false)) { ... }` and then when handling cancellation token, you could try to initiate `tran.Rollback()`. I have no idea if that will work, but it's the only thing I can think of at the moment. — codeMonkey, Dec 15 '20 at 16:52
No, it works nicely as a stream and I can see from the profiler that the query is not completed even though the first results are provided (the original query is a bit more complex than what is shown here but that should not be the problem). I'll try the rollback and let you know it that helped — Jan, Dec 15 '20 at 16:55
That doesn't help either. Thanks for trying to help anyway :) — Jan, Dec 15 '20 at 18:07
Looks like the behavior really depends on the type of the query - some queries are using buffering data reader and cannot really be cancelled, while other are using directly the underlying data reader and can be cancelled. Not sure what is the criteria for buffered data reader queries, for sure split queries are the ones which do that. Anyway, I don't see a way to control that currently. — Ivan Stoev, Dec 15 '20 at 19:51
@IvanStoev thank you, thank you, thank you. that explanation was on point for my question. If you like, make it the answer so I can award the points — Jan, Dec 16 '20 at 05:24

score 6 · Accepted Answer · edited Oct 15 '21 at 03:04

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.

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

Update

1 Answers1

Linked