How to ignore 'where' and 'order by' condition if the column is null in LINQ

Question

I have list of transaction objects and want to order them by certain condition depending on view user is currently on.

The problem I have is that in order to add a condition in a where clause, first I need to check if it is null or not to prevent null pointer exception. This causes records with the column null being filtered out(and I want to include them at bottom of the list).

How can I modify the query so that it ignores the conditions(where and order by) if that column is null and still append them to the result set?

This is one example query:

transactions = transactions
                    .Where(t => t.PurchaseRequisition != null && 
                    t.Award != null && t.PurchaseRequisition.RequisitionedBy != null)
                    .OrderBy(t => t.Award.ContractNumber).
                    ThenBy(t => ToSafeString(t.Award.ContractNumber)).
                    ThenBy(t => ToSafeString(t.PurchaseRequisition.RequisitionedBy.FullName));


public string ToSafeString(string s)
{
    return s ?? String.Empty;
}

// I want records where PurchaseRequisition or Award is null to be appended to the result set.

Answer 1

You simply need to modify your OrderBy and ThenBy clauses:

.OrderBy(t => t.Award == null || t.Award.ContractNumber == null)
.ThenBy(t => t.Award == null ? "" : ToSafeString(t.Award.ContractNumber))
.ThenBy(t => t.PurchaseRequisition == null ? "" 
             : ToSafeString(t.PurchaseRequisition.RequisitionedBy.FullName));

Now you can completely remove the Where clause.