ASP.Net MVC 3 JQGrid

Question

After reading up on the JQGrid control, I decided it would be good to use it in one of my ASP.Net MVC 3 Web applications.

Firstly I followed Phil Haacks tutorial http://haacked.com/archive/2009/04/14/using-jquery-grid-with-asp.net-mvc.aspx which is all good. I then tried to implement something similar into my app, the only difference being, I use Linq To Entities.

My View page has all the css and Jquery classes imported, then I have my JavaScript Function and table which holds the data

<script type="text/javascript">
jQuery(document).ready(function () {
    jQuery("#list").jqGrid({
        url: '/Home/LinqGridData/',
        datatype: 'json',
        mtype: 'GET',
        colNames: ['equipmentID', 'categoryTitle', 'title'],
        colModel: [
      { name: 'equipmentID', index: 'equipmentID', width: 40, align: 'left' },
      { name: 'categoryTitle', index: 'categoryTitle', width: 40, align: 'left' },
      { name: 'title', index: 'title', width: 200, align: 'left'}],
        pager: jQuery('#pager'),
        width: 660,
        height: 'auto',
        rowNum: 10,
        rowList: [5, 10, 20, 50],
        sortname: 'Id',
        sortorder: "desc",
        viewrecords: true,
        imgpath: '/scripts/themes/coffee/images',
        caption: 'My first grid'
    });
}); 

<h2>My Grid Data</h2>
<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>

Then in my controller, I have the following method which is suppose to return the Json data

public ActionResult LinqGridData(string sidx, string sord, int page, int rows)
    {
        AssetEntities context = new AssetEntities();

        var query = from e in context.Equipments
                    select e;

        var count = query.Count();

        var result = new
        {
            total = 1,
            page = page,
            records = count,
            rows = (from e in query
                    select new
                    {
                        id = e.equipmentID,
                        cell = new string[]
                        {
                        e.equipmentID.ToString(),
                        e.Category.categoryTitle,
                        e.Department.title
                        }

                    }).ToArray()
        };

        return Json(result, JsonRequestBehavior.AllowGet);

    }

When I run this, the code falls over with the following error

LINQ to Entities does not recognize the method 'System.String ToString()' method

Does anyone know how to fix this error? And also, am I doing this the correct way, or should I be doing it a different way from the Phil Haack explanation since he is using Linq to SQL?

Any feedback would be much appreciated.

Thanks Folks.

Answer 1

EF doesn't support ToString method, you must retrieve the data without ToString and format

this should work

public ActionResult LinqGridData(string sidx, string sord, int page, int rows)
{
    AssetEntities context = new AssetEntities();

    var query = from e in context.Equipments
                select e;

    var count = query.Count();

    var result = new
    {
        total = 1,
        page = page,
        records = count,
        rows = query.Select(x => new { x.equipamentID, x.Category.categoryTitle,x.Department.title })
                    .ToList() // .AsEnumerable() whatever
                    .Select(x => new { 
                        id = x.equipamentID,
                        cell = new string[] {
                            x.equipamentID.ToString(),
                            x.categoryTitle,
                            x.title
                        }})
                    .ToArray(),
    };

    return Json(result, JsonRequestBehavior.AllowGet);

}

Answer 2

I will address the issue of inline editing and adding a new row to jqGrid as it applies to ASP.NET MVC 3 and Razor C#. I will also include C# Controller code to populate the grid and save data to the grid. First lets look at how to install jqGrid 4.4.1 in an MVC3 Web Application using the NuGet package manager.

1. Install jQuery 1.7.2 or higher.
2. Install jQuery.UI.Combined.
3. Install jqGrid 4.4.1

You can download jqGrid separately from

http://www.trirand.com/blog/?page_id=6

and the jqGrid documentation can be found at

http://www.trirand.com/jqgridwiki/doku.php

I am not going to test the code in this post but it is based on code that does work. I am going to take the brute force approach to solving the difficult and complex problem of populating a jqGrid from an action method, editing a single row or adding a new editable row, then saving the row to an action method. I am sure that more optimal ways of doing this can be found but this is a good starting point. I am not going to show you how to tweak the appearance of your jqGrid, I will leave that to you. I will be using JSON as the data interchange format between jqGrid and ASP.NET MVC 3. I am not going to addres the issue of deleting a row in the grid.

Lets start with the GET action method in the Controller

public JsonResult GetProduct(int productId = 0)
{
    var productsQuery = dbContext.FirstOrDefault(p => p.ProductId == productId);
    var productsList = new List<Products>();

    // SQL does not understand ToString() so we have to do this or something like it
    foreach(var p in productsQuery)
    {
        var product = new Product{
           ProductId = p.ProductId,
           Product.Name = p.Name,
           Product.Date = p.Date.ToShortDateString()
           // and so on...
        };
        productsList.Add(product);
    }

    // You must build an anonymous object that can then be converted into a 2-dimensional 
    // array formatted for jqGrid, convert it to a 2d array then Json.  Note that all grid 
    // data must be in string format.
    var jsonData = new {
        total = 1,
        page = 1,
        records = productsQuery.Count(),
        rows = productsList.Select(p => new {
            id = p.id.ToString(),
            cell = new string[] {
                p.Name,
                p.Date.ToShortDateString(),
                // and so on...
               }
            }).ToArray();
        };

       return Json(jsonData, JsonRequestBehavior.AllowGet);
}

And the View...

<script type="text/javascript">
    $(document).ready(function () {

        var lastSelectedId;
        var grid = $('#grid');
        grid.jqGrid({
            url: "@Url.Action("GetProducts", "Products")",
            datatype: 'json',
            mtype: 'post',
            colNames: ['ProductId', 'Name', 'Date',
                // and so on...
                ],
            colModel: [
                { name: 'ProductId', index: 'ProductId', editable: false },
                { name: 'Name', index: 'Name', editable: true, edittype: 'text' },
                { name: 'Date', index: 'Date', editable: true, edittype: 'text' }
                // and so on...
                ],
            onSelectRow: function(rowid) {
                if (rowid && rowid !== lastSelectedId) {
                    grid.jqGrid('resotreRow', lastSelectedId);
                    lastSelectedId = rowid;
                }
                grid.jqGrid('editRow', rowid, { keys: true });
            },
            editurl: "@Url.Action("SaveProducts", "Products");
            rownum: [10],
            rowList: [5,10,20,50],
            pager: '#grid_pager',
            sortName: 'Name',
            viewrecords: true,
            gridview: true,
            caption: 'Sample Grid'
    });
    grid.jqGrid('navGrid', '#pager', { edit: false, add: false: del: false,
         refresh: false });
    grid.jqGrid('inlineNav', '#pager', {
        addParams: {
            position: 'first',
            addRowParams: {
                keys: true,
                oneditfunc: onInlineEdit
            }
            add: true,
            edit: false,
            save: false,
            cancel: true
    });
    function onInlineEdit(rowid) {
        // add inline editing functionality here
    }             
</script>
@using (Html.BeginForm("","", FormMethod.Post, new { id = "ProductsForm" }))
{
    <table id="grid">
    </table>
    <div id="pager">
    </div>
}

and then the POST method

[HttpPost]
public JsonResult SaveProduct(FormCollection frm)
{
    Product product;

    if (frm["oper"] == "add")
    {
        product = new Product();
    }
    else
    {
        int productId = Int32.Parse(frm["id"]);
        product = dbContext.Products.FirstOrDefault(p => p.ProductId == productId);
    }

    foreach (var key in frmAllKeys)
    {
        switch(key)
        {
            case "Name":
                product.Name = frm[key];
                break;
            case "Date":
                product.Date = DateTime.Parse(frm[key]);
                break;
            // and so on...
        }
    }

    try
    {
        if (frm["oper"] == "add")
        {
            dbContext.AddObject(product);
        }
        dbContext.SaveChanges();
    }
    catch (Exception ex)
    {
        Debug.WriteLine(exception.StackTrace);
        return Json(false);
    }
    return Json(true);
}

There are better ways to do this but this is a good start. I am not addressing the dynamic grid issue. I am not sure how that could be accomplished. Suffice it to say that a dynamic jqGrid would require a lot more JavaScript and/or C# code. I would take a look at the "grid within a grid" functionality in jqGrid for combining a static grid with a dynamic grid.

I did try to build functionality that would accept the object type, a list of records and generate the jqGrid Array and Json data for the grid without having to do all of the extra work shown above. I think it can be done with reflection but I don't have time to do it right now.

Finally, I also tried to build functionality that would extract the data from the FormCollection and populate an object given only the object type and the FormCollection. Again, I think this can be done using reflection but I dont have time to do it right now. If anyone wants to try to build an MVC3 C# jqGrid Json generator and extractor, I would reccomend that you use the Entity Framework Code First method with POCO classes for your model. POCO classes are much easier to work with than entity objects for such a task.

I hope this helps :)

Answer 3

Look at the code example from the another answer. I hope it will be helpful.

Small remarks:

1. sortname: 'Id' is wrong parameter because you have no column with the name 'Id'. Probably you mean sortname:'equipmentID'.
2. You should remove imgpath: '/scripts/themes/coffee/images' parameter of jqGrid which is depricated.
3. You should remove all attributes excepting id from the HTML code: <table id="list"></table><div id="pager"></div>

Answer 4

Ah, I have found the issue. .ToString doesn't work in LINQ to Entity. Yes, this is weird and IMO very dumb. But that is the core problem. As for a work-around...when JSON serializes things, they end up looking very much like a string anyway, by the time jQuery gets around to reading them. So, essentially, you should be able to completely leave out the .ToString() and it should work.