Sortable JqGrid using LINQ to MySQL (DbLinq) and D

问题:

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

回答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.

回答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.