CODEWITHSUNDEEP
c#sql-serverlinqsql-order-by
Simon C
Simon C

Reputation: 9508

Order by date in different directions

I have a booking system and a page showing a user's bookings - past and future.

I want to order the bookings with future bookings first, ordered with the next session at the top (asc), then past bookings, beginning with the latest session (desc).

I currently have this: 

from sess in Sessions
    orderby sess.SessionDate < DateTime.Now.Date, sess.SessionDate
    select sess

But that gives both sets in asc order. Any way to conditionally sort after the first order by clause?

I've also considered using IQueryable<T>.Concat. Would that be the way to go? I assume that results in a Union of some sort, but can I guarantee it would keep the sort order for both sets?

e.g. results: Sessions

2015-03-01 - some class
2015-03-15 - a different class
2015-04-10 - also a class
(the split between past and future sessions)
2014-12-22 - some tutorial
2014-11-15 - some sort of class
2014-10-08 - some other class

Upvotes: 1

Views: 277

Answers (2)

Aducci
Aducci

Reputation: 26664

You can use a ternary operator combined with a DateDiff function for the second order by. This will allow you to swap DateTime.Now and your SessionDate

let isSessionDateLessThanNow = sess.SessionDate < DateTime.Now
orderby isSessionDateLessThanNow, 
        isSessionDateLessThanNow ? 
           SqlFunctions.DateDiff("s", sess.SessionDate, DateTime.Now) :
           SqlFunctions.DateDiff("s", DateTime.Now, sess.SessionDate)

Upvotes: 3

loiti
loiti

Reputation: 168

What about split it into two separate queries, first one with future sessions order by session date ascending. add them to a list. an then another query with past sessions order by session date descending. add them to the same list.

i don't see any chance to solve this i one query without any mess.

Upvotes: 0

Related Questions