Sorting a 1-to-many relationship for a log table for the most recent item

Question

So I have a table, Equipments, that contains assets. There is a table connected to this one in a 1-many relationship that logs each time it is inventoried, by whom, and the location it was inventoried at. It stores these historically so that they can go back and find out who did what, when. My issue is that when I am trying to sort by the InventoryDate, I display the last inventory date on the table and want to sort by that server side.

I use jquery datatables to display the data, and am trying to sort asc/desc depending on the clicks to the sorting arrow. The error is:

'DbSortClause expressions must have a type that is order comparable. Parameter name: key'

            int totalDBRecords = eqp.Count();
            int filteredDBRecords = totalDBRecords;
            int pageSize = dtp.length != -1 ? dtp.length : totalDBRecords;
            int skip = dtp.start;

            string search = dtp.search.value;

            string dir = dtp.order[0].dir.ToUpper();
            //string orderFilter;

            IQueryable<eqp> orderFilter;

            IQueryable<eqp> pageData = eqp;

            //Column Sorting
            switch (dtp.columns[dtp.order[0].column].name)
            {
                case "c1":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.c1)).ThenBy(d => d.c1) : pageData.OrderByDescending(d => d.c1);
                    break;
                case "c2":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.c2)).ThenBy(d => d.c2) : pageData.OrderByDescending(d => d.c2);
                    break;
                case "c3":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.c3)).ThenBy(d => d.c3) : pageData.OrderByDescending(d => d.c3);
                    break;
                case "c4":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.c4)).ThenBy(d => d.c4) : pageData.OrderByDescending(d => d.c4);
                    break;
                case "c5":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.c5)).ThenBy(d => d.c5) : pageData.OrderByDescending(d => d.c5);
                    break;
                default:
                    orderFilter = pageData.OrderByDescending(d => d.c3).ThenByDescending(n => n.c0);
                    break;
            }

            pageData = orderFilter;
            ...
            ...
            filteredDBRecords = pageData.Count();

            pageData = pageData.Skip(skip).Take(pageSize);

            ...
            return new JsonResult() { Data = model, MaxJsonLength = Int32.MaxValue };```

Answer 1

All of the linq OrderBy methods are annoying to reuse in a dynamic way.

First, I would recommend projecting the query to the results you are displaying. Particularly so that you compute d.GovEquipmentInvLogs.Max(x => x.InventoryDate) once, before attempting to sort the results.

Then I'd introduce an extension method to call the .[Order/Then]By[Descending] method based on an argument;

public IOrderedQueryable<T> Order<T, V>(this IQueryable<T> query, Expression<Func<T, V>> key, bool then, bool desc)
    => (desc)
        ? (then ? ((IOrderedQueryable<T>)query).ThenByDescending(key) : query.OrderByDescending(key))
        : (then ? ((IOrderedQueryable<T>)query).ThenBy(key) : query.OrderBy(key));

Now you can tidy up your switch statement considerably. Though you could eliminate that too, if you wished to resort to dynamically creating an expression tree to identify each property. But that's a separate SO question, that already has answers.