c# Subsonic 2.1 : Order by case

Question

Hi All,

I would like to know if its possible to order by a specific ID in subsonic 2.1. Same as regular sql query:

ORDER BY 
CASE  
WHEN ID = 5 then 1 
WHEN ID = 10 then 2 
WHEN ID = 1 then 3 
WHEN ID = then 4 
else 5 
END 

I hope someone can help me, I want this functionality for country specific content.

Kind regards, Mark

Answer 1

You can do the following:

Dim q As SubSonic.SqlQuery =
    New SubSonic.Select().From(MyTable.Schema)
    .OrderAsc("case when ID = 5 then 1 when ID = 10 then 2 else 5 end")
Dim foo = q.ExecuteAsCollection(Of MyTableCollection)()

I don't think there is any SubSonic native, type-safe syntax (i.e. Case().When()...), but this works.