none
Order By with Nullable Dates RRS feed

  • Question

  • Hey all. This *should* be an easy one to solve. I have a DB with two dates (CreatedDate and UpdatedDate). I want to retrieve the results ordered by the date last updated so the logic should really be:

    IIF(UpdatedDate is nothing then CreatedDate else UpdatedDate)

    However, when I do this, I get the "System Type not supported) - because the UpdatedDate field is nullable.

    My code looks like this:

            Dim oRows = (From rMain In oTable Select rMain, _

            ' , CustomSort = New With {.Date = IIf(rMain.UpdatedDate Is Nothing, rMain.CreatedDate, rMain.UpdatedDate)} _

                    Where rMain.SessionID = Me.ID)

     

            oRows.OrderBy(Function(p) p.CustomSort)


    Now, I did some searching around and found a bunch of examples that should resolve this ( which is where I ended up on my code) but they simply aren't.

    I'm obviously missing something basic. Can anyone help?

    Thanks


    Andrew MacNeill (MVP VFP)
    Wednesday, August 4, 2010 4:23 PM

Answers

  • Hi Andrew,

    The IIF method returns two separate types: DateTime, Nullable.  Translation to SQL does not support different return types. However, you can try to use the If function, it will be translated to CASE statement in the SQL statement. And I think you want to order by the date, but not the AnonymousType, so you ‘d better change the OrderBy clause too.

    Please try this:

    Dim oRows = From rMain In db.oTables Select rMain _
    , CustomSort = New With {.Date = If(rMain.UpdatedDate Is Nothing, rMain.CreatedDate, rMain.UpdatedDate)}
    Where rMain.SessionID = Me.ID
    
    Dim results = oRows.OrderBy(Function(p) p.CustomSort.Date)
    
    


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 5, 2010 5:26 AM
    Moderator

All replies

  • I don't use VB, but this how I do in c#
    
    Date = rMain.UpdatedDate.HasValue ? rMain.UpdatedDate.Value : rMain.CreatedDate.Value
    
    Wednesday, August 4, 2010 6:13 PM
  • Hi Andrew,

    The IIF method returns two separate types: DateTime, Nullable.  Translation to SQL does not support different return types. However, you can try to use the If function, it will be translated to CASE statement in the SQL statement. And I think you want to order by the date, but not the AnonymousType, so you ‘d better change the OrderBy clause too.

    Please try this:

    Dim oRows = From rMain In db.oTables Select rMain _
    , CustomSort = New With {.Date = If(rMain.UpdatedDate Is Nothing, rMain.CreatedDate, rMain.UpdatedDate)}
    Where rMain.SessionID = Me.ID
    
    Dim results = oRows.OrderBy(Function(p) p.CustomSort.Date)
    
    


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 5, 2010 5:26 AM
    Moderator
  • Hi Meidi,

    I only *wish* this client would let things be done in C# - that's where all the good examples are.


    Andrew MacNeill (MVP VFP)
    Thursday, August 5, 2010 10:21 AM
  • Thanks Alex - that was it. IIF vs IF. Go figure!!!! 

    To re-iterate for any readers, the solution was to use IF ( rMain.UpdatedDate .... 

    and NOT IIF (rMain.UpdatedDate....

     

    One more reason to hate languages bloated with superfluous functions !

    Thanks again Alex!


    Andrew MacNeill (MVP VFP)
    Thursday, August 5, 2010 10:29 AM