EF6 ToListAsync() Cancel Long Query not working

Question

I am working on an Entity Framework WinForms app and am trying to run queries on a thread separate from the UI and allow the user to cancel long queries. I've already asked one question about how to implement this correctly and might still be incorrect there too, but my current question is how do I allow a user to cancel a long-running EF6 query?

I found links along the lines of this, but still can't seem to get this working well... Again, it may be that I programmed the original part wrong (as from my first question), but my question is how do I allow a user to click a cancel button that would stop a long query on the DB?

My (relevant) current code is as follows...

Private cts As New CancellationTokenSource

Private Sub Cancel_Click(sender As Object, e As EventArgs) Handles Cancel.Click
    cts.Cancel()
End Sub

Attempt 1 (add cancellation token in Task):

Private Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim y As New List(Of DBData)

    Try
        Var1 = "Var1"

        Dim qry As Task(Of List(Of DBData)) = Task.Run(Function() GetDBData(Var1), cts.Token))

        Try
            y = Await qry
            cts.Token.ThrowIfCancellationRequested()
        Catch ex As OperationCanceledException
            ' ** ONLY REACH HERE AFTER QUERY PROCESSES!! **
        End Try
End Sub

---

Attempt 2 (add cancellation token in EF ToListAsync() method):

Private Async Function GetDBData(Var1 As String, ct As CancellationToken) As Task(Of List(Of DBData))

    Dim retval As List(Of DBData)

    Using x As New DBContext
        Try
            retval = Await (From rw In x.DBData
                             Where rw.Val1= Val1
                             Select rw).ToListAsync(ct)
            ct.ThrowIfCancellationRequested()

            Return retval

        Catch ex As Exception
            ' ** ONLY REACH HERE AFTER QUERY PROCESSES!! **
            MsgBox(ex.Message)
            Return Nothing
        End Try
    End Using

End Function

---

I hope my explanation / code posted makes sense in the differences between the 2... Any help would be greatly appreciated! - And even though this is in VB, I'm equally comfortable with VB / C# solutions.

Thanks!!

Answer 1

It is a bug that was never fixed by the EF team sadly.

In fact it is an overall weakness in the whole EF 6 Async implementation.

The dev team have done their very best to abstract away any access to the underlying SqlCommand by using internal class InternalContext etc.

What they have failed to do is to connect the Cancel() action of your CancellationToken to the SqlCommand.Cancel. This is a clear bug and very frustrating.

If your query returns rows then their code works because the task will return after an iteration, but this a very poor effort.

internal static Task<List<T>> ToListAsync<T>(this IDbAsyncEnumerable<T> source, CancellationToken cancellationToken)
    {
      TaskCompletionSource<List<T>> tcs = new TaskCompletionSource<List<T>>();
      List<T> list = new List<T>();
      IDbAsyncEnumerableExtensions.ForEachAsync<T>(source, new Action<T>(list.Add), cancellationToken).ContinueWith((Action<Task>) (t =>
      {
        if (t.IsFaulted)
          tcs.TrySetException((IEnumerable<Exception>) t.Exception.InnerExceptions);
        else if (t.IsCanceled)
          tcs.TrySetCanceled();
        else
          tcs.TrySetResult(list);
      }), TaskContinuationOptions.ExecuteSynchronously);
      return tcs.Task;
    }

This could be fixed by adding logic to the IDbAsyncEnumerable to handle cancellation or creating a new interface IDbCancellableAsyncEnumerable, that is, a class that exposes a Cancel method that internally accesses the executing SqlCommand and calls Cancel.

As they haven't bothered with this I suspect this will not be happening any time soon as it would be probably quite a lot of work.

Note: Even if you try to use ExecuteSqlCommandAsync() you still have no way to kill the SqlCommand.