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

Question

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

Answer 1

my decision:

Array=_context.Products.OrderBy(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

Answer 2

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     

Answer 3

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.

Answer 4

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

Answer 5

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?

Answer 6

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.

Answer 7

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)

Answer 8

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

Answer 9

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.

Answer 10

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