Error during database connection with jqgrid

Question

I'm trying to connect to database from jqgrid. I have this bug in the controller, does anyone know how to fix it?

Component LINQ to Entities does not recognize the method 'System.String ToString () "and you can not translate it to express the warehouse.

When given data rigidly works. new {id = 1, cell = new[] {"1", "zzzzzz", "xxxxxx"}}

In addition, I would like to ask how to add edit to jqgrid?

View

<asp:Content ID="indexTitle" ContentPlaceHolderID="TitleContent" runat="server">
    Home Page
</asp:Content>

<asp:content contentplaceholderid="HeadContent" runat="server">

    <link href="/Content/jquery-ui-1.8.7.css" rel="stylesheet" type="text/css" />
    <link href="/Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />

    <script src="/Scripts/jquery-1.8.2.min.js" type="text/javascript"></script>
    <script src="/Scripts/js/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>

    <script type="text/javascript">
        $(function () {
            $("#list").jqGrid({
                url: '/Home/LinqGridData/',
                datatype: 'json',
                mtype: 'GET',
            colNames: ['CatID', 'CatName', 'Age'],
            colModel: [
            { name: 'CatID', index: 'CatID', width: 40, align: 'left' },
            { name: 'CatName', index: 'CatName', width: 40, editable: true, align: 'left' },
            { name: 'Age', index: 'Age', width: 400, align: 'left' }],
                pager: jQuery('#pager'),
                rowNum: 10,
                rowList: [5, 10, 20, 50],
                sortname: 'Id',
                sortorder: "desc",
                viewrecords: true,
                imgpath: '/scripts/themes/coffee/images',
                caption: 'My first grid'
            });
        jQuery("#list").jqGrid('navGrid', "#pager", { edit: true, add: true, del: true });
        jQuery("#list").jqGrid('inlineNav', "#pager");
        });
    </script>

</asp:content>

<asp:content contentplaceholderid="MainContent" runat="server">
    <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>

</asp:content>

Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace MvcApplication2.Models
{
    public class Cat
    {
        [Key]
        public int CatID { get; set; }
        public string CatName { get; set; }
        public string Age { get; set; }
    }
}

Controller

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

    var jsonData = new
    {
        total = 1, //todo: calculate
        page = page,
        records = context.Cats.Count(),
        rows = (
            from question in context.Cats
            select new
            {
                i = question.CatID,
                cell = new string[] { question.CatID.ToString(), question.CatName, question.Age }
            }).ToArray()
    };
    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

I think it's because the question for a moment it is sent to the database,

Answer 1

You can't send this:

question.CatID.ToString()

to SQL Server because it doesn't know what to do with the method call. If you force enumeration of the set first you can make use of .NET methods in memory:

from question in context.Cats.ToList()

The problem is going to be balancing performance. If the Cats unfiltered set is large performance will suffer as the entire table is loaded into memory. In your case you probably won't notice a difference as you're invoking ToArray() later anyway.

If possible, use Skip() and Take() to implement paging and keep your sets small.

You should also consider storing the result in a variable so that you can reference it for your rows and records properties rather than hitting the context twice.