JSON results are returned in a different order than expected

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.

score 1 · Answer 1 · answered Feb 09 '11 at 13:37

1

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.

answered Feb 09 '11 at 13:37

GBPackers

11
1

You are very close to the right answer (and this eventually lead me to the correct answer). See my response for the final solution. I would give you a +1 for helping me out, but, as a Steelers fan, I can't. – JasCav Feb 09 '11 at 16:58
;-) Haha...just kidding. +1. Thanks for the insight! – JasCav Feb 09 '11 at 16:58

score 0 · Answer 2 · edited May 23 '17 at 09:58

0

Try to use the method described here. If you use fields instead of properties in the repository.FindAllPeople() you should look at the commented part of the code where are used FieldInfo and GetField instead of PropertyInfo and GetProperty.

edited May 23 '17 at 09:58

Community

1
1

answered Feb 02 '11 at 20:16

Oleg

220,925
34
403
798

Thanks, but that doesn't seem to solve the problem. (I could be doing something wrong, but I couldn't get it to work.) Something that is interesting is that my data error is consistent. For example: ([1,One], [Two,2], [3,Three], [Four,4]). The names/numbers flip on every other. – JasCav Feb 02 '11 at 22:00
@JasCav: Could you include definition of the class to which has `PersonID` property? Moreover I don't understand from your code why you use `var scenarios = repository.FindAllPeople()...` and then use `from person in **people**` (`people` and not `scenarios`)? One more remark you should replace `i = person.PersonID` to `id = person.PersonID`. Currently `i` will be ignored and as `id` will be used integers like 1,2,3... – Oleg Feb 02 '11 at 22:11
Type on the **scenarios** vs. **people**. Fixed that. I also changed to `id = person.PersonID`, although that doesn't seem to make much difference. As for the class that contains PersonID - it is of type "Person" and is generated using Linq to Entities (EF4). PersonID happens to be an integer (self-incrementing key) in the database. – JasCav Feb 03 '11 at 14:16
@JasCav: From your comment I don't understand whether the problem is fixed or not. About `PersonID` I want only know whether it is a property of a field of the class. Do you tried the sorting from the http://stackoverflow.com/questions/4078592/sortable-jqgrid-using-linq-to-mysql-dblinq-and-dynamic-linq-orderby-doesnt-w/4079121#4079121 after all the changes from **scenarios** to **people**? – Oleg Feb 03 '11 at 14:40
The scenarios to people was just a typo on SO. It was not a problem in my code. PersonID is a property (an integer) within the Person class. (Again, I don't handle this code - it is generated completely by the Entity Framework.) – JasCav Feb 03 '11 at 14:43

score 0 · Accepted Answer · edited May 23 '17 at 11:47

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);

JSON results are returned in a different order than expected

3 Answers3