LINQ - Dynamic expression to OrderBy

Question

I have a little issue, I'll try to explain this in detail.

Over my system I have a Generic Repository working with EF 4.1. Everything works fantastic, but I have a problem in a certain situation I would need to do dynamic orderBy over some querys.

I recieve by parameter an "string" that represent the field on my class, to do the orderBy (like "id" or "description")

Some portion of code:

        public class SomeClass
        {
            public int id { get; set; }
            public string description { get; set; }
        }

        // First we define the parameter that we are going to use
        // in our OrderBy clause. This is the same as "(parameter =>"
        // in the example above.
        var param = Expression.Parameter(typeof(SomeClass), "parameter");

        // Now we'll make our lambda function that returns the
        // request.SortingName property by it's name.
        var expression = Expression.Lambda<Func<SomeClass, int>>(Expression.Property(param, request.SortingName), param);

Well, this code works if the "request.SortingName" is type "int" (id) , but if I want to make the orderBy by "string" (description) or another type this code doesn't work.

I changed the expression to using "object":

        var expression = Expression.Lambda<Func<SomeClass, object>>(Expression.Property(param, request.SortingName), param);

But when I run the code, the compiler throws the next exception: Expression of type 'System.Int32' cannot be used for return type 'System.Object'

In case the property is string type, the exception is Expression of type 'System.String' cannot be used for return type 'System.Object'

In other words the code doesn't work with "object" type.

Anyone knows how can I figure this out?

Thanks for your time.

Can't you use reflection on the target type and look up the property based on the name you have and determine the proper type from that? — Kirk Woll, May 03 '12 at 00:20
[Dynamic LINQ OrderBy](http://stackoverflow.com/questions/41244/dynamic-linq-orderby) might help — Eranga, May 03 '12 at 00:55

score 8 · Accepted Answer · answered May 03 '12 at 01:19

Here's how I do dynamic sort and paginate using EF4 and some generic methods I've created in my standard dev library. The important thing is the second bit of code which you use to create the Lambda expression for the SortBy method.

public enum SqlOrderByDirecton
{
    ASC,
    DESC
}

 //Derive Lambda Expression from string

 string sortByKey = "BusinessId";
 string value = "DESC";

 var p = Expression.Parameter(typeof(T));

 this.SortBy = Expression.Lambda<Func<T, dynamic>>(Expression.TypeAs(Expression.Property(p, sortByKey), typeof(object)), p).Compile();

 this.SortOrder = (DevCore.SqlOrderByDirecton)Enum.Parse(typeof(DevCore.SqlOrderByDirecton), value, true);

public static List<T> SortAndPaginate<T>(IEnumerable<T> query,
                                             Func<T, object> sortBy,
                                             SqlOrderByDirecton sortOrder,
                                             int rowLimit,
                                             int startRecord,
                                             out int recordCount)
    {
        recordCount = query.Count();

        List<T> list = new List<T>();

        if (sortOrder == SqlOrderByDirecton.ASC)
        {
            list = query.OrderBy(sortBy).Skip(startRecord).Take(rowLimit).ToList();
        }
        else
        {
            list = query.OrderByDescending(sortBy).Skip(startRecord).Take(rowLimit).ToList();
        }

        return list;
    }

nice! I modified my code with: Expression.Lambda>(Expression.TypeAs(Expression.Property(param, request.SortingName), typeof(object)), param); then I do .Compile(); on my Repository and it's work perfect. But... if I don't 'Compile' the query doesn't work. Do you know what is the explanation? — HolloW, May 03 '12 at 02:52
@HolloW I'm not 100% sure why that is - I originally got the code from a question I asked, it worked and I didn't look at it any further - http://stackoverflow.com/questions/9756407/use-reflection-to-assign-value-of-funcproduct-object-property — Rob, May 03 '12 at 08:09
Does it really properly map into SQL's ORDER BY? How about Skip/Take? — Konstantin, Oct 04 '16 at 20:10
@Konstantin you'd have to check what the query in SQL Server Profiler or some other EF tool - but I don't see why not... OrderBy() is the standard Linq to Entity syntax. In relation to Skip/Take there's some complexities around how SQL Server does this anyway (at least in older release 2008/12?) but again they're the recommended out-of-the-box solutions for Linq to Entities. Btw I'm by no means suggesting these are efficient - it depends on the quantity of data and frequency of the queries - if you need lighting fast you need to consider native SQL or my pref is a stored procedure or view — Rob, Oct 05 '16 at 00:41

LINQ - Dynamic expression to OrderBy

1 Answers1