How to query column name compare with a string?

Question

I want to sort my columns on the base of column name which is a string but unfortunately I couldn't achieve this because OrderByDescending compares something else, but I am sending a string.

public List<DOlead> sortLead(DOuser user, string Item)
    {
        List<DOlead> ObjLead = new List<DOlead>();
        ObjLead = _Context.leads.Where(x => x.is_converted == false).OrderByDescending(Item).ToList();
        return ObjLead;
    }

Kindly Help me out?

You probably might need to look into Dynamic LINQ Query Library - http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library — ramiramilu, Dec 30 '15 at 12:18
basically i want to sort on the base of column name which ll come in a string. so OrderByDescending(x => x.columnName == Item) cann't work. — Noman Ahmad, Dec 30 '15 at 12:23

score 1 · Answer 1 · edited May 23 '17 at 12:07

Use reflection

public List<DOlead> sortLead(DOuser user, string Item)
{    
    var propertyInfo = typeof(DOlead).GetProperty(Item);    
    List<DOlead> ObjLead = new List<DOlead>();
    ObjLead = _Context.leads.Where(x => x.is_converted == false).OrderByDescending(x => propertyInfo.GetValue(x, null)).ToList();
    return ObjLead;
}

Edit After getting comment by SO, and after some research, I came across this answer and decided to modify it for user.

You have to create lambda expression first then pass it to order by clause.

Creating lambda expression.

public static class QueryableHelper
{
    public static IQueryable<TModel> OrderBy<TModel>(this IQueryable<TModel> q, string name)
    {
        Type entityType = typeof(TModel);
        PropertyInfo p = entityType.GetProperty(name);
        MethodInfo m = typeof(QueryableHelper).GetMethod("OrderByProperty").MakeGenericMethod(entityType, p.PropertyType);
        return(IQueryable<TModel>) m.Invoke(null, new object[] { q, p });
    }

    public static IQueryable<TModel> OrderByDescending<TModel>(this IQueryable<TModel> q, string name)
    {
        Type entityType = typeof(TModel);
        PropertyInfo p = entityType.GetProperty(name);
        MethodInfo m = typeof(QueryableHelper).GetMethod("OrderByPropertyDescending").MakeGenericMethod(entityType, p.PropertyType);
        return (IQueryable<TModel>)m.Invoke(null, new object[] { q, p });
    }

    public static IQueryable<TModel> OrderByPropertyDescending<TModel, TRet>(IQueryable<TModel> q, PropertyInfo p)
    {
        ParameterExpression pe = Expression.Parameter(typeof(TModel));
        Expression se = Expression.Convert(Expression.Property(pe, p), typeof(object));
        return q.OrderByDescending(Expression.Lambda<Func<TModel, TRet>>(se, pe));
    }

    public static IQueryable<TModel> OrderByProperty<TModel, TRet>(IQueryable<TModel> q, PropertyInfo p)
    {
        ParameterExpression pe = Expression.Parameter(typeof(TModel));
        Expression se = Expression.Convert(Expression.Property(pe, p), typeof(object));
        return q.OrderBy(Expression.Lambda<Func<TModel, TRet>>(se, pe));
    }
}

Your modified method

public List<DOlead> sortLead(DOuser user, string Item)
    {      
        List<DOlead> ObjLead = new List<DOlead>();
        ObjLead = _Context.leads.Where(x => x.is_converted == false).OrderByDescending(Item).ToList();
        return ObjLead;
    }

thanks Imad Bhai ! But it shows exception "LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object, System.Object[])' method, and this method cannot be translated into a store expression." — Noman Ahmad, Dec 30 '15 at 12:49
Great extension methods. One suggestion, however, would be to have them return IOrderedQueryable rather than IQueryable, as the actual OrderBy does. This allows one to chain with ThenBy, etc. — Jeff, Mar 01 '16 at 16:28

score 0 · Answer 2 · answered Dec 30 '15 at 12:19

From Stack , I guess you could use reflection:

from x in db.TableName
    where (x.GetType().GetProperty(stringCOLUMN_1_or2).GetValue(x, null)) == " 8"
select x;

Not sure there's any easy Linqish way to do that though...

Assuming dynamic Linq will work it would just be:

from x in objets
.Where(stringCOLUMN_1_or2  + " = ' " + 8 + "'")
select x

There is some more info about dynamic Linq with SQL here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

i used x.GetType().GetProperty(Item). it gives exception "LINQ to Entities does not recognize the method 'System.Reflection.PropertyInfo GetProperty(System.String)' method, and this method cannot be translated into a store expression." — Noman Ahmad, Dec 30 '15 at 12:35

score 0 · Answer 3 · answered Dec 30 '15 at 12:46

You should use Expression builder to achieve this example simple with string properties:

public class OrderByData
    {
        public string PropertyName { get; set; }
    }
    public static class ExpressionBuilder
    {
        public static Expression<Func<T, string>> GetExpression<T>(OrderByData filter)
        {

            ParameterExpression param = Expression.Parameter(typeof(T), "t");
            Expression exp = GetExpression<T>(param, filter);
            return Expression.Lambda<Func<T, string>>(exp, param);
        }

        private static Expression GetExpression<T>(ParameterExpression param, OrderByData filter)
        {
            MemberExpression member = Expression.Property(param, filter.PropertyName);
            return member;
        }
    }

and then call like this:

    public List<DOlead> sortLead(DOuser user, string Item)
        {
            List<DOlead> ObjLead = new List<DOlead>();
 new OrderByData { PropertyName = Item };
            var deleg = ExpressionBuilder.GetExpression<lead>(filter).Compile();
            ObjLead = _Context.leads.Where(x => x.is_converted == false).OrderByDescending(deleg).ToList();
            return ObjLead;
        }

also you can extend to use also other types not only strings.

How to query column name compare with a string?

3 Answers3