none
Linq Order by Show Null Dates Last RRS feed

  • Question

  • Sorry if I'm not asking in the right forum. I have a list of Trial Dates, some of which are NULL. I want the records with Null Trial Dates to appear last, and have the records with Trial Dates to appear first, in Ascending order.

    I can't get it working. I had another variation of the below statement that almost worked, but it listed the Trial Dates in Descending order, and I need them to be Ascending when one exists.

    Dim entries = db.ChevCalendars.Include(Function(t) t.l_OutsideCounsel).OrderBy(Function(t) t.TrialDate IsNot Nothing).ThenBy(Function(t) t.TrialDate Is Nothing).Select.......

    Monday, March 12, 2018 6:11 PM

Answers

  • Here is a slightly different version to Viorel_ suggestion. The difference in this example is with a nullable field.

    Dim sortedList = custList.
    OrderBy(Function(c) Not c.LastOrderDate.HasValue).
    ThenBy(Function(c) c.LastOrderDate)
    This comes from the following page.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JMANCI Tuesday, March 13, 2018 1:29 PM
    Monday, March 12, 2018 7:44 PM
    Moderator

All replies

  • Try something like this:

    ...OrderBy(Function(t) t.TrialDate Is Nothing).ThenBy(Function(t) t.TrialDate)...

    or this:

    ...OrderBy(Function(t) If(t.TrialDate, DateTime.MaxValue))...


    • Edited by Viorel_MVP Monday, March 12, 2018 6:32 PM
    Monday, March 12, 2018 6:26 PM
  • If you want a solution, than it is probably better to show what kind of type the Trial Dates are. 

    If that is the structure DateTime of .Net, than that cannot be nothing in VB. 

    The word NULL is the most misunderstood word in programming. Originally it was meant for an empty place in memory 000000 or 999999 or FFFFFF.

    Currently there is given other definitions for it. For instance in a SQL type database it means an column which item in a row can be avoided. 

    The VB team decided not to use that confusing word when .Net was created. A NULL item in SQL server can be recognized as DBNull.Value but that can never be set to DateTime.


    Success
    Cor


    Monday, March 12, 2018 7:13 PM
  • Here is a slightly different version to Viorel_ suggestion. The difference in this example is with a nullable field.

    Dim sortedList = custList.
    OrderBy(Function(c) Not c.LastOrderDate.HasValue).
    ThenBy(Function(c) c.LastOrderDate)
    This comes from the following page.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JMANCI Tuesday, March 13, 2018 1:29 PM
    Monday, March 12, 2018 7:44 PM
    Moderator
  • Use the overload of OrderBy which takes a comparer instance.  Then you specify the order in which null values are sorted.  You need to use the correct type for TrialDate.  Since the variable indicates a date and null values are possible, here is an example using the Date? (Nullable(Of Date)) type for TrialDate.  You can change the type and the associated comparison code to suit whatever data type you are actually using.

    .OrderBy(Function(t) t.TrialDate,
             Comparer(Of Date?).Create(
             Function(t1 As Date?, t2 As Date?)
                 If t1 Is Nothing Then
                     If t2 Is Nothing Then
                         Return 0
                     Else
                         Return 1
                     End If
                 End If
                 If t2 Is Nothing Then
                     Return -1
                 End If
                 Return t1.Value.CompareTo(t2.Value)
             End Function))
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, March 12, 2018 7:54 PM
    Moderator
  • Hi JMANCI,

    You can take a look the following same thread:

    https://stackoverflow.com/questions/6461479/linq-order-by-null-column-where-order-is-ascending-and-nulls-should-be-last

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 7:15 AM
    Moderator
  • That works! Thanks so much!!!
    Tuesday, March 13, 2018 1:29 PM