LINQ order by null column where order is ascending and nulls should be last

sf_43603 0
Viewed 123858

I'm trying to sort a list of products by their price.

The result set needs to list products by price from low to high by the column LowestPrice. However, this column is nullable.

I can sort the list in descending order like so:

var products = from p in _context.Products
   where p.ProductTypeId == 1
   orderby p.LowestPrice.HasValue descending
   orderby p.LowestPrice descending
   select p;

// returns:    102, 101, 100, null, null

However I can't figure out how to sort this in ascending order.

// i'd like: 100, 101, 102, null, null
sorting linq c#
10 Answers

I have another option in this situation. My list is objList, and I have to order but nulls must be in the end. my decision:

var newList = objList.Where(m=>m.Column != null)
                     .OrderBy(m => m.Column)
                     .Concat(objList.where(m=>m.Column == null));
user_8726250

Another Option (was handy for our scenario):

We have a User Table, storing ADName, LastName, FirstName

  • Users should be alphabetical
  • Accounts with no First- / LastName as well, based on their ADName - but at the end of the User-List
  • Dummy User with ID "0" ("No Selection") Should be topmost always.

We altered the table schema and added a "SortIndex" Column, which defines some sorting groups. (We left a gap of 5, so we can insert groups later)

ID | ADName |      First Name | LastName | SortIndex
0    No Selection  null         null     | 0
1    AD\jon        Jon          Doe      | 5
3    AD\Support    null         null     | 10     
4    AD\Accounting null         null     | 10
5    AD\ama        Amanda       Whatever | 5

Now, query-wise it would be:

SELECT * FROM User order by SortIndex, LastName, FirstName, AdName;

in Method Expressions:

db.User.OrderBy(u => u.SortIndex).ThenBy(u => u.LastName).ThenBy(u => u.FirstName).ThenBy(u => u.AdName).ToList();

which yields the expected result:

ID | ADName |      First Name | LastName | SortIndex
0    No Selection  null         null     | 0
1    AD\jon        Jon          Doe      | 5
5    AD\ama        Amanda       Whatever | 5
4    AD\Accounting null         null     | 10
3    AD\Support    null         null     | 10
user_10575470

Below is extension method to check for null if you want to sort on child property of a keySelector. 

public static IOrderedEnumerable<T> NullableOrderBy<T>(this IEnumerable<T> list, Func<T, object> parentKeySelector, Func<T, object> childKeySelector)
{
    return list.OrderBy(v => parentKeySelector(v) != null ? 0 : 1).ThenBy(childKeySelector);
}

And simple use it like:

var sortedList = list.NullableOrderBy(x => x.someObject, y => y.someObject?.someProperty);
user_9705700

Here is another way:

//Acsending
case "SUP_APPROVED_IND": qry =
                            qry.OrderBy(r => r.SUP_APPROVED_IND.Trim() == null).
                                    ThenBy(r => r.SUP_APPROVED_IND);

                            break;
//….
//Descending
case "SUP_APPROVED_IND": qry =
                            qry.OrderBy(r => r.SUP_APPROVED_IND.Trim() == null).
                                    ThenByDescending(r => r.SUP_APPROVED_IND); 

                            break;

SUP_APPROVED_IND is char(1) in Oracle db.

Note that r.SUP_APPROVED_IND.Trim() == null is treated as trim(SUP_APPROVED_IND) is null in Oracle db.

See this for details: How can i query for null values in entity framework?

user_69337660
Related