CODEWITHSUNDEEP
linqlinq-to-sqldynamicsql-order-by
dan
dan

Reputation: 93

Linq To Sql - Dynamic OrderBy - Case When

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

Upvotes: 8

Views: 12681

Answers (3)

Tiju John
Tiju John

Reputation: 1180

public class Cat 
{
     public string Name {get;set;}
     public Cat(string name) { Name = name; }
}    

var cats = new List<Cat> { new Cat("cat1"), new Cat("cat2"), new Cat("cat3") };
var customSortExpression = new List<string> { "cat2", "cat1", "cat3" }.CustomSortOrder<Cat>("Name");

//custom sort order, works with EF too //orders cat2,cat1,cat3               
var customOrderCats = cats.AsQueryable().OrderBy(customSortExpression);

when used with EF, this generates sql like the following 

SELECT [c].[Name]
   FROM [Cats] AS [c]
   ORDER BY CASE
       WHEN [c].[Name] = N'cat2'
       THEN 0 ELSE CASE
           WHEN [c].[Name] = N'cat1'
           THEN 1 ELSE CASE
               WHEN [c].[Name] = N'cat3'
               THEN 2 ELSE 3
           END
       END
    END

Here is my extension method CustomSortOrder<TEntity>. 

public static Expression<Func<TEntity, int>> CustomSortOrder<TEntity>(this IList<string> customSortOrderValues, string propName) {

var e = Expression.Parameter(typeof(TEntity), "e");
var prop = typeof(TEntity).GetProperty(propName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
var eDotProp = Expression.MakeMemberAccess(e, prop);
var maxLen = customSortOrderValues.Count;

Expression ElseExpression(IList<string> values) {
    var value = values[0];
    var condition = Expression.Equal(eDotProp, Expression.Constant(value));
    var ifTrue = Expression.Constant(maxLen - values.Count);
    Expression ifFalse ;

    if (values.Count == 1) {
        ifFalse = Expression.Constant(maxLen - values.Count + 1);
    }
    else {
        values.RemoveAt(0);
        ifFalse = ElseExpression(values);
    }
    return Expression.Condition(condition, ifTrue, ifFalse);
}

return Expression.Lambda<Func<TEntity, int>>(
    ElseExpression(customSortOrderValues),
        e);
}

Upvotes: 2

a4bike
a4bike

Reputation: 639

This really works for me:

var queryResult = 
   from o in Source
   orderby (o.ColumnThatSaysOrderByColumn1 ? o.Column1 : o.Column2) descending
   select o

Hope this helps!

Upvotes: 1

Dave D
Dave D

Reputation: 8972

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

Upvotes: 12

Related Questions