1

I've got problem with sorting entries in JqGrid. Orderby seem to not work. I set breakpoint in code and I noticed, that orderby doesn't change order of elements. Any idea what could be wrong?

I'm using LINQ to SQL with MySQL (DbLinq project).

My action code:

public ActionResult All(string sidx, string sord, int page, int rows)
        {
            var tickets = ZTRepository.GetAllTickets().OrderBy(sidx + " " + sord).ToList();
            var rowdata = (
                from ticket in tickets
                select new {
                    i = ticket.ID,
                    cell = new String[] {
                        ticket.ID.ToString(), ticket.Hardware, ticket.Issue, ticket.IssueDetails, ticket.RequestedBy, ticket.AssignedTo, ticket.Priority.ToString(), ticket.State
                    }
                }).ToArray();

            var jsonData = new
            {
                total = 1, // we'll implement later 
                page = page,
                records = tickets.Count(),
                rows = rowdata
            };

            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
mlusiak
  • 1,054
  • 14
  • 28
  • What are "sidx" and "sord"? I assume they're a column name and either "asc" or "desc"? Also, what does ZTRepository.GetAllTickets() return? – PatrickSteele Nov 02 '10 at 15:20
  • sidx and sord - as you assumed; GetAllTickets() returns IQueryable – mlusiak Nov 02 '10 at 15:41
  • I fixed some errors in the code (see my answer). I pasted a code example from my application and replaced not all variables to youth. I hope now I made all changes needed. Moreover don't forget to use `id = ticket.ID` instead of `i = ticket.ID` in your old code (see the code below). – Oleg Nov 02 '10 at 17:09

2 Answers2

2

Try with the following

public ActionResult All(string sidx, string sord, int page, int rows)
{
    IQueryable<Ticket> repository = ZTRepository.GetAllTickets();
    int totalRecords = repository.Count();

    // first sorting the data as IQueryable<Ticket> without converting ToList()
    IQueryable<Ticket> orderdData = repository;
    System.Reflection.PropertyInfo propertyInfo =
        typeof(Ticket).GetProperty (sidx);
    if (propertyInfo != null) {
        orderdData = String.Compare(sord,"desc",StringComparison.Ordinal) == 0 ?
            (from x in repository
             orderby propertyInfo.GetValue (x, null) descending
             select x) :
            (from x in repository
             orderby propertyInfo.GetValue (x, null)
             select x);
    }
    // if you use fields instead of properties, then one can modify the code above
    // to the following
    // System.Reflection.FieldInfo fieldInfo =
    //         typeof(Ticket).GetField (sidx);
    // if (fieldInfo != null) {
    //  orderdData = String.Compare(sord,"desc",StringComparison.Ordinal) == 0 ?
    //      (from x in repository
    //       orderby fieldInfo.GetValue (x, null) descending
    //       select x) :
    //      (from x in repository
    //       orderby fieldInfo.GetValue (x, null)
    //       select x);
    //}

    // paging of the results
    IQueryable<Ticket> pagedData = orderdData
        .Skip ((page > 0? page - 1: 0) * rows)
        .Take (rows);

    // now the select statement with both sorting and paging is prepared
    // and we can get the data
    var rowdata = ( from ticket in tickets
                    select new {
                        id = ticket.ID,
                        cell = new String[] {
                            ticket.ID.ToString(), ticket.Hardware, ticket.Issue,
                            ticket.IssueDetails, ticket.RequestedBy,
                            ticket.AssignedTo, ticket.Priority.ToString(),
                            ticket.State
                        }
                    }).ToList();                

    var jsonData = new {
        total = page,
        records = totalRecords,
        total = (totalRecords + rows - 1) / rows,
        rows = pagedData
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

Here I suppose that the type of your ticket object is Ticket.

Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Hi again Oleg ;). Code look interesting, but I got "Lambda Parameter not in scope" exception again. – mlusiak Nov 03 '10 at 14:47
  • @kMike: It must be a small error in your code. The code which I posted you is a small modification from the code from another answer http://stackoverflow.com/questions/3912008/jqgrid-does-not-populate-with-data/3914796#3914796. If you want I can place the whole project on the web and paste the url to you. So you can look at a working code and find the error in your code. – Oleg Nov 03 '10 at 15:09
  • Hi. I Took two days off form this problem to work on something else, but today I got back to it. I managed to fixed Lambda error, but there seem to be problem with reflection code, you provided. System.Reflection.PropertyInfo propertyInfo = typeof(Ticket).GetProperty(sidx); It always returns null. I don't know why :( – mlusiak Nov 05 '10 at 13:46
  • @kMike: Is the value of `sidx` in the case is the name of some property of `Ticket` class? You can download the test application www.ok-soft-gmbh.com/jqGrid/WfcToJqGrid.zip and compare with your application. – Oleg Nov 05 '10 at 13:54
  • Hi. I finally figure out, what was wrong. There where some issues with lower/upper cases. Thanks for your help and patience. I end up using most of your code. I just left the paging support, because I don't need it. – mlusiak Nov 09 '10 at 14:13
0

The tickets variable is ordered, but then you use that as the source for another query which is NOT ordered so it's order is undetermined. You want the orderby on the second LINQ query.

PatrickSteele
  • 14,489
  • 2
  • 51
  • 54
  • Actually, tickets variable is not ordered. I've set a breakpoint and looked in debugger. I'm affraid, there may be some problem with DbLinq Linq to MySQl implementation. It lacks some other features too. I was also trying to incorporate everything in one query, but got "Lambda Parameter not in scope" exception. – mlusiak Nov 02 '10 at 15:03