Descending order by string field in query
I want to write an EF query that does an ascending or descending order based on a condition. Below is my pseudocode:
var result= q.OrderByDescending(x => x.StatusId == 3)
if( x.StatusId == 3)
then order by x.ReserveDate
if( x.StatusId != 3 )
then order by descending x.LastUpdateDate
How can i do this?
Update
This is not the same as, q = condition ? q.OrderBy(..) : q.OrderByDescending(..)
as stated in the duplicate question, the sort order differs from the value within the string, not the outer query of the flag.
source to share
You can create complex expressions in OrderBy ...
// you might have to give bounding start,end for
// for this query to work correctly...
var end = DateTime.Now;
var start = end.AddYears(-100);
var result = q.OrderBy(
x => x.StatusId == 3 ?
// this will sort by in ascending order
DbFunctions.DiffDays(x.ReserveDate, start) :
// this will sort in descending order
DbFunctions.DiffDays(end, x.LastUpdateDate) );
SQL Generated will be
SELECT
...
...
FROM ( SELECT CASE
WHEN ([Extent2].[StatusId ] = 3)
THEN DATEDIFF (day, @p__linq__0, [Extent1].[ReserveDate])
ELSE
DATEDIFF (day, [Extent1].[LastUpdateDate], @p__linq__1)
END AS [C1]
FROM [dbo].[Table] AS [Extent1]
) AS [Project1]
ORDER BY [Project1].[C1]
source to share
As BoessB comment , this is easiest with two concatenated queries:
var q1 = from x in source
where x.StatusId == 3
order by x.ReserveDate;
var q2 = from x in source
where x.StatusId != 3
order by x.LastUpdateDate descending;
var results = await q1.Concat(q2).ToListAsync();
It might be possible to do this in a single statement if you can create a derived field ( let
clauses help) from ReserveDate
and LastUpdateDate
that sorts correctly. But I would suggest splitting the query to be clearer.
source to share