WebGrid with filtering, paging and sorting

A few days ago I received some questions on the use of the Webgrid helper in the comments section of my personal blog, specifically on how to implement custom filters on the data displayed. Since it’s not easy to answer through the comments section, I have forced myself to write an article on it. Here it is.

The main obstacle here is that WebGrid doesn’t include any kind of tool to perform this frequent task. It simply displays the data we provide, and if we want to filter we have to do it manually. In theory, it would be enough to just to supply the Controller the filters we want to apply. Then the Controller queries the info from the Model following the criteria set.

However, something that may seem relatively simple gets harder if we want to also keep the sort order and paging capabilities untouched because we have to arrange for state maintenance between requests as well. Well, this is something we can solve in a few minutes ;-)

Starting out from the project developed in this post of the series on Webgrid, let’s see how we can add a couple of search criteria to the grid, so it can appear to be this way:

The text field enables us to look for substrings in the names and surnames of the people stored in the database, and the other two fields allow to filter by range (min-max) the children they have. As usual, we’ll combine these conditions with a logic AND.

1. The View

First, we enter the form we are going to use to request the user the search criteria in the view, right before generating the grid:

The code is as follows:

@using(Html.BeginForm(null, null, FormMethod.Get))
{
    <fieldset>
        <legend>Search criteria</legend>
        @Html.Label("search", "Text:")
        @Html.TextBox("search") 
        @Html.Label("minChildren", "Children min:")
        @Html.TextBox("minChildren", null, new { @class="num" }) 
        @Html.Label("maxChildren", "Children max:")
        @Html.TextBox("maxChildren", null, new { @class="num"} )
        <input type="submit" value="Apply" />
    </fieldset>
}

Notice the simplicity of the form. We don’t even have to use the lambda syntax in the edit helpers. We generate the controllers starting out from the fields whose values will be in the query string (and not from the Model, which is the usual thing to do). For this reason, notice that the form is set to be sent using the HTTP GET method.

This way we can propagate easily the values of the controls (textbox) between calls:

  • if the user enters criteria and clicks the send button, the URL the request is performed on will be, for instance, /friends/index?search=aguilar&minChildren=1&maxChildren=4.
  • if the user uses the grid’s navigation buttons (next/previous page, go to page, or reorder), these parameters will be added to the previous ones, and therefore they preserve their values between the different calls. This is because Webgrid generates the links to these actions preserving the current query string parameters. In other words, if we are filtering and we go to page 4, we access an address that includes both the search criteria info as well as the paging, something like: /friends/index?search=aguilar&minChildren=1&maxChildren=4&page=4.

And by doing so, we the view layer is finished.

2. The controller

The action method in charge of getting the grid data and sending the view with the data to the user receives three parameters: current page, sort order field and its direction (ascending/descending).

Since now we have to get the sort order criteria, we have to extend its definition adding parameters for these values:

public ActionResult Index(int page = 1, string sort = "surname", 
                          string sortDir = "ASC", string search = null, 
                          int? minChildren = null, int? maxChildren = null)

See how all the parameters are optional, and we set them to null to easily detect when they come filled.

And at which point do we need to use these new parameters? Only in two:

  • in the call we make to the Model to count the total amount of grid rows. We have to inform the grid on the filtering criteria so the counting is performed properly.
  • in the call we make to the Model to get the rows to be displayed in the current page, where we obviously have to take into account the filters.

The action is results somewhat like this:

public ActionResult Index(int page = 1, string sort = "surname", 
                          string sortDir = "ASC", string search = null,
                          int? minChildren = null, 
                          int? maxChildren = null)
{
  var friendsCount = _services.GetFriendsCount(search, minChildren, maxChildren);
  var friends = _services.GetFriendsPage(page, FRIENDS_PER_PAGE, sort, sortDir,
                                               search, minChildren, maxChildren);

  var data = new FriendsPageViewModel()
                 {
                     NumberOfFriends = friendsCount,
                     FriendsPerPage = FRIENDS_PER_PAGE,
                     Friends = friends
                 };

  return View(data);
}

And this is all in the comtroller.

3. The Model

And at last, now in the Model, we have to make the methods used from the controller (GetFriendsCount and GetFriendsPage) take into account the parameters in which we indicate the search conditions.

In the first one, we simply return the number of people which follow the criteria returned as parameters:

public int GetFriendsCount(string searchText = null, int? minChildren = null, 
                           int? maxChildren = null)
{
    IQueryable<Friend> query = _data.People;
    query = filterPeople(searchText, minChildren, maxChildren, query);
    return query.Count();
}

The helper method filterPeople() we use is only in charge of adding the where clauses to the query that we need to take into account the specified conditions:

private static IQueryable<Friend> filterPeople(
                    string searchText, int? minChildren, 
                    int? maxChildren, IQueryable<Friend> query)
{
    if (!string.IsNullOrWhiteSpace(searchText))
        query = query.Where(p => p.Name.Contains(searchText) 
                                 || p.Surname.Contains(searchText));
    if (maxChildren != null)
        query = query.Where(p => p.Children <= maxChildren);
    if (minChildren != null)
        query = query.Where(p => p.Children >= minChildren);
    return query;
}

At last, we implement the method that obtains the data to display on the current page:

public IEnumerable<Friend> GetFriendsPage(int currentPage, int friendsPerPage, 
                                          string sortColumn, string sortDir, 
                                          string searchText, int? minChildren, 
                                          int? maxChildren)
{
    // Validate input data
    sortDir = sortDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? 
                      sortDir : "asc";

    var validColumns = new[] { "surname", "birthdate", "email", "children" };
    if (!validColumns.Contains(sortColumn.ToLower()))
        sortColumn = "surname";

    if (currentPage < 1) currentPage = 1;
    if (friendsPerPage < 1) friendsPerPage = 10;

    // Create the query
    var query = (IQueryable<Friend>)_data.People
                .OrderBy("it." + sortColumn + " " + sortDir);

    query = filterPeople(searchText, minChildren, maxChildren, query);

    return query
            .Skip((currentPage - 1) * friendsPerPage)
            .Take(friendsPerPage)
            .ToList();
}

There is not much to say about this code. In first place, a basic check of the entry parameters is performed, and then generate the query to be performed on the database. As you can see, the Como podéis observar, the queryPeopleFiltered() method is performed to apply the query criteria.

Summarizing…

As we have seen, implementing search criteria in Webgrid doesn’t differ much from what we have described earlier on in my other posts about WebGrid. We just have take into account the following points:

  • firstly, include a form in the View where the query criteria is collected in order to send it to the controller.
  • secondly, prepare the Controller so it can receive this criteria and make it reach the Model.
  • thirdly, in the Model, simply apply this criteria when countimg the total amount of rows, and when obtaining the data to be displayed in the grid page.

Here you can download the example code for this post.

Jose M. Aguilar

ASP.NET/IIS MVP

Jose M. is a well-known world expert in web technologies. He is the author of Microsoft Press SignalR Programming in Microsoft ASP.NET. He works as an independent consultant and developer, helping companies and institutions to reach their goals by using software. He also works with company development teams providing consultancy services and support in several fields. Follow Jose M on Twitter.

, , ,

19 Responses to WebGrid with filtering, paging and sorting

  1. aryan August 2, 2013 at 20:14 #

    I have made one similar webgrid which has a searching criteria. The paging works fine in normal case. But in case of searched results, the pagination is getting failed.
    I guess the problem is related to URL formation. Any help would be appreciated. Thank you.

    • Jose M. Aguilar August 3, 2013 at 11:05 #

      Hi,

      of course, the problem could be due to a wrong composition of the URL, but with the information you provided I’m not able to tell you anything more accurate. Said that, if you use the techniques shown in this post, the pagination and search should work properly.

      Regards.

  2. aryan August 6, 2013 at 20:01 #

    hi

    The idea of sending page number through get request is great. It solved the paging issue.
    Initially I was making ajax call on search textbox which was resulting in paging failure. I followed your way and now it is working absolutely fine. Thanks again :)

  3. Tim August 7, 2013 at 17:46 #

    Hi Jose

    I am trying to achieve the same functionality inside a jquery dialog box because I want my view to come up as popup after clicking on a button.
    When I click on the search button, the view is not rendering inside the jquery dialog box, rather it is coming on the main page. Is there any way to get this done? Or can you suggest some way to show this view as a popup?

    • Jose M. Aguilar August 7, 2013 at 19:57 #

      Hi, Tim.

      The approach shown here is valid only for full page navigation. In fact, the webgrid component is mostly oriented to this kind of interaction, although if has its own Ajax mechanisms.

      If you don’t need to implement searching, you could try to use the webgrid in ajax mode into your jquery dialog. More info about it here: http://www.campusmvp.net/blog/webgrid-in-ajax-mode-with-mvc-3

      Otherwise, I think you could try to capture the clicks to any webgrid link (pagination, sorting, etc.) and search form, and replace the standard navigation by an Ajax call to the server with the same querystring, for example using jquery’s load() function to get only the fragment of the page that you’ll need (the webgrid contents) to put into the dialog.

      I’ve not checked this solution and may not work fine, but at least it is an interesting line of research ;)

      Regards,
      Jose.

  4. ajay September 2, 2013 at 08:28 #

    Thanks for nice tutorial…as I am newbie to MVC web-grid it helped a lot….

  5. Alicia December 11, 2013 at 13:09 #

    Hi Jose,
    Great article ,it really helped me !

    Just one question around validation. I have built the screen similar to above , and now want to add validation to my search criteria , i.e. if i want to search for a date range and validate that its between two different dates.

    I have added attributes and it’s all working fine , but the issue i have is returing to the view after a validation has failed. i.e. when if (ModelState.IsValid) is false . I dont know what to return to the view. If i simply return view() , i get an error on my grid because my model is now null. i.e. data has not been persisted. Do i have to rebuild the model again from the search criteria , so i can return a view with a model ? If the validation has failed , how can i just return to the view as is ?

    I hope I’m making myself clear , thanks ,
    Alicia

    • Jose M. Aguilar December 11, 2013 at 17:56 #

      Hi!

      Yes, if the view is typed to FriendsPageViewModel, you should send and object of this type, as it is done in the Index() action. So, you’ll have to instantiate this object and populate its fields before sending it to the view.

      Hope this helps,
      Jose.

  6. Anil December 16, 2013 at 05:46 #

    Hi Jose,

    Thanks for posting this article this is properly working with me .
    But i want Parent child Relation with Searching

    Suppose
    I have 2 table one is

    Friend
    FId Fname

    and second is

    FriendDetail
    FId FCity FState FCountry

    and i want to search on both cases either Fname or Fcity

    Please guide me how to do this

    Thank you in advance

    Thank
    Anil

    • Jose M. Aguilar December 16, 2013 at 12:36 #

      Hi!

      If you want to search in these two fields, you should change only the model component. In the example above, you could extend the “where” clause of the query (in filterPeople method) to include the search by another fields.

      Regards.

      • Anil December 17, 2013 at 05:05 #

        Hi Jose,

        Thanks for you reply but i did not get my desire output , i just pass one more parameter in filterPeople in and trying to write where clause direct but i did not work for me .

        according to you i will change my Friend entity –>Frienddetails
        and use in Frienddetails every where or just for for where clause

        Please guide me.
        i am new in entity framework

        Thanks

  7. Susan February 13, 2014 at 15:42 #

    Great article. However, I am trying to display a dropdown and a webgrid on the same page. The webgrid will contain the results based on the value of the dropdown. I am using the Html.Begin form to submit the dropdown value to the backend and returning the results in the webgrid for the first page successfully. However, when I try to page forward, the dropdown is reloaded and the webgrid results are gone. Do you know how I can achieve what I am trying to do? Thanks.

    • Jose M. Aguilar February 13, 2014 at 17:34 #

      Hi,

      I think you should try replacing the dropdown by a simple text box, and then follow the steps described in this post to filter the data by the value of text box.

      Once everything is working, you could then replace the text box with the dropdown.

      Hope this helps,
      Jose.

      • Susan February 13, 2014 at 19:19 #

        Thank you, Jose…however, I am getting data. It’s just that when I attempt to go to next page, the dropdown values get repopulated. I need to somehow separate the two functions. I tried a partial view with the webgrid but it still reposts the dropdown back to the first value in the dropdown.

  8. Susan February 13, 2014 at 19:24 #

    Here is the code in my view page:
    using Syndication.ViewModel

    @model SyndicationViewModel
    @{

    ViewBag.Title = “Index”;
    IEnumerable accounts = Model.Accounts.Select(a =>
    new SelectListItem()
    {
    Text = a.SyndicationAccount.ToString(),
    Value = a.SyndicationAccount.ToString()
    }
    );
    }

    Syndication

    Select Account

    @using (Html.BeginForm(“Index”, “Home”, FormMethod.Post))
    {
    @Html.DropDownList(“accountNo”, accounts, new { @class = “userPreferenceItem”, id = “AccountFilter” })

    }

    @{ Html.RenderPartial(“GridView”, Model);}

    • Jose M. Aguilar February 13, 2014 at 20:56 #

      Hello,

      I think it’s nothing to do with partial views. Did you try using the GET method in the form, as it is shown in this article? In this way, the dropdown current value will be propagated to the subsequent requests, and you’ll be able to use it to select the initial value.

      Regards.

  9. Susan February 13, 2014 at 22:51 #

    Jose, you were so right! I was using POST instead of GET. Thanks for your help!

  10. Dmytro March 5, 2014 at 17:42 #

    Could you please tell how I can make the search criteria form appear to the left from the webgrid rather than above it?

    Thanks.

    • Jose M. Aguilar March 5, 2014 at 18:53 #

      Dymtro, it’s just a layout question and it depends on your web site design, so I can’t give you a generic answer for it :(

      Basically, you’ll have to change the markup in the view where webgrid resides and its associated stylesheet. Please refer to your HTML and CSS tutorials or documentation for more information.

      Regards.

Leave a Reply