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

User · Answer

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   gt  p LowestPrice HasValue  ThenBy p   gt  p LowestPrice

User · Answer

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

User · Answer

The solution for string values is really weird   OrderBy f   gt  f SomeString    null  ThenBy f   gt  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   quot coconut quot   null   quot apple quot    quot strawberry quot   First sort    quot coconut quot    quot apple quot    quot strawberry quot   null  null  Second sort    quot apple quot    quot coconut quot    quot strawberry quot   null  null    So  I prefer doing something more readable such as this   OrderBy f   gt  f SomeString     quot z quot    If SomeString is null  it will be replaced by  quot z quot  and then sort everything alphabetically  NOTE  This is not an ultimate solution since  quot z quot  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 lt T gt  NullableOrderBy lt T gt  this IEnumerable lt T gt  list  Func lt T  string gt  keySelector                return list OrderBy v   gt  keySelector v     null   0   1  ThenBy keySelector            And simple use it like  var sortedList   list NullableOrderBy f   gt  f SomeString

User · Answer

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 descending                select p    will be translated by the compiler to  var products    context Products                         Where p   gt  p ProductTypeId    1                          OrderByDescending p   gt  p LowestPrice HasValue                          OrderByDescending p   gt  p LowestPrice                          Select p   gt  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   gt  p ProductTypeId    1                          OrderByDescending p   gt  p LowestPrice HasValue                          ThenBy p   gt  p LowestPrice                          Select p   gt  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

User · Answer

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   gt  f SomeString    null  ThenBy f   gt  f SomeString    This works with LINQ 2 objects in memory   I did not test it with EF or any DB ORM

User · Answer

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   gt  u SortIndex  ThenBy u   gt  u LastName  ThenBy u   gt  u FirstName  ThenBy u   gt  u AdName  ToList      which yields the expected result    ID   ADName        First Name   LastName   SortIndex 0    No Selection  null         null       0 5    AD ama        Amanda       Whatever   5 1    AD jon        Jon          Doe        5 4    AD Accounting null         null       10 3    AD Support    null         null       10

User · Answer

Here is another way     Acsending case  SUP APPROVED IND   qry                               qry OrderBy r   gt  r SUP APPROVED IND Trim      null                                       ThenBy r   gt  r SUP APPROVED IND                                break           Descending case  SUP APPROVED IND   qry                               qry OrderBy r   gt  r SUP APPROVED IND Trim      null                                       ThenByDescending r   gt  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

User · Answer

my decision   Array    context Products OrderByDescending p   gt  p Val    float MinValue

User · Answer

Below is extension method to check for null if you want to sort on child property of a keySelector    public static IOrderedEnumerable lt T gt  NullableOrderBy lt T gt  this IEnumerable lt T gt  list  Func lt T  object gt  parentKeySelector  Func lt T  object gt  childKeySelector        return list OrderBy v   gt  parentKeySelector v     null   0   1  ThenBy childKeySelector       And simple use it like   var sortedList   list NullableOrderBy x   gt  x someObject  y   gt  y someObject  someProperty

User · Answer

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  gt m Column    null                        OrderBy m   gt  m Column                        Concat objList where m  gt m Column    null

[c#] LINQ order by null column where order is ascending and nulls should be last

Examples related to c#

Examples related to linq

Examples related to sorting