I'm having some trouble with Dynamic Linq and I was hoping that someone could help. The problem I'm facing is that I need to order a list in a specific sequence. In SQL I would write this as:
ORDER BY CASE WHEN Blah = 'blah' THEN 1 ELSE 0 END
How would I accomplish this with Dynamic Linq?
I've tried something like:
listToSort.AsQueryable().OrderBy("CASE WHEN Blah = 'blah' THEN 1 ELSE 0 END")
But this returns an error
No property or field 'CASE' exists in type
This code is generated at runtime as a list of expressions. So I'm using Dynamic Linq to make use of the expessions as a string.
You can use the conditional operator:
listToSort.AsQueryable() .OrderBy(x => x.Blah == "blah" ? 1 : 0);
You could also use this since a
true is "more" than a
listToSort.AsQueryable() .OrderBy(x => x.Blah == "blah");
It seems that in dynamic LINQ
CASE or the conditional operator are not supported. But there is another keyword you can use:
listToSort.AsQueryable() .OrderBy("iif(Blah = 'blah', 1, 0)");
Overview of the dynamic LINQ expression methods and keywords.
A bit old but an interesting question.
Tim sent us to the right direction: we can use nested iif statements to work like a case statement.
My working example: I want to first order by the items which have demand and workers, then by the ones which have only demand, then by the ones which have only workers:
iif(Workers.Count > 0 AND Demands.Count > 0, 3, iif(Demands.Count > 0, 2, iif(Workers.Count > 0, 1 , 0)))