Programmatically alter generated query generated by RadFilter

[Steve LaForge]

I have a SQL database that users should be able to query.  However, the database has some fields that are similar and when they choose to build a filter on one of those columns, I want to also include the same function for the other column(s) as well.  I am not trying to bind the filter directly to a data source, but will programmatically bind it in the code.

For example, if a user selects to filter on Manufacturer starts with 'somecompany', I want to build the SQL query to look like:
ManfName like 'somecompany%' or AltManf like 'somecompany%' or GenericManf like 'somecompany%'

I have been able to create the code to loop the generated expressions, but I am really hoping that there is a much easier way to accomplish my objective.  As it is, I have to use a recursive subroutine to process each of the groups, and manually build the SQL code.  For example, I can use

Dim rfProvider As New RadFilterSqlQueryProvider
rfProvider.ProcessGroup(e.ExpressionRoot)

To build the SQL string, but then there is a LOT of coding that would be required to parse through the string and inject the additional SQL code.

What I want to be able to do is use the recursive call to get the current SQL code, then use a string replacement function to add the check for the additional fields to the query.  The snippet below would be an example of what I am trying to accomplish, but don't know how.

dim queryString as string

Protected Sub RadFilter1_ApplyExpressions(sender As Object, e As Telerik.Web.UI.RadFilterApplyExpressionsEventArgs) Handles RadFilter1.ApplyExpressions
    queryString = String.Empty
    Dim objExpression As RadFilterGroupExpression = e.ExpressionRoot
    Dim objCollection As RadFilterExpressionsCollection = objExpression.Expressions
    processGroup(objCollection)
End Sub

Private Sub processGroup(ByVal objCollection As RadFilterExpressionsCollection)
  For Each objExp As RadFilterExpression In objCollection
    Dim objFunction As RadFilterFunction = objExp.FilterFunction
    Dim rfProvider As New RadFilterSqlQueryProvider
  ' I don't know how to produce the functionality of the next statement!
    Dim strFilter as new string = rfProvider.ProcessFilter(RadFilterExpression)  ' This would return "Manufacturer like 'somecompany%'"

    if strFilter.StartsWith("Manufacturer") then
      queryString &= " (" & strFilter.Replace("Manufacturer","ManfName") & _
        " or " & strFilter.Replace("Manufacturer", "AltManf") & _
        " or " & strFilter.Replace("Manufacturer", "GenericManf") & ") "
    end if
    ...
  Next
End Sub

Can you please provide any suggestions?  Thank you!!!

[Andrey]

Hello,

My colleague have answered your question in the support thread you have supplied, however, I will post his answer here as well, so the community could use it if needed.

The idea is to loop through all expression items and build each group recursively. Before appending the current group expression result you could implement your replacing and processing logic.

Protected Sub RadFilter1_ApplyExpressions(sender As Object, e As Telerik.Web.UI.RadFilterApplyExpressionsEventArgs)
    Dim bulder As New StringBuilder()
    Dim expressionResult As New StringBuilder()
    ProcessExpressions(e.ExpressionRoot, expressionResult)
    Response.Write(expressionResult.ToString())
End Sub

Protected Sub ProcessExpressions(groupExpression As RadFilterGroupExpression, resultExpression As StringBuilder)
    Dim sqlQueryProvider As New RadFilterSqlQueryProvider()
    sqlQueryProvider.ProcessGroup(TryCast(groupExpression, RadFilterGroupExpression))
    Dim result As String = sqlQueryProvider.Result
    'your processing
    resultExpression.Append(result)

    For Each expression As RadFilterExpression In groupExpression.Expressions
        If TypeOf expression Is RadFilterGroupExpression Then
            ProcessExpressions(TryCast(expression, RadFilterGroupExpression), resultExpression)
        End If
    Next
End Sub

Additionally, here is a project that demonstrates the approach.

Regards,
Andrey
the Telerik team