Filter on comma-delimited/multiple values on a Grid Column

[Peter]

Scenario: Users want to be able to specify a comma delimited-set of values to search for.

Solution: Convert "EqualsTo" query with a comma to a "IN { 'a','b','c' }" list style query. Bit hackish.

I'm using the sample in the Forum post as the base (it allows me to have a pre-specified/default filter and then add to it): http://www.telerik.com/forums/grid-filter-and-the-entitydatasource-control-whereparameters

Use a standard (or in my case Calculated) column, but with the field defined, with standard "EqualsTo" field defined:

<telerik:GridTemplateColumn DataField="CustomerTypeCode" UniqueName="CustomerType" HeaderText="Customer Type" SortExpression="CustomerTypeCode" CurrentFilterFunction="EqualTo" AutoPostBackOnFilter="true" ShowFilterIcon="false" FilterControlWidth="130px">
                    <HeaderStyle Width="150px" />
                    <ItemStyle Width="150px" />
                    <ItemTemplate>
                        <%# string.Format("{0} - {1}", Eval("CustomerTypeCode"), Eval("CustomerTypeDescription")) %>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>

You "Selecting" event on the Entity Data Source is handled as per above sample:

protected void edsCustomers_Selecting(object sender, EntityDataSourceSelectingEventArgs e)
        {
            // Get the filtered data source (if applicable) and then append/pre-pend default filter
            EntityDataSource dataSource = e.DataSource;
            AddParameters(dataSource);
            BuildWhere(dataSource);
        }

Here's the crazy hack to rewrite the EqualsTo / Contains (LIKE) to a "IN" clause style query:

private void BuildWhere(IDynamicDataSource dataSource)
        {
            string filter = FilterDefault;

            // Parse filters with commas - convert LIKE and Equals (=) queries to IN query
            string gridFilters = dataSource.Where;
            if (!string.IsNullOrEmpty(gridFilters))
            {
                if (gridFilters.Contains(","))
                {
                    // Find filter fields (either Equals = or LIKE) with data inside quotes and rewrite to an "IN" query
                    // Looks for it.[Field] = "a,b" (or it.[Field] LIKE "%a,b%") etc
                    Regex regexCommaValues = new Regex("it\\.\\w*.....\\s([\"'])(?:(?=(\\\\?))\\2.)*?\\1");
                    var matches = regexCommaValues.Matches(gridFilters);
                    foreach (Match match in matches)
                    {
                        string value = match.Value;
                        value = value.Replace("LIKE \"", "IN {'");
                        value= value.Replace("%", "");
                        value = value.Replace("= \"", "IN {'");
                        value= value.Replace(",", "','");
                        value= value.Replace("\"", "'}");

                        // Replace original match with modified string
                        gridFilters = gridFilters.Replace(match.Value, value);
                    }
                }

                filter += " AND " + gridFilters;
            }

            // If custom filtering enabled, pre-pend with our default parameter
            dataSource.Where = filter;
        }

Enjoy the hack. 

Please - if you can improve the solution or the regex, let me know!

[Peter]

I added a second check for a comma on the Match, in case you've got both LIKE/CONTAINS filters AND comma-delimited searches... you only want the query re-written to an IN query if the specific match contains a comma.

foreach (Match match in matches)
                    {
                        string value = match.Value;

                        // Need to re-check comma in specific match, otherwise could be replacing standard "CONTAINS" function
                        if (value.Contains(","))
                        {...

[Viktor Tachev]

Hi Peter,

The approach you are using looks fine. Since using comma separated values for filtering is not available out of the box such functionality requires custom implementation.

On a side note, if you would like to use multiple values for filtering a column you can consider using CheckList filtering. Check out the following resources that illustrate the functionality.

• CheckList Filtering
• RadGrid - Basic Filtering demo (check out the second RadGrid in the demo)

Regards,
Viktor Tachev
Telerik

 

 

[Peter]

Just a follow-up thanks on letting me know about CheckList filtering - it wasn't clear this functionality was available when I initially started researching how to achieve the result. 

[Viktor Tachev]

Hello Peter,

I am glad to hear that the provided information was helpful to you. In case you have additional queries do not hesitate to contact us again.

Regards,
Viktor Tachev
Telerik