[HxGrid] Handling Cancelled EF Queries

[TPIvan]

I'm encountering an exception when using HxGrid and cancelling an ongoing EF query due to page changes.

Problem:

The code snippet below demonstrates how I provide data for HxGrid:

    return  new GridDataProviderResult<TItem>
        {
            Data = await dt.ApplyGridDataProviderRequest(request).ToListAsync(request.CancellationToken),
            TotalCount = await dt.CountAsync(request.CancellationToken)
        };

The issue arises when the user navigates away from the page before the query finishes. This triggers a Microsoft.Data.SqlClient.SqlException with the message "Operation cancelled by user."

The exception propagates to the highest level and ultimately disrupts the navigation, preventing the user from reaching the desired page. (Full stack trace will be provided later.)

Solution (workaround?):

To address this, I implemented error handling using a try-catch block:

    try
    {
        return  new GridDataProviderResult<TItem>
        {
            Data = await dt.ApplyGridDataProviderRequest(request).ToListAsync(request.CancellationToken),
            TotalCount = await dt.CountAsync(request.CancellationToken)
        };
    }
    catch when (request.CancellationToken.IsCancellationRequested)
    {         
        request.CancellationToken.ThrowIfCancellationRequested();
        return new(); //this will never happen
    }

Proposed Improvement:

However, I believe the ideal solution would be to handle cancelled queries within the HxGrid component itself, specifically in the InvokeDataProviderInternal method

This approach would centralize the handling and potentially offer a more integrated user experience.

Full exception:

      Microsoft.Data.SqlClient.SqlException (0x80131904): A severe error occurred on the current command.  The results, if any, should be discarded.
      Operation cancelled by user.
         at Task<DbDataReader> Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)+(Task<SqlDataReader> result) => { }
         at void System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
         at async Task<RelationalDataReader> Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) x 2
         at async Task<bool> Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at async Task<TResult> Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
         at async ValueTask<bool> Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
         at async Task<List<TSource>> Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken) x 2
         at async Task<GridDataProviderResult<TItem>> 

     The presented code is here 

         at async Task<GridDataProviderResult<TItem>> Havit.Blazor.Components.Web.Bootstrap.HxGrid<TItem>.InvokeDataProviderInternal(GridDataProviderRequest<TItem> request)
         at async ValueTask Havit.Blazor.Components.Web.Bootstrap.HxGrid<TItem>.RefreshPaginationOrLoadMoreDataCoreAsync(bool forceReloadAllPaginationOrLoadMoreData)
         at async Task Havit.Blazor.Components.Web.Bootstrap.HxGrid<TItem>.RefreshDataCoreAsync()
         at async Task Havit.Blazor.Components.Web.Bootstrap.HxGrid<TItem>.OnAfterRenderAsync(bool firstRender)
         at async Task Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

[hakenr]

Unfortunately, this is a known issue with SqlClient. See dotnet/SqlClient#26.
We don't feel this is something we should resolve in HxGrid (where we expect OperationCancelledException to be thrown).
The workaround you proposed will most likely do the job.

[TPIvan]

Thank you for the information. I'll keep using my workaround and wait for Microsoft to correct the problem. I believe it will be soon. :-D

[hakenr]

The issue was tracked in 2016, so "soon" is quite relative in this case. :-D