Merge two fields when one of them will definitely be Null?

• ultrafez

  Grasshopper

  Points: 12

  More actions

  February 23, 2009 at 3:57 pm

  #99479

  I've come across a problem which doesn't seem to be documented anywhere else on any forums.

  What I have in my database (SQL Server 2008) is a table which has, amongst other fields, two fields which for each record one of them has a date in it. For example, I may have this table:

  DropOffDate | DeliverDate

  -------------+-------------

  01/03/2009 |

  | 09/04/2009

  | 03/03/2009

  11/04/2009 |

  What I would like to do is produce using some kind of LINQ expression which can merge these two fields like so:

  MergedDate

  ----------

  01/03/2009

  09/04/2009

  03/03/2009

  11/04/2009

  so I can then sort by this merged field.

  I can then use it in a LINQ expression (VB) such as:

  Dim matches = From order In DBDataContext.Orders _

  Order By mergedField _

  Select order

  I expect there is quite an easy way to do this that I just can't figure out. Can anybody please help?

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  February 23, 2009 at 4:09 pm

  #949060

  Well, I am not proficient with LINQ, but in T-SQL, we would do it like this:

  Select COALESCE(DropOffDate, DeliverDate) as MergedDate

  From order

  Order By MergedDate

  Assuming that your blank date fields are NULL's.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• ultrafez

  Grasshopper

  Points: 12

  More actions

  February 27, 2009 at 1:22 pm

  #951373

  Thanks for your reply, however I can't replicate the Coalesce function in LINQ, which is unfortunate as that would have been the perfect solution. Instead I opted to create a View that was almost an exact duplicate of the original table, with an additional formula-based column that used the Coalesce function in the formula to combine the two columns into one.

  I hope this helps anyone who has the same problem as I did.