Dynamic IQueryable order by using string

Question

Possible Duplicate:
Dynamic LINQ OrderBy

I'm trying to create a dynamic sorting to Iqueryable.
So bellow you can see that I am following some examples I see here in stackoverflow.

 var query = dalSession.Query<T>();
            var res = (from x in query orderby Extensions.Sort<T>(query, "FirstName") select x).Skip((paging.CurrentPageRecord)).Take(paging.PageSize);


public static class Extensions
{
    public static IQueryable<T> Sort<T>(this IQueryable<T> query,
                                             string sortField)
    {
        return query.OrderByDescending(s => s.GetType()
                                             .GetProperty(sortField));

    }
}

This is the exception that I get:

System.Linq.IQueryable1[Partners.BusinessObjects.Affiliate] Sort[Affiliate](System.Linq.IQueryable1[Partners.BusinessObjects.Affiliate], System.String)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NotSupportedException: System.Linq.IQueryable`1[Partners.BusinessObjects.Affiliate] Sort[Affiliate](System.Linq.IQueryable`1[Partners.BusinessObjects.Affiliate], System.String)

See this example: http://stackoverflow.com/questions/41244/dynamic-linq-orderby — Hardrada, Nov 06 '12 at 13:49

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

The problem is here:-

s => s.GetType().GetProperty(sortField)

When you try to access the result set of the IQueryable, it gets translated to SQL and the query gets run against your database (rather than being run in memory). The problem is that obviously your database knows nothing about your types, can't call any methods on them, and certainly can't perform any reflection on them.

You'll need to build your own expression tree which can be translated into SQL. The expression API is quite complicated and you'll need to do quite a bit of reading if you want to really get it. Here's a good starting point on creating dynamic queries using the expression API for you.

Thankfully your specific case is reasonably simple and there are worked examples of it here, here, and here, but I really do recommend doing some reading on it to understand what's going on. If you just copy-paste then you or someone else will end up having to maintain it and having a really sad time.

score -1 · Answer 2 · answered Nov 06 '12 at 14:14

-1

Iain is right, your iqueryable starts pulling data only when really needed. You can force the load by a simple trick long dummy = query.Count();. That is not the complete solution, you want to avoid complexity of building the query tree so if the collection is not extremely large, just convert it into some sortable type.

answered Nov 06 '12 at 14:14

Peter

29
2

If you want to do the sort in memory you can use .ToList() – Iain Galloway Nov 06 '12 at 15:39

Dynamic IQueryable order by using string

2 Answers2