This is a migrated thread and some comments may be shown as answers.

Example of custom filtering?

14 Answers 695 Views
Grid
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Nick
Top achievements
Rank 1
Nick asked on 19 Feb 2010, 01:49 PM
Hi,

I have a service layer between my web site and my repository so I cannot expose an IQueryable to the grid. Therefore I am trying to create a service function that takes the sortOrder collection and the IFilterDescriptor collection and translates them into a linq query against the repository, returning the correct listing of business objects to the controller.

The sorting was not a problem, but I am confused as to how to go about the filtering.

So far I have:

  Public Function GetAudits(ByVal index As Integer, _ 
                            ByVal pageSize As Integer, _ 
                            ByVal sortOrders As List(Of Telerik.Web.Mvc.SortDescriptor), _ 
                            ByVal filters As List(Of Telerik.Web.Mvc.IFilterDescriptor)) As PagedList(Of PortalAudit) Implements IPortalAuditService.GetAudits 
 
    Dim audits = From a In _repository.GetAudits Select a 
 
    ' filtering 
    If filters.Count > 0 Then 
      ' DO SOMETHING HERE.... 
    End If 
 
    ' sort order 
    If sortOrders.Count = 0 Then 
      audits = audits.OrderByDescending(Function(a) a.DateStamp) 
    Else 
      For Each order In sortOrders 
        AddSortOrder(audits, order) 
      Next 
    End If 
 
    ' Return paged list of audit records. 
    Return (From a In audits Select New PortalAudit With {.Action = a.Action, _ 
                                                          .AffectedEntity = a.AffectedEntity, _ 
                                                          .AuditId = a.AuditId, _ 
                                                          .Controller = a.Controller, _ 
                                                          .DateStamp = a.DateStamp, _ 
                                                          .Description = a.Description, _ 
                                                          .Status = a.Status, _ 
                                                          .UserName = a.UserName}).ToPagedList(index, pageSize) 
  End Function 
 
  Private Shared Sub AddSortOrder(ByRef audits As System.Linq.IQueryable(Of Audit), ByVal order As SortDescriptor) 
    Select Case order.Member.ToLower 
      Case "action" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.Action) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.Action) 
        End If 
      Case "affectedentity" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.AffectedEntity) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.AffectedEntity) 
        End If 
      Case "auditid" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.AuditId) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.AuditId) 
        End If 
      Case "controller" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.Controller) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.Controller) 
        End If 
      Case "datestamp" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.DateStamp) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.DateStamp) 
        End If 
      Case "description" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.Description) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.Description) 
        End If 
      Case "status" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.Status) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.Status) 
        End If 
      Case "username" 
        If order.SortDirection = ComponentModel.ListSortDirection.Ascending Then 
          audits = audits.OrderBy(Function(a) a.UserName) 
        Else 
          audits = audits.OrderByDescending(Function(a) a.UserName) 
        End If 
    End Select 
  End Sub 
 

Can you point me at an example of how I would take the IFilterDescriptors and use them to filter my repository?

Many thanks,
Nick



14 Answers, 1 is accepted

Sort by
0
Atanas Korchev
Telerik team
answered on 19 Feb 2010, 02:14 PM
Hi Nick,

Here is some sample code based on our custom binding example which should get you started:

//Filtering should be performed before paging
foreach (IFilterDescriptor filterDescriptor in command.FilterDescriptors)
{
    data = ApplyFilter(data, filterDescriptor);
}

And here is the implementation of ApplyFilter (not for all columns and not for all filter operators though):

private static IQueryable<Order> ApplyFilter(IQueryable<Order> source, IFilterDescriptor filter)
{
        if (filter is CompositeFilterDescriptor)
        {
            foreach (FilterDescriptor childFilter in ((CompositeFilterDescriptor)filter).FilterDescriptors)
            {
                source = ApplyFilter(source, childFilter);
            }
        }
        else
        {
            FilterDescriptor filterDescriptor = (FilterDescriptor)filter;
            switch (filterDescriptor.Member)
            {
                case "OrderID":
                    switch (filterDescriptor.Operator)
                    {
                        case FilterOperator.IsGreaterThan:
                            source = source.Where(o => o.OrderID > Convert.ToInt32(filterDescriptor.Value));
                            break;
                        case FilterOperator.IsLessThan:
                            source = source.Where(o => o.OrderID < Convert.ToInt32(filterDescriptor.Value));
                            break;
                    }
                    break;
                case "ShipAddress":
                    switch (filterDescriptor.Operator)
                    {
                        case FilterOperator.Contains:
                            source = source.Where(o => o.ShipAddress.Contains(filterDescriptor.Value.ToString().ToLower()));
                            break;
                        case FilterOperator.IsEqualTo:
                            source = source.Where(o => o.ShipAddress.CompareTo(filterDescriptor.Value.ToString().ToLower()) == 0);
                            break;
                    }
                    break;
            }
        }
 
    return source;
}

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Nick
Top achievements
Rank 1
answered on 19 Feb 2010, 05:06 PM
Hi Atanas,

Thanks for the example, it was just what I needed.

I have come across one problem with the grid.  I have a column bound to a boolean datatype.  From the filter menu I can select either "Is True" or "Is False" and the data in the grid is filtered accordingly.  After that however, I cannot change that filter selection.

If I click on the Clear Filter button the grid is repopulated correctly but the filter menu on that column still has the previous selection.  I cannot select the other option to change the filter.

This behaviour is shown in Firefox 3.5.7 and 3.6.  In IE8 I cannot filter by that column at all.

I also have a quick question.  Is there any documentation that lists which FilterDescriptor.Operators are related to each datatype?  It would be useful so that I can make sure I don't miss any permutations when constructing the nested Switch statements.

Cheers,
Nick


0
Atanas Korchev
Telerik team
answered on 22 Feb 2010, 08:33 AM
Hi Nick,

I believe the problem you are seeing is a bug which we have fixed in the Q1 2010 futures build. Unfortunately that build has problem with date filtering. I have attached a patched version of telerik.grid.filtering.js for Q1 2010 (assembly version 2010.1.218) which you can use if you decide to give that build a try.

Here is the list of all filter operators supported for data type:

'String': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Starts with': 'startswith',
            'Contains': 'substringof',
            'Ends with': 'endswith'
        },
        'Number': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Is less than': 'lt',
            'Is less than or equal to': 'le',
            'Is greater than': 'gt',
            'Is greater than or equal to': 'ge'
        },
        'Date': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Is before': 'lt',
            'Is before or equal to': 'le',
            'Is after': 'gt',
            'Is after or equal to': 'ge'
        }

And for boolean values we support only the equals operator.

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Atanas Korchev
Telerik team
answered on 22 Feb 2010, 08:34 AM
Hi Nick,

I believe the problem you are seeing is a bug which we have fixed in the Q1 2010 futures build. Unfortunately that build has problem with date filtering. I have attached a patched version of telerik.grid.filtering.js for Q1 2010 (assembly version 2010.1.218) which you can use if you decide to give that build a try.

Here is the list of all filter operators supported for data type:

'String': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Starts with': 'startswith',
            'Contains': 'substringof',
            'Ends with': 'endswith'
        },
        'Number': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Is less than': 'lt',
            'Is less than or equal to': 'le',
            'Is greater than': 'gt',
            'Is greater than or equal to': 'ge'
        },
        'Date': {
            'Is equal to': 'eq',
            'Is not equal to': 'ne',
            'Is before': 'lt',
            'Is before or equal to': 'le',
            'Is after': 'gt',
            'Is after or equal to': 'ge'
        }

And for boolean values we support only the equals operator.

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Nick
Top achievements
Rank 1
answered on 22 Feb 2010, 10:57 AM
Thanks Atanas,

I have downloaded and installed the 2010 Q1 Futures (2010.1.218) and replaced the 2009 Q3 stuff in my project with them.  Now when I build my project I have the following errors:

  • Warning   1   'Public Overridable Function Ajax(configurator As System.Action(Of Telerik.Web.Mvc.UI.Fluent.GridAjaxSettingsBuilder)) As Telerik.Web.Mvc.UI.Fluent.GridBuilder(Of T)' is obsolete: 'Use DataBinding(dataBinding => dataBinding.Ajax().Select()) instead'.
  • Error   2   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   3   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   4   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   5   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   6   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   7   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.
  • Error   8   Type 'Telerik.Web.Mvc.UI.GridColumn' is not defined.

I assume that GridColumn has gone away and been replaced by GridBoundColumn?  My view looks like this:

<%@ Page Title="" Language="VB" MasterPageFile="~/Views/Shared/SingleColumn.Master" Inherits="System.Web.Mvc.ViewPage" %> 
 
<asp:Content ID="cTitle" ContentPlaceHolderID="TitleContent" runat="server"
  Index 
</asp:Content> 
 
<asp:Content ID="cHeader" ContentPlaceHolderID="HeaderContent" runat="server"
</asp:Content> 
 
<asp:Content ID="cMain" ContentPlaceHolderID="MainContent" runat="server"
  <h1>Index</h1> 
  <
    Dim grid As Telerik.Web.Mvc.UI.Grid(Of PortalAudit) = Html.Telerik.Grid(Of PortalAudit) _ 
                                                              .Name("AuditGrid") _ 
                                                              .Ajax(Function(g) g.Action("AuditListing", "Audit")) _ 
                                                              .Pageable(Function(p) p.Total(CInt(ViewData("Total")))) _ 
                                                              .Sortable() _ 
                                                              .Filterable() _ 
                                                              .EnableCustomBinding(True) _ 
                                                              .ClientEvents(Function(events) events.OnError("onGridError")) 
    grid.Paging.PageSize = 20 
    Dim colUserName As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.UserName) 
    Dim colAction As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.Action) 
    Dim colController As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.Controller) 
    colController.Title = "Area" 
    Dim colAffectedEntity As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.AffectedEntity) 
    Dim colStatus As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.Status) 
    Dim colDescription As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.Description) 
    Dim colTimeStamp As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.DateStamp) 
    colTimeStamp.Title = "DateStamp" 
    colTimeStamp.Format = "{0:dd/MM/yyyy HH:mm}" 
    grid.Columns.Add(colUserName) 
    grid.Columns.Add(colController) 
    grid.Columns.Add(colAction) 
    grid.Columns.Add(colAffectedEntity) 
    grid.Columns.Add(colStatus) 
    grid.Columns.Add(colDescription) 
    grid.Columns.Add(colTimeStamp) 
    grid.Render() 
  %> 
</asp:Content> 
 
<asp:Content ID="cScript" ContentPlaceHolderID="ScriptContent" runat="server"
  <script type="text/javascript"
    function onGridError(e) { 
      var xhr = e.XMLHttpRequest; 
      if (xhr.status == 403) { 
        e.preventDefault(); 
        windowwindow.location = window.location; 
      } 
      else { 
        alert(e.textStatus); 
      } 
    } 
  </script> 
</asp:Content> 

The help files for the gridboundcolumn does not have any useful example of how to use this new class. Can you point me in the right direction please?  Just to make things more interesting for you I'm doing this in VB.NET on Framework 3.5  :)

Thanks,
Nick


0
Atanas Korchev
Telerik team
answered on 22 Feb 2010, 11:31 AM
Hello Nick,

Yes GridBoundColumn has replaced GridColumn. However unless you are using customized source code you shouldn't be having problems. The warnings you are getting are from stuff which is now made obsolete. Here is a list of the obsolete stuff. We have also removed and renamed a few types which however should affect you only if you are using a customized source.

Let me know if you have applied any customizations to GridColumn so I can assist with migrating them to GridBoundColumn.

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Nick
Top achievements
Rank 1
answered on 22 Feb 2010, 11:55 AM
Hi Atanas, thanks for the quick reply.

If I try to replace this:
Dim colUserName As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit)(Function(a) a.UserName) 

With this:
Dim colUserName As New Telerik.Web.Mvc.UI.GridBoundColumn(Of PortalAudit)(Function(a) a.UserName) 

I get the error message:
    Too few type arguments to 'Telerik.Web.Mvc.UI.GridBoundColumn(Of TModel, TValue)'.

The help page for the constructor has no examples so I don't understand what I should be providing for TModel and TValue  :(

Do you have an example of constructing this new version of the grid in VB.NET?

Thanks,
Nick

0
Atanas Korchev
Telerik team
answered on 22 Feb 2010, 01:08 PM
Hi Nick,

TValue is the type of the property returned by the expression. This should work:

Dim colUserName As New Telerik.Web.Mvc.UI.GridColumn(Of PortalAudit, String)(Function(a) a.UserName)

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Nick
Top achievements
Rank 1
answered on 22 Feb 2010, 01:20 PM
Hmm, not quite  :(

Dim colUserName As New Telerik.Web.Mvc.UI.GridBoundColumn(Of PortalAudit, String)(Function(a) a.UserName) 
gives the error message
"Lambda expression cannot be converted to 'Telerik.Web.Mvc.UI.Grid(Of Attenda.Stargate.Services.PortalAudit)' because 'Telerik.Web.Mvc.UI.Grid(Of Attenda.Stargate.Services.PortalAudit)' is not a delegate type"

Nick.


0
Georgi Krustev
Telerik team
answered on 22 Feb 2010, 02:44 PM
Hello Nick,

Here is a simple declaration of the grid, which uses the new GridBoundColumn:
<%         
    Dim grid As Telerik.Web.Mvc.UI.Grid(Of Product) = Html.Telerik.Grid(Of Product) _
                                                              .Name("ProductGrid") _
                                                              .DataBinding(Function(g) g.Ajax().Select("_AjaxLoading", "Home")) _
                                                              .Pageable() _
                                                              .Sortable() _
                                                              .Filterable() _
                                                              .EnableCustomBinding(True) _
                                                              .ToComponent()
    grid.Paging.PageSize = 20
     
    Dim colProductName As New Telerik.Web.Mvc.UI.GridBoundColumn(Of Product, String)(grid, Function(p As Product) p.ProductName)
    Dim colDiscontinued As New Telerik.Web.Mvc.UI.GridBoundColumn(Of Product, Boolean)(grid, Function(p As Product) p.Discontinued)
     
    grid.Columns.Add(colProductName)
    grid.Columns.Add(colDiscontinued)
 
    grid.Render()
%>

Regards,
Georgi Krustev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Doug
Top achievements
Rank 1
answered on 22 Feb 2010, 02:49 PM
Nick,

I found yourthread while working on something similar. I found a clever way to create Dynamic Orderby's in LINQ at  http://blogs.msdn.com/swiss_dpe_team/archive/2008/06/05/composable-linq-to-sql-query-with-dynamic-orderby.aspx

The solution works perfectly and you can avoid all the if..then's on columns and asc/desc'ing

Now, I'm looking for the same kind of solution for filtering...
0
Nick
Top achievements
Rank 1
answered on 22 Feb 2010, 03:47 PM
Hello Georgi,

Brilliant, that was exactly what I was looking for.  My grid is rendering successfully again  :)

Ok, hopefully this is the last problem....I now have an client-side issue filtering on the date column of my grid.

The date column has date-pickers on the filter menu, which is very nice but is causing me two problems:
1. Once the date-picker has had a date selected, the clear filter button does not remove the date from the text box. 
Steps to reproduce the problem:
    a) On a date column set the filter to Is After dateX and Is Before dateY
    b) Click the filter button.  This filters the data as you would expect, showing only records between date X and Y
    c) On the same column, click the Clear Filter button.  The data returned is unfiltered but the filter menu for the column has the dates still in it.  These dates cannot be deleted  :(

This testing was done in Firefox 3.6 and IE8 on Windows XP Pro SP3 on build 2010.1.218 of the Telerik MVC Extensions

2. The dates in my column are formatted to UK format (colTimeStamp.Format = "{0:dd/MM/yyyy HH:mm}")  However, the dates in the filter menu are still in US format  :(

Once again, many thanks for your assistance thus far,
Nick

0
Accepted
Atanas Korchev
Telerik team
answered on 22 Feb 2010, 04:50 PM
Hello Nick,

This first issue is a known problem and has been fixed already. You can find a more recent build in this forum thread.

The second problem is a known issue which we will fix for the official release. I hope this is not a showstopper for you.

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Nick
Top achievements
Rank 1
answered on 22 Feb 2010, 05:30 PM
Awesome, that all works now, many thanks to both of you for your support.

The date format thing in the filter menu is not a show stopper for me at the moment, I'll be keeping an eye out for the full release  :)

Thanks again,
Nick

Tags
Grid
Asked by
Nick
Top achievements
Rank 1
Answers by
Atanas Korchev
Telerik team
Nick
Top achievements
Rank 1
Georgi Krustev
Telerik team
Doug
Top achievements
Rank 1
Share this question
or