Setting FilterDescriptors.Expression replaces single quotes

[Pioter]

Hi, I am using the following line of code to set FilterDescriptors.Expression programatically from a string in SQL-like format:

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!

[Dess | Tech Support Engineer, Principal]

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

[Pioter]

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.

[Dess | Tech Support Engineer, Principal]

Hello, Pioter,

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