Skip to content

Using jqGrid with ASP.NET MVC: LINQ Extensions

Mere hours after I posted the first in a planned series of posts on using jqGrid in ASP.NET MVC applications, Phil Haack, a rather-more-widely-read-ASP.NET-MVC-blogger, wrote a long post on, er, exactly the same thing. Who, me, bitter? Naahhh… :)

But it turns out that we’re using the grid in a different way, and I think the difference is important. So rather than just referring you to Phil’s post and skipping the rest of the series, I think I still have some important information to contribute. In particular, I’m using the grid in a way which allows me to write an extension method for IQueryable<T> which returns data suitable for the grid, without having to know anything about the type T. If you look closely at the code in Phil’s post, you will see that populating the cell array requires custom code (or use of reflection) every time you want to return data to a grid.

It’s important to me that the method should work without knowing anything about the type of the data in the list because I don’t want to have to write repetitious code in every action method which supplies data to a grid to shape the data according to the grid’s needs, and because I generally use anonymous types for JSON serialization. JSON cannot handle object graphs with cycles (i.e., a "circle" of objects which all reference each other). The .NET JSON serializer will throw an exception if you pass it an object which contains a circular reference, or refers to another object which does. Since this is largely data-dependent and, worse, can be occasionally masked by lazy loading and the like, the safest way to serialize to JSON is to pass it data which provably can never contain a cycle. Anonymous types work really well for this. I’ll probably elaborate on this point in a future post. For the time being, the important thing is that I need to be able to take any IQueryable and turn it into data suitable for the grid, without having to think about what type that list might contain.

In a nutshell, this is what I want to be able to do:

public JsonResult ListGridData(int page, int rows, string search, string sidx, string sord)
{
    var model = repository.SelectAll().ToJqGridData(page, rows, sidx + " " + sord, search,
      new[] { "Column1", "Column2", "Column3" })
    return Json(model);
}

In this example, repository.SelectAll() returns an IQueryable<TSomething>. I’ve written a method, ToJqGridData, which can transform that IQueryable into data suitable for the grid without having to know what TSomething is. (What the heck is that string array? Don’t worry about that just yet; I’ll cover it in a little bit.) The argument names aren’t what I would have picked, but they come from the grid. There is probably a way to change them, but I’ve never bothered to look.

The key to this is a grid property called jsonReader.repeatitems. I’m not sure why it’s called repeatitems, but here’s what it does. When repeatitems is true (the default), data is returned to the grid in a "cell array," like in Phil’s post. That is, an individual row of data in the JSON looks like this:

{id:"1", cell:["cell11", "cell12", "cell13"]}

When repeatitems is false, the data looks like a regular object in JSON format:

{id:"45678",name:"Speakers",note:"note",stock:"false",ship:"4"}

Interestingly, it turns out that this is the format the .NET JSON serializer already uses. So I don’t have to write any code to get this portion of the data into the right shape. I just hand an object to the JSON serializer and let it do its thing. The only things I need to do to make the data work for the grid is to handle paging, sorting, search, and provide certain other bits of data, like the number of rows.

But, as you may have noticed, I’m lazy. Why write code when someone else has already done it? Rob Conery wrote a decent type for paging eons ago, and Troy Goode enhanced it. So I’m going to use their work instead of re-writing paging. It turns out this type already computes most of what the grid needs to know. And Microsoft has this thing called Dynamic LINQ which can deal with ordering and filtering by property names, mostly. All I have to do is tie this stuff together.

I wrote a type to hold the data which the grid needs. You can actually use an anonymous type for this; the JavaScript grid obviously doesn’t care if the data it receives comes from an anonymous type or not, but because I am writing extension methods to IQueryable, it was convenient for me to have a non-anonymous type so that the extension methods could build on each other. This type represents one page of data requested by the grid:

namespace GridDemo.Models
{
    /// <summary>
    /// This type is designed to conform to the structure required by the JqGrid JavaScript component.
    /// It has all of the properties required by the grid. When this type is serialized to JSON, the resulting
    /// JSON will be in the structure expected by the grid when it fetches pages of data via AJAX calls.
    /// </summary>
    [SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
        Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
    [SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId="Jq",
        Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
    public class JqGridData
    {
        /// <summary>
        /// The number of pages which should be displayed in the paging controls at the bottom of the grid.
        /// </summary>
        public int Total { get; set; }
        /// <summary>
        /// The current page number which should be highlighted in the paging controls at the bottom of the grid.
        /// </summary>
        public int Page { get; set; }
        /// <summary>
        /// The total number of records in the entire data set, not just the portion returned in Rows.
        /// </summary>
        public int Records { get; set; }
        /// <summary>
        /// The data that will actually be displayed in the grid.
        /// </summary>
        public IEnumerable Rows { get; set; }
        /// <summary>
        /// Arbitrary data to be returned to the grid along with the row data. Leave null if not using. Must be serializable to JSON!
        /// </summary>
        public object UserData { get; set; }
    }
}

Note that I’ve used C# capitalization for the property names. This is not what the grid expects. But I have to tell the grid that I’m using a non-default value for repeatitems anyway, so it’s nice to follow C# conventions.

We have lots of grids in our application, so rather than configuring each grid individually, we set defaults for the grid in one place. In a JavaScript file referenced by the Site.Master, this method is called in the jQuery ready event:

    setDefaults: function() {
        $.jgrid.defaults = $.extend($.jgrid.defaults, {
            datatype: 'json',
            height: 'auto',
            imgpath: '/Content/jqGrid/themes/basic/images',
            jsonReader: {
                root: "Rows",
                page: "Page",
                total: "Total",
                records: "Records",
                repeatitems: false,
                userdata: "UserData",
                id: "Id"
            },
            loadui: "block",
            mtype: 'GET',
            multiboxonly: true,
            rowNum: 20,
            rowList: [10, 20, 50],
            url: "ListGridData.json",
            viewrecords: true
        });
    }

In addition to setting repeatitems, telling the grid to expect the capitalization I mentioned above, this code also sets the grid theme and other options we want to be consistent throughout the application. One important property set in the jsonReader property is id. The value supplied is the name of a column in the returned data which contains a unique ID for the row. You can supply any name you want to, and the type of the property can be just about anything serializable, but it is important that the data you return to the grid contains some unique ID. All of the types in our Entity Framework model contain a unique ID called "Id", so we set this in our defaults for the grid. If you don’t have a single operating name for any data you might supply to the grid, you will have to set this value on the individual grids, when you write the JavaScript for the view.

Now I can write a method which takes a PagedList and transforms it into jqGrid data:

        /// <summary>
        /// Converts a paged list into a format suitable for the JqGrid component, when
        /// serialized to JSON. Use this method when returning data that the JqGrid component will
        /// fetch via AJAX. Take the result of this method, and then serialize to JSON. This method
        /// will also apply paging to the data.
        /// </summary>
        /// <typeparam name="T">The type of the element in baseList. Note that this type should be
        /// an anonymous type or a simple, named type with no possibility of a cycle in the object
        /// graph. The default JSON serializer will throw an exception if the object graph it is
        /// serializing contains cycles.</typeparam>
        /// <param name="list">The list of records to display in the grid.</param>
        /// <param name="userData">Arbitrary data to be returned to the grid along with the row data.
        /// Leave null if not using. Must be serializable to JSON!</param>
        /// <returns>A structure containing all of the fields required by the JqGrid. You can then call
        /// a JSON serializer, passing this result.</returns>
        [SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
            Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
        [SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Jq",
            Justification = "JqGrid is the correct name of the grid component we use.")]
        public static JqGridData ToJqGridData<T>(this PagedList<T> list, object userData)
        {
            return new JqGridData()
            {
                Page = list.PageIndex,
                Records = list.TotalItemCount,
                Rows = from record in list
                       select record,
                Total = list.PageCount,
                UserData = userData
            };
        }

That’s getting somewhere, but it’s not yet enough to let me write the action method I showed above. For that, I need to wrap up the PagedList inside the action so that I can pass the arguments from the grid directly, without having to construct a new PagedList inside of every action method which supplies data to a grid. So I build on the method above:

        /// <summary>
        /// Converts a queryable expression into a format suitable for the JqGrid component, when
        /// serialized to JSON. Use this method when returning data that the JqGrid component will
        /// fetch via AJAX. Take the result of this method, and then serialize to JSON. This method
        /// will also apply paging to the data.
        /// </summary>
        /// <typeparam name="T">The type of the element in baseList. Note that this type should be
        /// an anonymous type or a simple, named type with no possibility of a cycle in the object
        /// graph. The default JSON serializer will throw an exception if the object graph it is
        /// serializing contains cycles.</typeparam>
        /// <param name="baseList">The list of records to display in the grid.</param>
        /// <param name="currentPage">A 1-based index indicating which page the grid is about to display.</param>
        /// <param name="rowsPerPage">The maximum number of rows which the grid can display at the moment.</param>
        /// <param name="orderBy">A string expression containing a column name and an optional ASC or
        /// DESC. You can, separate multiple column names as with SQL.</param>
        /// <param name="searchQuery">The value which the user typed into the search box, if any. Can be
        /// null/empty.</param>
        /// <param name="searchColumns">An array of strings containing the names of properties in the
        /// element type of baseList which should be considered when searching the data for searchQuery.</param>
        /// <param name="userData">Arbitrary data to be returned to the grid along with the row data. Leave
        /// null if not using. Must be serializable to JSON!</param>
        /// <returns>A structure containing all of the fields required by the JqGrid. You can then call
        /// a JSON serializer, passing this result.</returns>
        [SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
            Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
        [SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Jq",
            Justification = "JqGrid is the correct name of the grid component we use.")]
        public static JqGridData ToJqGridData<T>(this IQueryable<T> baseList,
            int currentPage,
            int rowsPerPage,
            string orderBy,
            string searchQuery,
            IEnumerable<string> searchColumns,
            object userData)
        {
            var filteredList = ListAddSearchQuery(baseList, searchQuery, searchColumns);
            var pagedModel = new PagedList<T>(filteredList.OrderBy(orderBy), currentPage, rowsPerPage);
            return pagedModel.ToJqGridData(userData);
        }

Now you see that string array (IEnumerable, actually) again. JqGrid supports sorting and searching, and so do I. Like Phil, I suggest using Microsoft Dynamic LINQ in order to take the field names that jqGrid passes and turn them into a sorted, filtered, result set. ListAddSearchQuery is a (longish) method which takes a search string and a list of columns to search and turns them into a Dynamic LINQ expression and applies it to the base list. Unfortunately, search is kind of application-specific. In some applications you might want to search different columns in different ways. Some applications might want "Google-style" searching. It kind of depends upon what you’re doing. So although I will supply the source code for this method when I make the demo project available, I cannot promise that it will be useful for your application. Feel free to substitute your own method here.

It turns out both methods (the one which takes a PagedList and the one which takes an IQueryable) are useful. Generally, I use the method which takes an IQueryable. But every once in a while I want to do something to the data I’m about to supply to the grid before the grid sees it, but after paging, like transform a single page of results in some way. In this case, the action builds up a PagedList, uses that to reduce to one page of data, modifies the results in whatever way is appropriate, and then calls the PagedList extension. I’ve also written additional overloads to both methods which do not have a userData argument, since it is very seldomly used. userData, as the name suggests, is an additional bit of data you can return to the grid with your result set for use in grid event handlers when loading a fresh page of data. Since it’s of type object, you can put as much data in there as you need to, so long as it’s serializable to JSON. In practice, though, we rarely use this feature of the grid.

With these methods, it is easy to write actions as concise as the code I showed at the beginning of this post. Just get a list from somewhere and call ToJqGridData, passing information about which page to display, how to sort, etc.

In a future post, I will tie this all together into a demo application.

{ 18 } Comments

  1. Bill Beckelman | April 15, 2009 at 6:49 am | Permalink

    Great post. I’m really looking forward to the next one and the demo app.

  2. trendbender | April 16, 2009 at 4:53 am | Permalink

    good article, thx

  3. Bryan Reynolds | April 22, 2009 at 2:43 pm | Permalink

    Regarding jgGrid.

    How do you hide a column?

    I have my key column that I want used, but I dont want to show it.

  4. Mads | May 5, 2009 at 1:03 am | Permalink

    Great post :).

  5. Sarah | June 8, 2009 at 9:11 pm | Permalink

    I’m using Linq to Entity and have been fighting to get this functionality in my app for days! Is there a version of PagedList and the other helpers that is known to work with Linq to Entity?

  6. Nithin Mohan T K | July 4, 2009 at 4:47 am | Permalink

    is there any way i can see the full source of this. im bit confused in the case. because in my case i have a LIST of DTO’s(classes with properties) , my data provider is not a database. initially need to fetch from a web svc and keep a cache like or session.

    so would it be possible for you to help me to achieve the same functionality using the same.

    my application is a major store front. latest version we are developing from scratch using MVC

  7. Nithin Mohan T K | July 9, 2009 at 9:14 pm | Permalink

    Thanks Craig; i implemented it like a charm with List , i created a Generic Rpository class and applied.

    Thanks for your efforts.

  8. Chandra Sekhar | August 14, 2009 at 1:09 am | Permalink

    Is there any possible to display a message to user when no search data is found.

    Thanks in advance.

  9. Ad | November 27, 2009 at 1:05 am | Permalink

    Hi Craig,

    Great article and demo, specially the fact that it’s very scalable. I’m however using EF and it gives problems using the PagedList - The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.
    Is this a known issue (that hopefully can be solved) or am I doing something wrong? Sarah pointed out the same problem.

  10. Ted | January 14, 2010 at 7:43 am | Permalink

    When I use the Json serializer it puts quotes around my field names …
    {"FirstName":"John", "LastName":"Smith"}
    How do I serialize my object so that the field names are not in quotes?

    Thanks

    Response: You don’t. The JSON format requires this. If it didn’t add the quotes, then it wouldn’t be JSON.

  11. Ted | January 14, 2010 at 8:35 am | Permalink

    I guess I am missing something. Maybe you can set my straight…Great post by the way!

    In your post you have ..

    When repeatitems is false, the data looks like a regular object in JSON format:

    {id:"45678",name:"Speakers",note:"note",stock:"false",ship:"4"}

    no quotes.

    Response: Well, that’s copied and pasted from the jqGrid documentation. I make no guarantee it’s right. :)

    Here is my example of the return code from my MVC Action Method (public JsonResult GetGridData()) that loads the grid


    //This test works
    var jsonData = new
    {
    totalpages = 20
    ,
    currpage = 1
    ,
    totalrecords = 500
    ,
    invdata = new[]{
    new{Id =23, FirstName = "Ted", LastName="Test"},
    new{Id =19, FirstName = "Mark", LastName="Smith"},
    new{Id =5, FirstName = "Frank", LastName="Johnson"},
    new{Id =46, FirstName = "Bob", LastName="Anderson"}
    }
    };

    //This one doesn’t doesn’t because I have quotes around the field names

    string data = JSONHelper.Serialize<IList>(emps);
    //Where data =
    [{"FirstName":"Bob","Id":1,"LastName":"Smith"},{"FirstName":"mark","Id":2,"LastName":"Johnson"}, {"FirstName":"Steve","Id":3,"LastName":"Anderson"},{"FirstName":"Jim","Id":4,"LastName":"Davis"}]
    //

    var jsonData = new
    {
    totalpages = 20
    ,
    currpage = 1
    ,
    totalrecords = 500
    ,
    invdata = data
    };

    Response: Does my demo solution not work for you? Can’t you just do what my demo project does?

  12. Mhoque | April 10, 2010 at 12:34 pm | Permalink

    I am using MVC 2 and ToJQGridData having some issues. My Data don’t show. With MVC 2 you have to define the JsonRequestBehavior.AllowGet. That being said from my controller if I use the Following the Data shows on the grid

    var jsonData = new
    {
    total = totalPages,
    page = page,
    records = totalRecords,
    rows = new[]{
    new {id = 1, cell = new[] {"1", "Test1", "$99.99", "AGR12345YU1", "$20.00", "VS123UI1"}},
    new {id = 2, cell = new[] {"2", "Test2", "$199.99", "AGR12345YU2", "$24.00", "VS123UI2"}},
    new {id = 3, cell = new[] {"3", "Test3", "$945.99", "AGR12345YU3", "$200.00", "VS123UI3"}}
    }
    };

    On the other hand if I do the following statement, the data doesn’t show.

    var result = repository.Select(crit).Items;
    var jsondata = Json(result.AsQueryable().ToJqGridData(page, rows, null, null, null), JsonRequestBehavior.AllowGet);

    Any Ideas how to fix this?

    Response: If the demo works but your app doesn’t, then either you’ve configured the grid differently, either in terms of your call to jqGrid or setting the grid defaults, or you’re returning invalid JSON.

  13. Jerome | May 14, 2010 at 1:44 am | Permalink

    Hi Craig,

    Nice Post. I’ve been following this along.

    Anyway, I’m stuck at something, I’m using stored procedure with .ToJqGridData(), it returns an exception "The query result cannot be enumerated more than once". I don’t know how to fix this. Appreciate your help.

    Thank you and God Bless.

    Regards,

    Jerome

    Response: You need a completely different method of paging if your data comes from a proc.

  14. Fabrizio | September 6, 2010 at 4:01 pm | Permalink

    Wonderful post, but… repeatitems: false ?
    With small pages is fine, but in cases when you may return hundred of rows, may it be a bad chioce ? repeating names in every row are few bits in a row, but multiply 100, 200 and…

    i’m missing something or it’s a thing to consider ?
    Tks again!

  15. Herman van der Blom | April 4, 2011 at 1:35 pm | Permalink

    Craig, I got errors when I used poco’s in the selects of IQueryable statements. I had properties in classes that could not be translated to SQL. I thought as long as they are not used in the query that does not matter, but this piece of code hits those properties :
    return new JqGridData()
    {
    Page = list.PageIndex,
    Records = list.TotalItemCount,
    Rows = from record in list select record,
    Total = list.PageCount,
    UserData = userData
    };
    I changed the code: Rows = from record in list select record to:
    Rows = list, that works, but what I like to ask you is it right? because I use your code at many places in my webapplication i would like to know if this is right. If I debug your code it iterates over the records. That also seems not to be right, and because you inherites your pagelist from IList and an IList inherites IEnumerate my code seems right. So I hope you can shine some light on this.

  16. peter | January 5, 2012 at 2:23 pm | Permalink

    I have a similar set up with jqgrid being fed by web services back end and linq; Uncanny how much my code is similar to yours - :-)
    Im interested in the part
    var filteredList = ListAddSearchQuery(baseList, searchQuery, searchColumns);
    how did you implement a search strategy to handle any number of search columns operators (and/or) and conditions?

  17. Bob | February 20, 2012 at 3:36 pm | Permalink

    This is an excellent tutorial/starting point/library for interfacing with jqGrid, but I’m running into a similar problem to others - no data displayed even though json data is being returned.

    Background - I am running each of the two downloaded examples AS-IS in VS2010 with MVC 3 installed, so no MVC2 issues or coding errors introduced by me.

  18. vimal | October 7, 2014 at 1:10 am | Permalink

    HI,Nice post …I want to download these code…where it is available to download your demo project?

{ 1 } Trackback

  1. [...] jqGrid with ASP.NET MVC: Introduction Using jqGrid with ASP.NET MVC: LINQ Extensions Using jqGrid with ASP.NET MVC: Finally, A Solution Using jqGrid with ASP.NET MVC: Search and [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

Bad Behavior has blocked 713 access attempts in the last 7 days.

Close