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

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

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.

Tags:

C#

Linq

Sorting

Recent Posts