Linq To SQL: Sort Query by Arbitrary Property(Column) Name

Question

I have a larger/more complex problem, but for simplicity sake, let us consider the following:

Let us say that I have table in the SQL DataBase called Product, having two columns, ID (int, primary key) and Name (varchar/string). I also have a simple LINQ DataContext.

I have a query constructed and handed to “my” function. Let us say it is something like: (though it may be a bit more complex)

IQueryable<Product> query = from p in db.Products select p;

Once my method gets this query, passed in as a parameter, it has to change the sort order e.g.

IQueryable<Product> sortedQuery = query.OrderBy(x => x.Name);

I would like to make this more generic i.e. to specify the field to sort on. Normally, I can do a switch statement that takes a string. However I would like to know if there is a way to pass the parameter directly. I intend to extend this to other Database tables, so these switch statements would get tedious.

I was trying something like:

IQueryable<Product> sortedQuery = query.OrderBy(x => (typeof(Product)).GetProperty(“Name”));

But this does not work. I also want to ensure that the LINQ to SQL is maintained i.e. the sort to be done on the SQL Server. Hence if I debug, I should get a SQL query from this LINQ query.

Thank you in advance for your help.

Answer 1

You could use Dynamic Linq for this purpose.

See here Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library)

Then you can make calls like this:

var query = DBContext.Users.Where( "Age > 3" ).OrderBy( "Name asc" );

Answer 2

Try this out instead:

query.OrderBy(x => x.GetType().GetProperty(“Name”).GetValue(x, null));

You can't just grab the property. You need to grab the value off of that property, hence the call to GetValue.

Answer 3

It is not as easy as it seems. The LINQ to SQL engine parses the expression you pass to the OrderBy method in order to obtain the name of the property you are referencing, then uses this information to compose a plain SQL order by clause.

I guess that maybe it can be done by using reflection, anyway. Maybe you can get something useful from the accepted answer of this SO question.