Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have the following

C#
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
C#
db.Pupil.OrderBy(x => x.Name).Select(x => x);
Posted
Updated 6-Jan-22 1:34am
v2
Comments
Maciej Los 30-Apr-15 3:09am    
What have you tried? Where are you stuck?

BTW: Select(x=>x) is unnecessary!

C#
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
C#
var Pupil = from s in db.Pupil select s;
Pupil = SortExtension.OrderByDynamic(Pupil, sortfield,false);

here Pupil is Iqueryable
 
Share this answer
 
v2
Comments
Maciej Los 1-May-15 12:56pm    
Sascha mentioned that this is the only solution which works, so +5
Member 13020068 23-Dec-21 16:08pm    
Doesn't compile
add System.Linq.Dynamic.cs [^] file to your solution and use the below code to do dynamic order by
references : dynamic-query-library[^]


C#
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
 
Share this answer
 
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?
This is basically the same solution as solution 3, but a bit beautified.

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


Extension class:
C#
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:
C#
var pupils = db.Pupil.OrderByDynamic("Name", QueryableExtensions.Order.Asc);
 
Share this answer
 
v2
Comments
Member 13020068 23-Dec-21 16:06pm    
Won't even compile
Shippy 1971 15-Jul-22 9:17am    
Works great. Thank you!
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:
C#
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:
C#
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.
C#
var access = Expression.Property(parameter, property);
// var convert = Expression.Convert(access, typeof(object));
var function = Expression.Lambda<Func<T,object>>(access, parameter);
 
Share this answer
 
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.
John V 2021 20-Apr-21 16:51pm    
For some reason all the solutions submitted seem to assume only ONE column to order by. Quite often people need MULTIPLE sorts. That's more complex.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900