Sortable JqGrid using LINQ to MySQL (DbLinq) and Dynamic LINQ - Orderby doesn't work

Question

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

Cubiform

What are "sidx" and "sord"? I assume they're a column name and either "asc" or "desc"? Also, what does ZTRepository.GetAllTickets() return? – Pals

sidx and sord - as you assumed; GetAllTickets() returns IQueryable<Ticket> – Cubiform

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). – Ranita

Answer 1

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.

Ranita

Hi again Oleg ;). Code look interesting, but I got "Lambda Parameter not in scope" exception again. – Cubiform

@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 #3912508. 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. – Ranita

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 :( – Cubiform

@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. – Ranita

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. – Cubiform

Answer 2

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.

Pals

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. – Cubiform