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

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 = equipment.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<Equipment> orderFilter;

            IQueryable<Equipment> pageData = equipment;

            //Column Sorting
            switch (dtp.columns[dtp.order[0].column].name)
            {
                case "Serial":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.Serial)).ThenBy(d => d.Serial) : pageData.OrderByDescending(d => d.Serial);
                    break;
                case "Type":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.Type)).ThenBy(d => d.Type) : pageData.OrderByDescending(d => d.Type);
                    break;
                case "LOC":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.LOC)).ThenBy(d => d.LOC) : pageData.OrderByDescending(d => d.LOC);
                    break;
                case "Make":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.Make)).ThenBy(d => d.Make) : pageData.OrderByDescending(d => d.Make);
                    break;
                case "Model":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.Model)).ThenBy(d => d.Model) : pageData.OrderByDescending(d => d.Model);
                    break;
                case "SSP":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.SSP)).ThenBy(d => d.SSP) : pageData.OrderByDescending(d => d.SSP);
                    break;
                case "InventoryDate":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => d.GovEquipmentInvLogs.OrderBy(x => x.InventoryDate).FirstOrDefault()) : pageData.OrderBy(d => d.GovEquipmentInvLogs.OrderByDescending(n => n.InventoryDate).FirstOrDefault());
                    break;
                case "LastInventoriedBy":
                    orderFilter = dir == "ASC" ? pageData.OrderBy(d => string.IsNullOrEmpty(d.LOC)).ThenBy(d => d.LOC) : pageData.OrderByDescending(d => d.LOC);
                    break;
                default:
                    orderFilter = pageData.OrderByDescending(d => d.LOC).ThenByDescending(n => n.Serial);
                    break;
            }

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

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

            DVGovEquipmentInvLogDataTableMapper mapper = new DVGovEquipmentInvLogDataTableMapper(pageData, _unitOfWork);

            DataTableData<JsonDVGovEquipmentInvLogSDTModel> dtModel = new DataTableData<JsonDVGovEquipmentInvLogSDTModel>
            {
                draw = dtp.draw,
                recordsTotal = totalDBRecords,
                recordsFiltered = filteredDBRecords,
                data = mapper.dataIL
            };

            ModelState.Clear();

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

CodePudding user response：

Firstly, statements like this:

IQueryable<Equipment> orderFilter;

IQueryable<Equipment> pageData = equipment;

pageData = orderFilter;

... don't actually do anything, they just make the code harder to read. Setting references to one another doesn't do anything like copy/preserve the data those references are pointing at. This also commonly leads to errors where the code "forgets" which reference it is actually using and chained operations end up overwriting each other leading to unexpected results.

this code also doesn't make much sense:

pageData.OrderBy(d => string.IsNullOrEmpty(d.Serial)).ThenBy(d => d.Serial) : 
pageData.OrderByDescending(d => d.Serial)

You're trying to tell EF to order a string.IsNullOrEmpty() rather than a property on the entity. The fact that a string in your entity might be null and might need to be factored into ordering specially is different than what you've written.

Typically for a conditional sort you would use something like:

switch (dtp.columns[dtp.order[0].column].name)
{
    case "Serial":
        employees = dir == "ASC" 
            ? employees.OrderBy(d => d.Serial) 
            : employees.OrderByDescending(d => d.Serial);
        break;
    // ...
}

If you want to handle #null serial values to go first or last etc. then:

? employees.OrderBy(d => d.Serial ?? "00000") 
: employees.OrderByDescending(d => d.Serial ?? "00000");

... substituting "00000" with a suitable default value.

The next detail would likely be that you would want to be able to sort based on multiple sort criteria, where your example is just considering the first sort criteria. This is a little more tricky since you will want to OrderBy on the first condition then ThenBy on all subsequent ones. There is a slightly hacky work-around to make this simple:

employees = employees.OrderBy(e => 0); // Does nothing, but allows us to `ThenBy` our multiple conditions.

foreach ( var condition in dtp.order)
{
    var isAscending = condition.dir.ToUpper() == "ASC";
    switch (dtp.columns[condition.column].name)
    {
        case "Serial":
            employees = isAscending 
                ? employees.ThenBy(d => d.Serial) 
               : employees.ThenByDescending(d => d.Serial);
        break;
        // ...
    }
}

This way you can pass multiple conditions and append the ordering to the IQueryable.

CodePudding user response：

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.