Example of custom filtering?

[Nick]

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

[Atanas Korchev]

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.

[Nick]

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

[Atanas Korchev]

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.

[Atanas Korchev]

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.

[Nick]

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

[Atanas Korchev]

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.

[Nick]

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

[Atanas Korchev]

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.

[Nick]

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.

[Georgi Krustev]

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.

[Doug]

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...

[Nick]

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

[Atanas Korchev]

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.

[Nick]

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