CODEWITHSUNDEEP
c#sqllinqentity-framework
Anne Schuessler
Anne Schuessler

Reputation: 1702

Trying to sort IQueryable by dynamic properties

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.

Upvotes: 2

Views: 911

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25361

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.

Upvotes: 1

Related Questions