[Blazor] Improve the experience with QuickGrid and EF Core

[javiercn]

Getting QuickGrid to work correctly with EF is hard and requires using the ItemsProvider interface in order to avoid issues with concurrent queries that might be triggered with any action defined on the form.

Switching to ItemsProvider is not trivial (had to look up the implementation from our QuickGrid implementation) and could be simplified with a few additional methods on the request, to apply Skip and Take automatically or a more general ApplyRequestParameters method that applies all of them.

The implementation with EF should be simpler since this is a main scenario.

@pilarodriguez what's happening here is that the code in Items is triggering multiple concurrent queries, as every time we render it triggers a new query.

The following change will make it work as expected.

--- a/QuickGridTestProj/Components/Pages/ResourceOverview.razor
+++ b/QuickGridTestProj/Components/Pages/ResourceOverview.razor
@@ -27,7 +27,7 @@
 <div class="pt-4">

      <h3>Resources overview</h3>
-     <QuickGrid Class="mt-2" Items="_dbContext.Resources.OrderBy(x => x.ResourceId)" ItemKey="(x => x.ResourceId)" Pagination="Pagination">
+    <QuickGrid TGridItem="QuickGridTest.Data.Entities.Resource" Class="mt-2" ItemsProvider="GetItems" ItemKey="(x => x.ResourceId)" Pagination="Pagination">
          <PropertyColumn Property="@(p => p.ResourceName)" Title="Name" Sortable="true" />
          <PropertyColumn Property="@(p => p.ResourceType)" Sortable="true" />
          <TemplateColumn>
diff --git a/QuickGridTestProj/Components/Pages/ResourceOverview.razor.cs b/QuickGridTestProj/Components/Pages/ResourceOverview.razor.cs
index 0b00352..af80195 100644
--- a/QuickGridTestProj/Components/Pages/ResourceOverview.razor.cs
+++ b/QuickGridTestProj/Components/Pages/ResourceOverview.razor.cs
@@ -23,6 +23,21 @@ namespace QuickGridTestProj.Components.Pages
             _dbContext = _dbContextFactory.CreateDbContext();
         }

+        public async ValueTask<GridItemsProviderResult<Resource>> GetItems(
+            GridItemsProviderRequest<Resource> request)
+        {

+            using var context = _dbContextFactory.CreateDbContext();
+            var totalCount = await context.Resources.CountAsync(request.CancellationToken);
+            IQueryable<Resource> query = context.Resources.OrderBy(x => x.ResourceId);
+            query = request.ApplySorting(query).Skip(request.StartIndex);
+            if (request.Count.HasValue)
+            {
+                query = query.Take(request.Count.Value);
+            }

+            var items = await query.ToArrayAsync(request.CancellationToken);
+            var result = new GridItemsProviderResult<Resource>
+            {
+                Items = items,
+                TotalItemCount = totalCount
+            };

+            return result;
+        }
+
         private async Task OnAddResourceSubmit()
         {
             using var dbContext = _dbContextFactory.CreateDbContext();

Originally posted by @javiercn in #58669

[MagistratasHK]

I'm working with the BlazorWebAppMovies sample, so my observation/recommendation stems from that and @guardrex has suggested I should put it here.

The proposed implementation is better, but it still throws an exception, two in fact. In the sample, if you type fast enough or if server-side updates come in quick succession, following exceptions might be thrown: System.InvalidOperationException and System.Threading.Tasks.TaskCanceledException.

The more frequent exception is thrown by the EF Core itself. As I understand from the EF ExecuteAsync documentation:

A cancellation token used to cancel the retry operation, but not operations that are already in flight or that already completed successfully.

A task that will run to completion if the original task completes successfully (either the first time or after retrying transient failures). If the task fails with a non-transient error or the retry limit is reached, the returned task will become faulted and the exception must be observed.

The less frequent exception is thrown by the SQL provider. It is Micrsofot.Data.SqlClient.SqlException wrapped in System.InvalidOperationException (0x80131904): The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
Operation cancelled by user..

I suggest following changes to the BlazorWebAppMovies sample and perhaps in here:

• Add missing filtering (BlazorWebAppMovies specific).
• Add exception handling/suppression when CancellationToken.IsCancellationRequested == true

public async ValueTask<GridItemsProviderResult<Movie>> GetMovies(GridItemsProviderRequest<Movie> request)
{
	using var context = DbFactory.CreateDbContext();
	Movie[] items = new Movie[0];
	int totalCount = 0;

	IQueryable<Movie> query = context.Movie.Where(m => m.Title!.Contains(titleFilter));

	try
	{
		totalCount = await query.CountAsync(request.CancellationToken);

		query = query.OrderBy(x => x.Id);
		query = request.ApplySorting(query).Skip(request.StartIndex);

		if (request.Count.HasValue)
		{
			query = query.Take(request.Count.Value);
		}

		items = await query.ToArrayAsync(request.CancellationToken);
	}
	catch (Exception ex) when (ex is System.InvalidOperationException || ex is System.Threading.Tasks.TaskCanceledException)
	{
		if (request.CancellationToken.IsCancellationRequested)
			Logger.LogError(ex, "Thread cancelled, CancellationToken.IsCancellationRequested = " + request.CancellationToken.IsCancellationRequested);
		else
			throw;
	}

	var result = new GridItemsProviderResult<Movie>
		{
			Items = items,
			TotalItemCount = totalCount
		};

	return result;
}

Exceptions that need handling:

fail: BlazorWebAppMovies.Components.Pages.MoviePages.Index[0]
      Thread cancelled, CancellationToken.IsCancellationRequested = True
      System.Threading.Tasks.TaskCanceledException: A task was canceled.
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at BlazorWebAppMovies.Components.Pages.MoviePages.Index.GetMovies(GridItemsProviderRequest`1 request) in D:\Projects\blazor-samples\8.0\BlazorWebAppMovies\Components\Pages\MoviePages\Index.razor:line 76
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (55ms) [Parameters=[@__titleFilter_0_contains='?' (Size = 60)], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM [Movie] AS [m]
      WHERE [m].[Title] LIKE @__titleFilter_0_contains ESCAPE N'\'
info: BlazorWebAppMovies.Components.Pages.MoviePages.Index[0]
      CancellationToken.IsCancellationRequested = False
fail: BlazorWebAppMovies.Components.Pages.MoviePages.Index[0]
      Thread cancelled, CancellationToken.IsCancellationRequested = True
      System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
      Operation cancelled by user.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
      ClientConnectionId:8f760eb5-ebe0-422d-99cd-e04439f6269d
      Error Number:3980,State:1,Class:16
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at BlazorWebAppMovies.Components.Pages.MoviePages.Index.GetMovies(GridItemsProviderRequest`1 request) in D:\Projects\blazor-samples\8.0\BlazorWebAppMovies\Components\Pages\MoviePages\Index.razor:line 76```