Linq To Sql - Dynamic OrderBy - Case When

Question

Hello,

I'm using Linq to sql and Linq Dynamic OrderBy.

I know linq dynamic can do simple sorting like - orderby("column_name").

But does it support something more complex like queries with "CASE WHEN" in them ?

string orderbyQuery = "(CASE WHEN (username == 100) THEN 1 ELSE 0 END) DESC)";

here is my query :

var u = from u in db.users
        orderby(orderbyQuery)
        select u;

the above example doesn't work! , any idea if its possible? any other way to do it?

thanks

Answer 1

var u = from u in db.users
        orderby u.username == 100 ? 1 : 0 descending
        select u;

Answer 2

This is my current code:

int[] onlineUsers = GetOnlineArray(); // Cache stored array in example {100,101,102,...,...,...,N} 

using (var db = new MyDataContext()) 
{  
   var users = (from u in db.Users 
   where u.u_gender == gender  
   orderby (onlineUsers.Contains(u.u_username)) descending 
   select u.u_username).Skip(startRowIndex).Take(maximumRows).ToList(); 
 } 

This works but its limited to 2100 online users (because linq pass each array item as a different paramter and sql is limited to 2100 param per query).

The reason for the question above is that i wanted to change this query to use dynamic linq for the orderby. so i built a function that return a sql OR string based on the int[] onlineUsers.

in example

string orderbyQuery = "100 == u.u_username || 101 == u.u_username || 102 == u.u_username" 

and the new query is :

using (var db = new MyDataContext()) 
    {  
       var users = (from u in db.Users 
       where u.u_gender == gender  
       orderby (orderByQuery) descending 
       select u.u_username).Skip(startRowIndex).Take(maximumRows).ToList(); 
     } 

but the new query doesn't work.

any idea?