Grid - Filter and Order int to string

[Lorenzo]

Hello everyone, while trying to implement a grid with filtering and sorting, I came across an issue that seems to be recurring. In the database, I have columns (IdLotto and IdPratica) that are numeric.
From what I’ve read, filtering and sorting of numbers are not supported, only strings are. So I tried searching and implemented my own partial solution:

var avrs = _avrService.GetFiltered().Select(a =>
            new GetAllAvr()
            {
                IdLotto = a.IdLotto.ToString(),
                IdPratica = a.IdPratica.ToString()               
            });

var dsResult = avrs.ToDataSourceResult(request);

GetFiltered returns IQueryable of db's entity.

Partial because filtering works, but sorting doesn’t. Since string sorting is different from numeric sorting, the result is actually an incorrect order.

I wonder if I'm doing something wrong, if I need extra support from jQuery, or if I need to fully customize filtering and sorting in the controller method.

Thanks for the help

[Eyup]

Hello Lorenzo,

 

Thank you for writing to us.

I am happy to let you know that numeric field operations provided by the Grid work automatically both for Filtering and Sorting:
https://demos.telerik.com/aspnet-mvc/grid/filter-row

If this is not the case for you, can you provide additional details about your project?

 

Regards,
Eyup
Progress Telerik

[Lorenzo]

Hi Eyup, maybe I wasn’t clear enough. What I want to achieve is a 'contains'.
In the database, the fields are numeric, and 'contains' is not supported. So by converting them to strings via a select (EF CORE and LINQ), I can apply the filter. But the sorting is incorrect because string sorting is different from numeric sorting.

What I want to achieve is both sorting and filtering (using 'contains' is fine) for a field that is numeric in the database.
How can I do this?

[Eyup]

Hi Lorenzo,

Thank you for the clarification.

If I understand correctly, you want to be able to filter this given column as string values (contains), but at the same time - the sorting should be numeric. For example, the value "9" should appear before "19".

You can achieve this requirement with several different approaches (you need to set the .ServerOperation(false) property in the DataSource instance of the Grid):

1. Custom Sorting compare function:

The grid provides the capability to define custom sorting logic function to compare values:
https://docs.telerik.com/aspnet-core/api/kendo.mvc.ui.fluent/gridboundcolumngroupablesortbuilder#comparesystemstring

My recommendation for this case is that you keep the field of type string so the filtering works with "contains" operator. And then define the .Compare() function and compare the values by parsing them to numbers.

2. Custom filter function.

By default, the Grid has the following filter operators:
https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/filter#filteroperator

But it is also possible to use custom filtering logic:
https://www.telerik.com/forums/how-to-define-a-custom-filter-operator

This approach will be more complex.

3. There is also a third option - using two different columns, similar to the implementation demonstrated in the attached sample.

Do you find this research beneficial? Let me know which option you like the most.

 

[Lorenzo]

ServerOperation(false)
Does this mean that pagination and filtering are not server-side? Unfortunately, I need everything to be server-side

[Eyup]

Yes, this is only available when filtering and sorting happens on the client.

If you really have to use server-side operations, then you can use this approach instead, but it will significantly complicated the logic:
https://demos.telerik.com/aspnet-core/grid/customajaxbinding