locked
if a date field is possibly null sort how do i? RRS feed

  • Question

  • User1034446946 posted

    Hi

    I have a model which has

    public DateTime? Start {get;set;}

    now I need search where(s => s.Start >= DateTime.Now).OrderByDescending(o => o.Start)

    however i need to make sure any records which have start as null are at the bottom of the list.

    I have search a few things but can't get any that are error free, this has to work while searching models in a controller and also running a query against the Entity Framework or have two seperate statements for both situations.

    Any suggestions would be appriciated.

    Sunday, February 10, 2019 1:20 AM

Answers

  • User-474980206 posted

    because of the where clause:

       where(s => s.Start >= DateTime.Now).OrderByDescending(o => o.Start)

    there will be no null, as a compare to null is always false. if the where clause allows nulls, then to sort null, you need to assign a value (high or low depending where in the sort).

       .OrderByDescending(o => o.Start == null ?  DateTime.Parse("1/1/1753") : o.Start)  -- nulls come last

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 14, 2019 3:34 PM

All replies

  • User1724605321 posted

    Hi EnenDaveyBoy ,

    however i need to make sure any records which have start as null are at the bottom of the list.

    You can try below query to involve the null datetime records:

    .Where(s => s.Start >= DateTime.Now || s.Start  == null).OrderByDescending(o => o.Start).ToList();

    Best Regards,

    Nan Yu

    Monday, February 11, 2019 5:16 AM
  • User1034446946 posted

    Thanks, much appriciated, but does thank make sure the null is at the bottom of the list?

    Thursday, February 14, 2019 1:02 AM
  • User1724605321 posted

    Hi EnenDaveyBoy,

    Thanks, much appriciated, but does thank make sure the null is at the bottom of the list?

    Have you tried the codes ? It was working during my testing . Any problem with your testing ?

    Best Regards,

    Nan Yu

    Thursday, February 14, 2019 1:31 AM
  • User1034446946 posted

    Not yet, I was partly looking for confirmation that a null would be considered the lowest value or the highest for future reference, theres a few examples online of people doing two orders to ensure its at the bottom (also looking for best practise)

    Thursday, February 14, 2019 1:47 AM
  • User1724605321 posted

    Hi EnenDaveyBoy ,

    There is no best practice , That all depends on how do you want to handle your data from database .

    Best Regards,

    Nan Yu

    Thursday, February 14, 2019 2:51 AM
  • User1034446946 posted

    so there no default behaviour for null values in a SQL database?

    Thursday, February 14, 2019 2:40 PM
  • User475983607 posted

    so there no default behaviour for null values in a SQL database?

    The SQL docs clearly explain how null is handled when sorted. 

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

    ASC | DESC
    Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

    You'll need to come up with a design since you do not want the default behavior.  Perhaps filter the query results into two lists; one with dates and one with the null.  Use the AddRange() method to move the null date to the end of the collection.

    https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist.addrange?view=netframework-4.7.2

    Thursday, February 14, 2019 3:19 PM
  • User-474980206 posted

    because of the where clause:

       where(s => s.Start >= DateTime.Now).OrderByDescending(o => o.Start)

    there will be no null, as a compare to null is always false. if the where clause allows nulls, then to sort null, you need to assign a value (high or low depending where in the sort).

       .OrderByDescending(o => o.Start == null ?  DateTime.Parse("1/1/1753") : o.Start)  -- nulls come last

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 14, 2019 3:34 PM
  • User1034446946 posted

    thanks for all the help

    Friday, February 15, 2019 3:02 PM