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

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

Try putting both columns in the same orderby.

orderby p.LowestPrice.HasValue descending, p.LowestPrice

Otherwise each orderby is a separate operation on the collection re-ordering it each time.

This should order the ones with a value first, "then" the order of the value.

It really helps to understand the LINQ query syntax and how it is translated to LINQ method calls.

It turns out that

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

will be translated by the compiler to

var products = _context.Products
                       .Where(p => p.ProductTypeId == 1)
                       .OrderByDescending(p => p.LowestPrice.HasValue)
                       .OrderBy(p => p.LowestPrice)
                       .Select(p => p);

This is emphatically not what you want. This sorts by Product.LowestPrice.HasValue in descending order and then re-sorts the entire collection by Product.LowestPrice in descending order.

What you want is

var products = _context.Products
                       .Where(p => p.ProductTypeId == 1)
                       .OrderByDescending(p => p.LowestPrice.HasValue)
                       .ThenBy(p => p.LowestPrice)
                       .Select(p => p);

which you can obtain using the query syntax by

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

For details of the translations from query syntax to method calls, see the language specification. Seriously. Read it.

The solution for string values is really weird:

.OrderBy(f => f.SomeString == null).ThenBy(f => f.SomeString) 

The only reason that works is because the first expression, OrderBy(), sort bool values: true/false. false result go first follow by the true result (nullables) and ThenBy() sort the non-null values alphabetically.

e.g.: [null, "coconut", null, "apple", "strawberry"]
First sort: ["coconut", "apple", "strawberry", null, null]
Second sort: ["apple", "coconut", "strawberry", null, null]

So, I prefer doing something more readable such as this:

.OrderBy(f => f.SomeString ?? "z")

If SomeString is null, it will be replaced by "z" and then sort everything alphabetically.

NOTE: This is not an ultimate solution since "z" goes first than z-values like zebra.

UPDATE 9/6/2016 - About @jornhd comment, it is really a good solution, but it still a little complex, so I will recommend to wrap it in a Extension class, such as this:

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

And simple use it like:

var sortedList = list.NullableOrderBy(f => f.SomeString);

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

my decision:

Array=_context.Products.OrderByDescending(p=>p.Val ?? float.MaxValue)

> This will treat a NULL value as float.MaxValue for the sorting only, which will put nulls at the end of the list, allowing us to order ascending excluding the nulls

I was trying to find a LINQ solution to this but couldn't work it out from the answers here.

My final answer was:

.OrderByDescending(p => p.LowestPrice.HasValue).ThenBy(p => p.LowestPrice)

This is what I came up with because I am using extension methods and also my item is a string, thus no .HasValue:

.OrderBy(f => f.SomeString == null).ThenBy(f => f.SomeString)

This works with LINQ 2 objects in memory. I did not test it with EF or any DB ORM.

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     

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

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: https://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework/4262617#4262617