Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have the following

Public class Pupil
{
public string Name {get;set;}
public string Gender {get;set;}
public string Standard {get;set;}
}


Name        Gender        Standard
----------------------------------
phani        Male          First
Abraham      Male         Second


How to write optimized linq query based on the column heading which you had clicked
db.Pupil.OrderBy(x => x.Name).Select(x => x);
Posted
Updated 29-Apr-15 22:07pm
v2
Comments
Maciej Los 30-Apr-15 3:09am
   
What have you tried? Where are you stuck?

BTW: Select(x=>x) is unnecessary!
Rate this:
Please Sign up or sign in to vote.

Solution 3

public static IQueryable<t> OrderByDynamic<t>(this IQueryable<t> query, string sortColumn, bool descending)
{
    // Dynamically creates a call like this: query.OrderBy(p =&gt; p.SortColumn)
    var parameter = Expression.Parameter(typeof(T), "p");

    string command = "OrderBy";

    if (descending)
    {
        command = "OrderByDescending";
    }

    Expression resultExpression = null;

    var property = typeof(T).GetProperty(sortColumn);
    // this is the part p.SortColumn
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);

    // this is the part p =&gt; p.SortColumn
    var orderByExpression = Expression.Lambda(propertyAccess, parameter);

    // finally, call the "OrderBy" / "OrderByDescending" method with the order by lamba expression
    resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { typeof(T), property.PropertyType },
       query.Expression, Expression.Quote(orderByExpression));

    return query.Provider.CreateQuery<t>(resultExpression);
}

call the above method
var Pupil = from s in db.Pupil select s;
Pupil = SortExtension.OrderByDynamic(Pupil, sortfield,false);

here Pupil is Iqueryable
   
v2
Comments
Maciej Los 1-May-15 12:56pm
   
Sascha mentioned that this is the only solution which works, so +5
Rate this:
Please Sign up or sign in to vote.

Solution 2

add System.Linq.Dynamic.cs [^] file to your solution and use the below code to do dynamic order by
references : dynamic-query-library[^]


static void Main(string[] args)
        {

            List<Pupil> list = new List<Pupil>();
            list.Add(new Pupil() { Name = "b" });
            list.Add(new Pupil() { Name = "c" });
            list.Add(new Pupil() { Name = "a" });


            var data = OrderByQuery(list, "Name", false);


         }

        public static List<Pupil> OrderByQuery(List<Pupil> data, string columnName, bool isAscending )
        {
            string sort = isAscending ? "ascending" : "descending";
            var source = data.AsQueryable();
            return source.OrderBy(columnName + " " + sort).ToList();
        }


refer Maciej Los solution, its simple and better
   
v3
Comments
Maciej Los 30-Apr-15 5:01am
   
There's nothing like "simplest and better" ;)
5ed!
Karthik_Mahalingam 30-Apr-15 5:05am
   
:) Thank u
Venkata Durga Rao 30-Apr-15 9:35am
   
Hi Maciej Los your solution is also not working
Karthik_Mahalingam 30-Apr-15 10:43am
   
did u get the solution?
Rate this:
Please Sign up or sign in to vote.

Solution 5

This is basically the same solution as solution 3, but a bit beautified.

Required "usings":
using System;
using System.Linq;
using System.Linq.Expressions;


Extension class:
public static class QueryableExtensions
{
    public enum Order
    {
        Asc,
        Desc
    }

    public static IQueryable<T> OrderByDynamic<T>(
        this IQueryable<T> query,
        string orderByMember,
        Order direction)
    {
        var queryElementTypeParam = Expression.Parameter(typeof(T));

        var memberAccess = Expression.PropertyOrField(queryElementTypeParam, orderByMember);

        var keySelector = Expression.Lambda(memberAccess, queryElementTypeParam);

        var orderBy = Expression.Call(
            typeof(Queryable),
            direction == Order.Asc ? "OrderBy" : "OrderByDescending",
            new Type[] { typeof(T), memberAccess.Type },
            query.Expression,
            Expression.Quote(keySelector));

        return query.Provider.CreateQuery<T>(orderBy);
    }
}


Sample use:
var pupils = db.Pupil.OrderByDynamic("Name", QueryableExtensions.Order.Asc);
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 4

Solution #3 is basically correct. You need to create and Expression on the fly. EF examines the expression to generate the SQL so plain lambda won't work. Here is my approach:
static Expression<Func<T,object>> CreateExpression<T>(string propertyName)
{
    var type = typeof(T);
    var property = type.GetProperty(propertyName);
    var parameter = Expression.Parameter(type);
    var access = Expression.Property(parameter, property);
    var convert = Expression.Convert(access, typeof(object));
    var function = Expression.Lambda<Func<T,object>>(convert, parameter);
    
    return function;
}

And you can use it like this:
db.Pupil.OrderBy(CreateExpression<Pupil>>("Name"));

It's ironic if you think about it. All this is needed to wrap the property name only to allow the LINQ provider to extract it later in order to generate the ORDER BY <your property="" name=""> clause. Ok, there may be some mapping but anyway...
Edit
Please note that this doesn't work. If you remove the convert to object it will eventually work for reference types but not for value types.
var access = Expression.Property(parameter, property);
// var convert = Expression.Convert(access, typeof(object));
var function = Expression.Lambda<Func<T,object>>(access, parameter);
   
v2
Comments
Sascha Lefèvre 30-Apr-15 19:03pm
   
Have you tested it? I'm getting a NotSupportedException: Unable to cast the type 'System.String' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
Tomas Takac 1-May-15 4:23am
   
You are right. I didn't test is with EF just a dummy IQueryable. If I remove the cast to object then it works, but only for reference types.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100