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

@PostMan: Yes, it's simple, it achieves the right result, but `OrderByDescending, ThenBy` is clearer. — jason, Jun 23 '11 at 23:30
@Jason, yeah I didn't know the syntax for the `orderby`, and got side tracked looking for it :) — PostMan, Jun 24 '11 at 01:37

score 183 · Accepted Answer · edited Jun 09 '19 at 07:13

183

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.

edited Jun 09 '19 at 07:13

David Silva-Barrera

776
5
12

answered Jun 23 '11 at 22:47

DaveShaw

50,828
16
110
139

30

Common mistake, people do the same with Lamda Syntax - using .OrderBy twice instead of .ThenBy. – DaveShaw Jun 23 '11 at 22:51
3

**This Worked** to order fields with values on top and null fields on bottom i used this : `orderby p.LowestPrice == null, p.LowestPrice ascending` Hope helps someone. – Shaiju T Sep 27 '15 at 11:32
@DaveShaw thank you for the tip - especially the comment one - very tidy - love it – Demetris Leptos Dec 03 '16 at 10:50

score 104 · Answer 2 · edited Jun 18 '21 at 05:45

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.

+1 or just ... don't write the LINQ query syntax :) Good explanation nonetheless — sehe, Jun 23 '11 at 23:12

Jaider · Answer 3 · 2020-10-09T16:37:17.383

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 think this is more readable, without the nasty constant: .OrderBy(f => f.SomeString != null ? 0 : 1).ThenBy(f => f.SomeString) — jornhd, May 04 '16 at 11:44

score 18 · Answer 4 · answered Mar 21 '12 at 06:32

18

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

answered Mar 21 '12 at 06:32

Gurgen Hovsepyan

386
4
18

This can work in scenarios where one wants result with other values like 0 in place of null. – Naresh Ravlani Sep 01 '16 at 09:24
Yes. Just replace null to 0. – Gurgen Hovsepyan Sep 01 '16 at 13:04
this is the only answer that worked for me, the rest kept the nulls in the beginning of the list. – BMills Aug 15 '19 at 10:45

score 12 · Answer 5 · edited Aug 23 '21 at 13:43

12

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

edited Aug 23 '21 at 13:43

Chris Schaller

10,266
3
41
71

answered Oct 25 '13 at 08:03

RTK

193
1
5

For OP requirement it should be assending order and MaxValue _context.Products.OrderBy(p => p.Val ?? float.MaxValue) – Michael Freidgeim Jun 18 '21 at 05:47

score 12 · Answer 6 · edited Jan 01 '19 at 08:20

12

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)

edited Jan 01 '19 at 08:20

Yodacheese

4,427
5
31
40

answered Dec 14 '15 at 11:38

User1

17,202
13
104
183

score 7 · Answer 7 · answered Mar 12 '14 at 22:43

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.

dognose · Answer 8 · 2022-01-28T10:00:41.470

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

score 0 · Answer 9 · edited Jul 09 '18 at 15:43

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

Leonid Minkov · Answer 10 · 2019-12-11T19:14:40.577

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?

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

10 Answers10

Linked

Related