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

Solution 1:

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.

Solution 2:

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.

Solution 3:

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]

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

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

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