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

[StarGazer202424]

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<Employee>()
    .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.<Execute>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.<<BuildRenderTree>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)

<HxGrid PageSize="5" TItem="Employee" Responsive="true" DataProvider="GetGridData">
	<Columns>
		<HxGridColumn HeaderText="Name" ItemTextSelector="employee => employee.Name" SortKeySelector="employee => employee.Name" IsDefaultSortColumn="true" />
		<HxGridColumn HeaderText="Phone" ItemTextSelector="employee => employee.Phone" SortKeySelector="employee => employee.Phone" />
		<HxGridColumn HeaderText="Salary" ItemTextSelector="@(employee => employee.Salary.ToString("c0"))" SortKeySelector="employee => employee.Salary" />
		<HxGridColumn HeaderText="Position" ItemTextSelector="employee => employee.Position" SortKeySelector="employee => employee.Position" />
		<HxGridColumn HeaderText="Location" ItemTextSelector="employee => employee.Location" SortKeySelector="employee => employee.Location" />
	</Columns>
</HxGrid>

@code {
	private async Task<GridDataProviderResult<Employee>> GetGridData(GridDataProviderRequest<Employee> 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<Employee>()
			{
				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<Employee> GetEmployeesAsQueryable()
	{
		return _context.Employees.AsQueryable();

	}

[hakenr]

@jirikanda, please take a look at this.

[jirikanda]

Confirmed.

[jirikanda]

@hakenr,

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;
	}
}

[hakenr]

Continues as issue #883.