How do I sort a gridview of Linq objects based on a derived field?

Question

Hi all, I have written a page which uses Linq to query the database and bind the resulting IQueryable to a datagrid. I have a partial class which contains extra properties which derive their values based on other values brought in from the database.

Sorting works fine on fields that are actually in the database but not for the derived fields. When I attempt to sort on such a field I get an error saying "The member 'Trip.Difference' has no supported translation to SQL.

Any idea how to allow sorting on these derived fields?

Answer 1

The problem is that you are binding to an IQueryable, so every time you enumerate it, you are translating the LINQ expression on the IQueryable to a SQL statement and going back to the database to execute it.

If you are trying to sort on properties that are not bound to the database model then you will get the error mentioned, as those properties only exist once an object has been created from a data row.

The simplest solution is to call ToList() on the IQueryable before using it for sorting and data-binding, so that you sort on the in-memory objects where the properties are actually available. ToList() converts your IQueryable into an IEnumerable (via List<T>), and stops it from going to the database again via LINQ to SQL.

This is generally a good design pattern to follow - the last thing you want is consumers of your business layer being able to unwittingly execute arbitrary queries against your database, simply because you returned IQueryable where you should have returned IEnumerable.

Answer 2

Call ToEnumerable() first, and then add the OrderBy:

var q = (from a in mDataContext.Somethings()
        select a).ToEnumerable().OrderBy...