this.portfolioGrid.FilterDescriptors.Expression = newFiltersExpr;
I discovered the following issue when using a string like
src_sec_prim_id in ('4008030','4008032','4010014','4025022','4029402','4032004','4150008','4150009','4150010','4150011','4150012')
Note that all the items in the above list have single quotes around them. But the resulting FilterDescriptors.Expression looks like this:
[src_sec_prim_id] IN (4008030,4008032,4010014,4025022,4029402,4032004,4150008,4150009,4150010,4150011,4150012)
That is, all the single quotes got eliminated! I imagine the issue is the setter of FilterDescriptors.Expression recognizes all the items as numbers and decides to remove the single quotes.
To test this, I used a list that has some items that could not be mistaken for numerical, and indeed this did not happen!
This is an undesirable behavior for me, since I later need to use the FilterDescriptors.Expression to construct an actual SQL query, that breaks if the single quotes are absent.
Would you advise anything that could help me override this behavior?
Thank you!
2 Answers, 1 is accepted
Hello, Pioter,
Following the provided information, I have prepared a sample project to test the FilterExpression property and what value is returned after filtering a text or a numeric column.
Example 1 with text column:
Example 2 with numeric column:
The above screenshots illustrate the result from the Excel-like filtering: https://docs.telerik.com/devtools/winforms/controls/gridview/filtering/excel-like-filtering
I have also tested the basic filtering:
Text column:
However, for the Numeric column you don't have such a filter operator "Is in list":
That is why I would recommend you to use either a text column with the basic filtering "Is in list" operator or use the numeric column with Excel like filtering.
I have attached my sample project for your reference. I hope this information helps. If you need any further assistance please don't hesitate to contact me.
Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik
Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.
Rank 1
Hi, Dess, thank you for the reply (I was away and could not respond immediately).
I agree with you that the situation I describe wouldn't happen if I used the grid's filter UI, even when using the Custom filter. However, note that this not what I am asking about. I am setting the FilterDescriptors.Expression programatically. And the resulting FilterDescriptors.Expression is such that what I set it to is not what I am getting back in case where all the values can be parsed as numeric! Here are some screenshots for you. In the first one you can see the value of the variable newFilterExpr as it being used in the line
this.portfolioGrid.FilterDescriptors.Expression = newFiltersExpr;
You can see that all the values in the list are single-quoted.
In the second screenshot you can see the value of this.portfolioGrid.FilterDescriptors.Expression after it was set in the line above. You can see that the values in the list are all unquoted.
This is the issue.
The FilterDescriptor.FilterExpression gives you access to the filter applied in RadGridView. However, please have in mind that it uses SQL-like format but it can't be completely considered as SQL compatible. RadGridView has its own parsing logic for interpreting the filter expressions. It is improved in such a way that the parsing logic can understand different formats as input. However, the returned expression follows the rules defined in RadGridView.
If you need to construct an actual SQL query considering the applied filters to RadGridView, it is necessary to convert manually the expression according to the desired custom format.
I hope this information helps. If you need any further assistance please don't hesitate to contact me.
Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik
Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.