The sequence in which JSON results are delivered differs from what is intended.

asp.net-mvc-2 dynamic-linq jqgrid json linq-to-entities

Question

I am following Phil Haack's example on using jQuery Grid with ASP.NET MVC. I have it working and it works well...except for one minor problem. When I sort the columns by something other than the ID, the JSON data returned from the server is very...well...wrong. Here's is my Controller method.

[HttpPost]
public ActionResult PeopleData(string sidx, string sord, int page, int rows)
{
    int pageIndex = Convert.ToInt32(page) - 1;
    int pageSize = rows;
    int totalRecords = repository.FindAllPeople().Count();
    int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

    var people = repository.FindAllPeople()
        .OrderBy(sidx + " " + sord)
        .Skip(pageIndex * pageSize)
        .Take(pageSize);

    var jsonData = new
    {
        total = totalPages,
        page = page,
        records = totalRecords,
        rows = (
            from person in people
            select new
            {
                i = person.PersonID,
                cell = new List<string> { SqlFunctions.StringConvert((double) person.PersonID), person.PersonName }
            }
        ).ToArray()
    };

    return Json(jsonData);
}

When I sort by PersonID in the jsGrid table, I get this data back (I just used the name of the current ID as the name - e.g. 1, One; 2, Two, etc.)

{"total":1,"page":1,"records":6,"rows":[{"i":1,"cell":[" 1","One"]},{"i":2,"cell":["         2","Two"]},{"i":3,"cell":["         3","Three"]},{"i":4,"cell":["         4","Four"]},{"i":5,"cell":["         5","Five"]},{"i":6,"cell":["         6","Six"]}]}

When I sort by PersonName, however, every other row has the order (the ID vs. the name) flipped around. So when I show it in the table, the PersonName is in the ID column and the ID is in the person column. Here is the JSON result.

{"total":1,"page":1,"records":6,"rows":[{"i":5,"cell":[" 5","Five"]},{"i":4,"cell":["Four","    4"]},{"i":1,"cell":["         1","One"]},{"i":6,"cell":["Six","      6"]},{"i":3,"cell":["         3","Three"]},{"i":2,"cell":["Two","    2"]}]}

Anybody have any insight into what I've done wrong that causes this to happen?

Update

So, I have learned that, what is happening, is that my array values are flipping for every other item in the array. For example...if I populate my database with:

[A, B, C]

then for every even-numbered result (or odd, if you're counting from 0), my data is coming back:

[C, B, A]

So, ultimately, my JSON row data is something like:

[A, B, C] [C, B, A] [A, B, C] [C, B, A] ...etc

This is always happening and always consistent. I am going a bit crazy trying to figure out what's going on because it seems like it should be something simple.

1
0
2/7/2011 10:54:11 PM

Accepted Answer

I found the solution here: linq to entities orderby strange issue

The issue ultimately stems from the fact that Linq to Entities has trouble handling strings. When I was using the SqlFunctions.StringConvert method, this was incorrectly performing the conversion (although, I must admit that I don't fully understand why the order was then switched around).

In either case, per the above post, the solution for fixing the problem was to do the selection locally so that I could "force" Linq to Entities to work with strings properly. From this, my final code is:

var people = repository.FindAllPeople()
             .OrderBy(sidx + " " + sord)
             .Skip(pageIndex * pageSize)
             .Take(pageSize);

// Due to a problem with Linq to Entities working with strings,
// all string work has to be done locally.
var local = people.AsEnumerable();
var rowData = local.Select(person => new
        {
            id = person.PersonID,
            cell = new List<string> { 
                person.PersonID.ToString(),
                person.PersonName
            }
        }
    ).ToArray();

var jsonData = new
{
    total = totalPages,
    page = page,
    records = totalRecords,
    rows = rowData
};

return Json(jsonData);
0
5/23/2017 11:47:45 AM

Popular Answer

I have the same problem with my data which are INT type. If elements in my queue (A,B,C) are NVARCHAR type I do not have this problem. So problem is obviously in SqlFunction.StringConvert function.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow