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.
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]
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.