How can you coalesce a datetimeoffset in Linq to SQL?

Question

I have a Linq query that orders by a datetimeoffest. The goal is to have the one that is NULL at the top, followed by most recent, 2nd recent, and so on. I started with this.

orderby item.Date descending

Doing it this way the NULLs go to the bottom. So I changed it to this.

orderby (item.Date.HasValue ? item.Date.Value.Ticks : long.MaxValue) descending

That works for in memory queries, but doesn't translate to SQL. My latest attempt is this.

orderby (item.Date.HasValue ? item.Date : new DateTimeOffset(new DateTime(9999, 09, 31))) descending

The issue here is the max datetimeoffset is not the same between SQL and C#. I feel like I am missing an obvious easy solution.

Any input?

Answer 1

When using a datetime I noticed you couldn't use DateTime.Max and pass that to SQL. Seems the max datetime in SQL is lower than C#. I assumed the same applied in DateTimeOffset. That does not appear to be true. It would appear this code works.

orderby (item.Date.HasValue ? item.Date : DateTimeOffset.MaxValue) descending

Answer 2

Have you tried C#'s coalesce operator?

orderby (item.Date ?? DateTimeOffset.MaxValue) descending