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

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.