[HxGrid] - Using IQueryable + Formating String? Leads to Error

[hakenr]

Discussed in https://github.com/havit/Havit.Blazor/discussions/865

^{Originally posted by **StarGazer202424** August 9, 2024} Hi I took the example that was on the demo page https://havit.blazor.eu/components/HxGrid#IQueryable and I changed it to EF Core instead of the memory DTO that the example uses, but I run into this ``` System.InvalidOperationException: The LINQ expression 'DbSet() .OrderBy(e => (IComparable)e.Salary)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at BlazorApp17.Components.Pages.Counter.GetGridData(GridDataProviderRequest`1 request) in BlazorApp17\BlazorApp17\Components\Pages\Counter.razor:line 25 at Havit.Blazor.Components.Web.Bootstrap.HxGrid`1.InvokeDataProviderInternal(GridDataProviderRequest`1 request) at Havit.Blazor.Components.Web.Bootstrap.HxGrid`1.RefreshPaginationOrLoadMoreDataCoreAsync(Boolean forceReloadAllPaginationOrLoadMoreData) at Havit.Blazor.Components.Web.Bootstrap.HxGrid`1.RefreshDataCoreAsync() at Havit.Blazor.Components.Web.Bootstrap.HxGrid`1.HandleSortingClick(IHxGridColumn`1 newSortColumn) at Havit.Blazor.Components.Web.Bootstrap.HxGrid`1.<>c__DisplayClass262_2.<b__4>d.MoveNext() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task) at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState) ``` ``` @code { private async Task> GetGridData(GridDataProviderRequest request) { var query = DemoDataService.GetEmployeesAsQueryable(); // this can be dbContext.Employees or whatever IQueryable<> you have await Task.Delay(100); // simulate server delay in demo (do not put this in your code) return new GridDataProviderResult() { Data = query.ApplyGridDataProviderRequest(request).ToList(), // use ToListAsync(request.CancellationToken) for EF core async data loading TotalCount = query.Count() // use CountAsync(request.CancellationToken) for EF core async data loading }; } } ``` ``` public IQueryable GetEmployeesAsQueryable() { return _context.Employees.AsQueryable(); } ```

Confirmed by @jirikanda

[hakenr]

@jirikanda commented in #865:

HxGridColumn has a property SortKeySelector of type Expression<Func<TItem, IComparable>>. When you assign a value, ie. employee => employee.Name, it is converted to employee => (IComparable)employee.Name. But this expression cannot be translated to a EF Core query (I have already solved this elsewhere) as mentioned in the exception above.

As a solution for EF Core the Convert part of expression must be removed. It can be done in the ApplyGridDataProviderRequest method or in the HxGridColumn. I am not sure what fits you best.

The change in ApplyGridDataProviderRequest works for sure and I am not sure if there can be some side effect when you change the HxGridColumn.

The solution for the ApplyGridDataProviderRequest method can be done by adding a local function RemoveConvertFromSortKeySelector with the usage below.

  public static IQueryable<TItem> ApplyGridDataProviderRequest<TItem>(this IQueryable<TItem> source, GridDataProviderRequest<TItem> gridDataProviderRequest)
  {
      Expression<Func<TItem, IComparable>> RemoveConvertFromSortKeySelector(Expression<Func<TItem, IComparable>> sortKeySelectorExpressionLambda) => Expression.Lambda<Func<TItem, IComparable>>(sortKeySelectorExpressionLambda.Body.RemoveConvert(), sortKeySelectorExpressionLambda.Parameters[0]);

      gridDataProviderRequest.CancellationToken.ThrowIfCancellationRequested();

      // Sorting
      if ((gridDataProviderRequest.Sorting != null) && gridDataProviderRequest.Sorting.Any())
      {
          Contract.Assert(gridDataProviderRequest.Sorting.All(item => item.SortKeySelector != null), $"All sorting items must have the {nameof(SortingItem<TItem>.SortKeySelector)} property set.");

          IOrderedQueryable<TItem> orderedDataProvider = (gridDataProviderRequest.Sorting[0].SortDirection == SortDirection.Ascending)
              ? source.OrderBy(RemoveConvertFromSortKeySelector(gridDataProviderRequest.Sorting[0].SortKeySelector))
              : source.OrderByDescending(RemoveConvertFromSortKeySelector(gridDataProviderRequest.Sorting[0].SortKeySelector));

          for (int i = 1; i < gridDataProviderRequest.Sorting.Count; i++)
          {
              orderedDataProvider = (gridDataProviderRequest.Sorting[i].SortDirection == SortDirection.Ascending)
                  ? orderedDataProvider.ThenBy(RemoveConvertFromSortKeySelector(gridDataProviderRequest.Sorting[i].SortKeySelector))
                  : orderedDataProvider.ThenByDescending(RemoveConvertFromSortKeySelector(gridDataProviderRequest.Sorting[i].SortKeySelector));
          }
          source = orderedDataProvider;
      }

      // Paging / Infinite scroll
      if (gridDataProviderRequest.StartIndex > 0)
      {
          source = source.Skip(gridDataProviderRequest.StartIndex);
      }
      if (gridDataProviderRequest.Count > 0)
      {
          source = source.Take(gridDataProviderRequest.Count.Value);
      }

      return source;
  }
}