如何使用GetAsyncEnumerator中止正在运行的EF核心查询? [英] How can I abort a running EF Core Query using GetAsyncEnumerator?
问题描述
我正在使用EF Core 5.0，并且具有以下代码:
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;
}
}
它按预期工作，允许我在仍加载更多数据的同时填充数据网格.如果我取消提供的CancellationToken，则首先在预期的MoveNextAsync()行上得到TaskCanceledException.
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.
但是:我可以在SQL事件探查器中看到，SQL查询本身并没有中止，而是一直运行直到加载所有数据，然后才在同一行上得到第二个TaskCanceledException.
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.
如何中止查询本身?
我将AsSplitQuery()添加到示例中，因为事实证明这是我所经历的行为的原因(正如Ivan正确猜测的那样).省略了它以使样本更短...
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...
推荐答案
所描述的行为是EF Core实现的特定于某种类型的查询，这些查询内部使用了所谓的完全消耗了基础数据读取器并缓冲结果，因此可以更早地发布基础数据读取器.
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.
很难确切地说按设计"使用哪种类型的查询，但是禁用/不支持多个活动结果集(MARS).
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.
为什么?拆分查询将执行一个以上的数据库查询，并合并其结果，就好像它们是单个查询一样.整合需要同时激活一个以上的数据读取器.如果基础数据库提供者不支持或禁用MARS(默认情况下被禁用)，则在存在活动读取器的情况下尝试执行第二个读取器会导致运行时异常.因此，要解决该问题，EF Core必须消耗并缓冲活动的读取器，并在执行下一个读取器之前将其释放(关闭/处置).
Why? Split queries execute more that one database query and consolidate their results as if they are single query. The consolidation requires having more that 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.
由于要使此功能正常运行，需要使用此方法，因此没有外部方法可以对其进行控制.除非数据库提供程序支持MARS(例如SqlServer支持)，在这种情况下，您可以
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.
这篇关于如何使用GetAsyncEnumerator中止正在运行的EF核心查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
