ejGrid possibly failing to handle search toolbar box properly with server side odata paging

[Tony Wright]

Hi there,

I have implemented server-side paging as per the attached project, which I found in another ejgrid thread. I got the paging working fine, but when I switched on the search toolbar item, the search toolbar passed through the filter and caused an exception when attempting to apply the filter.

The exception is: "An exception of type 'Microsoft.Data.OData.ODataException' occurred in Microsoft.Data.OData.dll but was not handled in user code. Additional information: No function signature for the function with name 'tolower' matches the specified arguments. The function signatures considered are: tolower(Edm.String Nullable=true)."

In the example, the EmployeeID field is an integer, and the Name field is a string.

When the odata filter is generated, it shows the following string: "(substringof('1',tolower(EmployeeID))) or (substringof('1',tolower(Name)))"

I believe the EmployeeID causes the exception because it is an Integer field, and there is no tolower or substring in an integer field.

I have tried specifying the type of the column and I have tried to set no filter on the column, but it ignores those attributes and passes the EmployeeId field anyway.

I have added a second grid to the sample application, where it uses all the same data, paging, attributes etc, but I have removed the EmployeeId field. This correctly applies the filter, because the only field is the name field, and it successfully applies the filter to the linq.

I have attached a VS2015 project to this thread.

Is there something that I am doing wrong?

Kind regards,

Tony

Attachment: WebApplication7_afa17ae1.7z

[Ragavee U S]

Hi Tony, 

We have analyzed the reported issue and suspect that the OData could not support searching for integer columns. Hence that issue has been occurred. If the grid contains only string column, then the search operation will work properly. However, we need to prevent the exception while searching the value in integer column and thus considered this to be an uncertain feature request. This feature will be implemented in any of our future releases.  

Regards, 

Ragavee U S. 

[Tony Wright]

Hi there,

I have analysed the paging solution and determined that odata will never be suitable for this task. Firstly, it can't handle performing string searches on non-string primitive types, and secondly, it doesn't return a rowcount over the filter. (This second issue means that the rowcount will need to be a separate query that excludes the $top and $skip arguments. odata from the grid doesn't do that.)

I believe you should abandon odata and provide us with a simple json object that provides us with the filter, pagesize and skip values, and let us decide on the page of data that we return. That way we can perform the rowcount query and return a page of results as well, and I will be able to perform it in a stored procedure.

Kind regards,

Tony

[Ragavee U S]

Hi Tony, 

Since OData doesn’t support search operation on integer columns, we suggest you to use the WebMethodAdaptor. We can perform server side paging using WebMethodAdaptor (only for ASP.NET) which is extended from the UrlAdaptor of Grid.  

While using UrlAdaptor in ASP.NET , we will get all server side properties as individual parameter in server side and need to handle those properties in server side. So, we suggest you to use WebMethodAdaptor in ASP.NET. The purpose of the WebMethodAdaptor is to bind the datamanager queries passed from client side to the DataManager class. Please refer to the following Help document, code example and sample.  

  

https://help.syncfusion.com/aspnet/grid/editing?cs-save-lang=1&cs-lang=csharp#webmethod-adaptor  

The response from server should be wrapped in an object with properties named result to hold the data and count to hold the total records count. 

Note: skip/take are the queries responsible for handling the paging action  

AllowPaging API of the Grid will handle the pagination. After enabling the AllowPaging, the Grid will be show the pager. It has been used to switching between the pages. In the server-end skip/take values will be varied according to the pagination occurred in the Grid. Refer to the following Help Document.  

  

https://help.syncfusion.com/api/js/ejgrid#members:allowpaging  

  

While using WebMethodAdaptor, you can handle grid operation (filtering/searching/sorting) in server side. When performing filtering, sorting, editing operations on the grid queries are passed to the server side and are serialized by the DataManager class as in the below screenshot. 

 

   

The DataManager class helps in binding the Grid queries passed to the server-side. Based on those queries you can perform server-side operation on the Grid data. The query parameters that help you perform the server-side operations are as follows.  

  

Expand	It is used as OData Expand query.
RequiresCounts	When this property is set to True, the total count of records are included in the result.
Skip	Details regarding current page are skipped.
Take	Used to take required records from data manager.
Sorted	Records return the sorted collection.
Table	It is a data source table name.
Where	Records return the filter collection.

 

DataManager Operations:   

To handle the server-side operations, we have Server-side APIs which has been found in the DataOperations class of Syncfusion Libraries. Refer to the following KB.  

  

https://www.syncfusion.com/kb/4300/server-side-api-for-datamanager-operations  

  

The server-side operations such as sorting, filtering, paging are performed by the PerformSorting, PerfomWherFilter, PerformSkip and PerformTake methods.   

   

In Grid, we have many adaptors to perform server side operations. Please refer to the below link.   

Adaptors: https://help.syncfusion.com/aspnet/grid/data-adaptors 

Handling CRUD operations on server side: https://help.syncfusion.com/aspnet/grid/editing#webmethod-adaptor 

       

Note: When you group a column, the query for sorting operation is passed to the server side and by default it processes and returns the processed data. The grouping operation is executed only at the client side.   

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ASPGrid-574054521.zip 

 

Regards, 

Ragavee U S.