Trying to sort IQueryable by dynamic properties

Question

I have the following problem that I would like to solve with a single Linq Query:

I have my data in a database which I retrieve with the help of Entity Framework's Linq to SQL, then I would like to apply a sorting. For this I have a simple string I get from the client which I then map to a property dynamically. After that I only get the chunk I need to display via a simple Skip and Take.

The problem now seems to be that the actions I try to apply don't really go together well. I use an IQueryable for the Linq result since I get my data from the database. As soon as the Linq query tries to execute I get the error that with Linq to SQL I cannot use "ToValue()" which I need to do my dynamical sorting like this:

x.GetType().GetProperty(propertyName).GetValue(x, null);

Is what I'm trying to do even possible and can someone point me in the right direction? I've been playing around for what seems like forever with different approaches, but to no avail.

This is my last approach with some variables hardcoded, but it doesn't work either (and it might be clunky, since I've been working on it for some time now).

IQueryable<OA_FileUpload> result;
Expression<Func<MyObject, object>> orderBy = x => x.GetType().GetProperty(propertyName).GetValue(x, null);
result = Db.MyObject.Where(f => f.isSomething == true && f.isSomethingElse == false)
                                .OrderBy(orderBy)
                                .Skip(20)
                                .Take(20);

As soon as I later try to do something with the result it fails completely.

Take a look at this : http://stackoverflow.com/a/233505/961526 — Raphaël Althaus, Apr 08 '14 at 12:44
I'll have a look at this, but please note, that I'm not trying to use SQL like sorting. At this point I already want to apply sorting on the properties of the object that was returned. — Anne Schuessler, Apr 08 '14 at 12:51
Well, as long as you don't enumerate (you say you get en IQueryable) your order will be applied "at the SQL level"... If you've already enumerated, your orderBy should be a `Func`, not an `Expression>` — Raphaël Althaus, Apr 08 '14 at 12:56

score 1 · Accepted Answer · answered Apr 08 '14 at 13:27

No it is not possible in the way you're trying. The Entity Framework's engine cannot translate x.GetType().GetProperty(propertyName).GetValue(x, null); to SQL. You're applying OrderBy before Skip and Take, which is the right way, but it also means that your sorting will be translated as part of the generated SQL.

What you can do though, is to build your query inclemently (i.e. in several steps). Then you can add the sorting with the conditions you want. Something like this:

IQueryable<OA_FileUpload> result = 
     Db.MyObject.Where(f => f.isSomething == true && f.isSomethingElse == false);

//Add your conditional sorting.
if(...) //Your first condition for sorting.
    result = result.OrderBy(....); //Your sorting for that condition.
else if(...) //Your second condition for sorting.
    result = result.OrderBy(....); //Your sorting for that condition.

//Now apply the paging.
    result = result.Skip(20).Take(20);

The LINQ above is still translated into and executed as one single query, but now you can add all the conditions you want.

I think this is what we're already doing. The problem is that we have quite a bunch of conditions, all in one big switch case and were looking for a way to make the code a bit sleeker by getting them dynamically (i.e. mapping the propertyName to an actual property). — Anne Schuessler, Apr 08 '14 at 13:46
@AnneSchuessler What I usually do to make it both neater and more reusable is: I put the conditions, sorting, etc in separate functions like: `ApplyCriteria()`, `ApplySorting()`, `ApplyPaging()`, etc. This way you can even call the same function to add conditions or sorting to different queries that have similar conditions and/or sorting. — Racil Hilan, Apr 08 '14 at 13:56

Trying to sort IQueryable by dynamic properties

1 Answers1