Filter Expression in Rad grid

2 posts, 0 answers
  1. Ben
    Ben avatar
    6 posts
    Member since:
    Jul 2016

    Posted 27 Sep Link to this post

    Hi, 
    I have a radgrid which has custom filtering, sorting , paging and if I want to find the filter expression I go to

    Radgrid.MasterTableView.FilterExpression.
    The expression I get uses a lot of OR clause instead of having them in a IN clause.Ex. 
    (([Column_One] = 'Cust ')) AND (([Column_Two] = '3/31/2016 12:00:00 AM') OR ([Column_Two] = '3/18/2016 12:00:00 AM') OR ([Column_Two] = '3/28/2016 12:00:00 AM'))
    When I use the below code to manually filter the datatable 
    Dim filteredRows As DataRow() = dataTable.Select(RadGrid1.MasterTableView.FilterExpression)
     I get stackoverflow error when I use the above filter expression for custom filtering as the number of columns and options can get huge. This https://blogs.msdn.microsoft.com/sqldev/2008/12/10/you-may-get-system-stackoverflowexception-in-your-asp-net-application-if-your-filter-condition-has-too-many-or-in-your-datatable-select-function/ article suggest that instead of having multiple OR clauses it should be reduced to IN clause to mitigate this problem. Is there a way to get the query in such a way so that I don't have to write one myself.

    Also, Can you also tell me how the default filtering using onNeedDataSource works. I have a huge datatable( around 50k recods). When I tried debugging with default paging goes to the onNeedDataSource event and then filters the data. I am not sure if the dataTable is being fetched every single time. As the time taken for filtering is quite high.

    I followed all the optimization techniques and got the paging to run quicker. But in the example for custom paging I see that Custom paging is being turned off when filtering or grouping  is applied. Like http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/paging/custom-paging . This again causes the filtering to run slower. 

    Is this because the default filtering causes the page the fetch the entire data and then select the required columns from there.?

    This the code as of Now. I am turning off the custom paging only for grouping. And it runs faster as I take the table from the Cache and not the Database.

    Private isGrouping As Boolean = False
     
        Protected Sub RadGrid1_GroupsChanging(source As Object, e As GridGroupsChangingEventArgs)
            isGrouping = True
            If e.Action = GridGroupsChangingAction.Ungroup AndAlso RadGrid1.CurrentPageIndex > 0 Then
                isGrouping = False
            End If
        End Sub
     
        Public Function ShouldApplySortFilterOrGroup() As Boolean
            Return (RadGrid1.MasterTableView.GroupByExpressions.Count > 0 OrElse isGrouping) OrElse RadGrid1.MasterTableView.SortExpressions.Count > 0
        End Function
     
        Protected Function getDataTable() As DataTable
            Dim key As String = [String].Format("{0}/{1}/{2}/{3}/{4}", x, y, c, f, s)
            Dim dataTable As DataTable = TryCast(HttpRuntime.Cache(key), DataTable)
            If dataTable Is Nothing Then
                SqlDataSource1.FilterExpression = Session("as")
                SqlDataSource1.DataBind()
                Dim dv As DataView = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), DataView)
                dataTable = dv.ToTable()
                Session(key) = dataTable
                HttpRuntime.Cache(key) = dataTable
            End If
            Return dataTable
        End Function
     
        Protected Sub RadGrid1_NeedDataSource(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
            Dim dataTable As DataTable = getDataTable()
            If RadGrid1.MasterTableView.FilterExpression <> "" Then
                Dim filteredRows As DataRow() = dataTable.Select(RadGrid1.MasterTableView.FilterExpression) `StackOverFlow error occours here
                If filteredRows.Length > 0 Then
                    dataTable = filteredRows.CopyToDataTable()
                End If
            End If
            Dim startRowIndex As Integer = If((ShouldApplySortFilterOrGroup()), 0, RadGrid1.CurrentPageIndex * RadGrid1.PageSize)
            Dim maximumRows As Integer = If((ShouldApplySortFilterOrGroup()), dataTable.Rows.Count, RadGrid1.PageSize)
            RadGrid1.AllowCustomPaging = Not ShouldApplySortFilterOrGroup()
            If RadGrid1.AllowCustomPaging Then
                RadGrid1.VirtualItemCount = dataTable.Rows.Count
            End If
            Dim rows As DataTable = dataTable.AsEnumerable().Skip(startRowIndex).Take(maximumRows).CopyToDataTable()
            RadGrid1.DataSource = rows
        End Sub

    Any help would be great. Thanks!
  2. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 30 Sep Link to this post

    Hi Ben,

    When you use custom paging, filtering, etc. you would need to perform the operations manually. Please refer to the following article that describes the IN operator in more detail. You can use the examples there to modify your query to use IN instead of OR.


    Regarding your second query. By default all records are retrieved from the data source and then paging, filtering, sorting is applied internally. If you have a lot of records it is likely to see poor performance.

    When filtering or sorting is performed the operation should be applied to all records. Thus, it is recommended to build the filtering query first and then apply paging.

    As an alternative I can suggest using an EntityDataSource control. With it the sorting, paging, filtering operations can be performed on database level. Thus, you should be able to have the same improved performance with less custom code. You can see RadGrid bound to EntityDataSource in action in the following online example.



    Regards,
    Viktor Tachev
    Telerik by Progress
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  3. UI for ASP.NET Ajax is Ready for VS 2017
Back to Top