SfGrid - Issues filtering on boolean values when using OData V4 adaptor

Question

Hello, I am using the DataGrid control and I am having some difficulty filtering on boolean values when binding to remote data using the OData v4 adaptor. I am on version 18.4.0.43 which is the latest nuget package available at the time of this writing.

Here is how I have defined my Grid:

<SfGrid AllowExcelExport="true"

AllowFiltering="true"

AllowMultiSorting="false"

AllowPaging="true"

AllowReordering="true"

AllowResizing="true"

AllowSelection="false"

AllowSorting="true"

EnableAltRow="true"

TValue="FormularyProductItem">

<GridColumn Field="ProductName"

HeaderText="Product Name"

Type="ColumnType.String" />

<GridColumn Field="PriorAuthorizationIndicator"

HeaderText="Prior Authorization Required"

Type="ColumnType.Boolean" />

</GridColumns>

</SfGrid>

Also, here is how I have defined the data type which is bound to the grid (the TValue):

public class FormularyProductItem

{

[Key]

public int FormularyProductId { get; set; }

public string ProductName { get; set; }

public bool PriorAuthorizationIndicator { get; set; }

}

As you can see, I am defining one of columns (PriorAuthorizationIndicator column) with the GridColumn.Type property as ColumnType.Boolean and also my class has the same property defined as a bool.

When using the FilterType.Menu on the GridFilterSettings, I believe an improper syntax is being applied to the OData parameters sent to the server. From my developer console, I can see that this attempts to filter on this property as though it were a string - here is the full URL it generates:

https://localhost:44320/odata/formularyproducts(3300)?$count=true&$filter=(tolower(PriorAuthorizationIndicator) eq 'true')&$skip=0&$top=15

If I try to send this exact call via Postman to my server, I see the following error:

"message": "The query specified in the URI is not valid. No function signature for the function with name 'tolower' matches the specified arguments.

The function signatures considered are: tolower(Edm.String Nullable=true).",

If I change it to use FilterType.CheckBox on the GridFilterSettings, then it works slightly better. If I use the checkbox filter and click true, I see the following URL generated for the API call:

https://localhost:44320/odata/formularyproducts(3300)?$count=true&$filter=(PriorAuthorizationIndicator eq true)&$skip=0&$top=15

Notice this time, it is not using the tolower() syntax in the URL query string, which I believe is how it should be for boolean data types. Now the problem with this approach is if I try to first sort on the column and then apply the filter, now another incorrect query is generated as follows:

https://localhost:44320/odata/formularyproducts(3300)?$count=true&$orderby=PriorAuthorizationIndicator,PriorAuthorizationIndicator&$filter=(PriorAuthorizationIndicator eq true)&$skip=0&$top=15

The error log on my server now shows:

"message": "The query specified in the URI is not valid. Duplicate property named 'PriorAuthorizationIndicator' is not supported in '$orderby'.",

I believe this second issue is related to another one someone else reported - the following issue is for the "Excel" type filter (but I'm using CheckBox filter). However the error sounds like it is the same:

https://www.syncfusion.com/feedback/21861/duplicate-sort-query-is-generated-when-opening-a-excel-filter-for-sorted-column

The only filter option which appears to let me do both sort and filter at the same time for boolean types is the FilterType.FilterBar on the GridFilterSettings, however this is not very user friendly because it requires the user to type, and also if I use a column template to show something like a different icon for true/false it is not intuitive to the user that they would have to type true or false in the filter bar.

It seems there is currently no good way to filter on Boolean data types using the OData v4 Adaptor on the DataGrid. Please advise if I have done something incorrectly or if there is any workaround. Thank you.

Jeevakanth Palaniappan · Accepted Answer

Hi Mashuk , 
 
Greetings from Syncfusion support. 

Query 1 : "message": "The query specified in the URI is not valid. No function signature for the function with name 'tolower' matches the specified arguments. 
The function signatures considered are: tolower(Edm.String Nullable=true).", 

we have considered your query as a bug and logged the defect report “Exception throws on Filtering(Menu) in Boolean columns in Odatav4” for the same. Thank you for taking the time to report this issue and helping us improve our product. At Syncfusion, we are committed to fixing all validated defects (subject to technological feasibility and Product Development Life Cycle ) and including the defect fix in our upcoming patch release which is expected to be rolled out by the mid of March 2021. 
 
You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.     
 
https://www.syncfusion.com/feedback/22694/exception-throws-on-filteringmenu-in-boolean-columns-in-odatav4 
 
Query 2 : "message": "The query specified in the URI is not valid. Duplicate property named 'PriorAuthorizationIndicator' is not supported in '$orderby'." 
 
We have fixed this issue in the latest patch release 18.4.44 version. So kindly update the NuGet to the latest version to resolve your issue. 
 
Regards, 
Jeevakanth SP.

Vadim · Answer

Hello,I just updated to "Syncfusion.Blazor" Version="18.4.0.47" - issue is fixedBest regards,Vadim.

Jeevakanth Palaniappan · Answer

Hi Vadim, 

We are glad that your issue has been resolved. Please get back to us if you have any other queries. 

Regards, 
Jeevakanth SP.