c# - Order a linq query

Question

For my generic grid I currently do this to activate the sorting:

Elements.OrderBy(column.SortExpression).AsQueryable();

In which SortExpression is of type Func<T, object> and column is a generic class Column<T>

I set SortExpression in a controller like this:

new Column<OrderViewData>{Key = "ShippingDate", SortExpression = e => e.ShippingDate}

The 'OrderBy' causes an execution of the sql statement, which I don't want.

So I'm trying to replace it with this:

Elements = from element in Elements
               orderby column.SortExpression
               select element;

Which doesn't trigger the sql execution.

Now, of course column SortExpression should be of another type. Only I can't really figure out what type it should be and how to set it on the generic class in the controller.

I should still be able to set SortExpression in a generic strong typed way of some sort.

Any suggestions on how I can order by an expression set somewhere else in the application, without executing the sql when applying the order to the IQueryable?

@ Earwicker:

This works:

Expression<Func<Employee, DateTime?>> sortexpression = e => e.BirthDate;

var db = new NorthwindDataContext();
var query = from e in db.Employees
                select e;
query = query.OrderBy(sortexpression);

int count = query.Count();

And generates:

SELECT COUNT(*) AS [value]
FROM [dbo].[Employees] AS [t0]

When I replace DateTime? in the first line with object:

Expression<Func<Employee, object>> sortexpression = e => e.BirthDate;

I get this exception :

InvalidOperationException: Cannot order by type 'System.Object'

Now, you might say: "then just use DateTime?", but I'd like building the columns in my generic grid to require the least amount of code possible. I don't want people to have to type the whole Expression<Func<Employee, some_type>>. I want people to be able to just type something small like my first attempt SortExpression = e => e.BirthDate, where I take advantage of the generic Column class to define 'T'.

Do you think it would be possible to create some kind of extension that somehow gets the type of e.BirthDate and then casts the Func<T, object> to Expression<Func<T,some_type>> ? Then I could do something in the internal code like: Elements.OrderBy(column.SortExpression.FixCast())

I don't care much that my internal code is ugly or complex at this moment. I need to get the SQL queries right & take care of usability for developers using the grid.

Thanks a lot for helping me out!

@ earwicker 2:

var gridBuilder = new RainbowGridBuilder<Employee> ("Examples_Employees") 
{
    
    Elements = GetEmployees(),
    //The elements (records) we will show in our grid. 
    
    //Define the columns for our grid.
    Columns = new List<Column<Employee >> 
    {
        new Column<Employee> 
        {
            Key = "EmployeeId", 
            //Key is used for sorting, selecting columns, ...
            
            Header = "Employee Id", 
            //The header of the column. Also used as caption in the column selection checkboxlist.
            
            ValueExpression = e => e.EmployeeID.ToString(), 
            
            //The Linq expression which will be executed on each element to fill the cell
            
            SortExpression = e => e.EmployeeID, 
            //The Linq expression by which to sort the elements in the grid. 
            
            Display = false
        }, //Is this column visible by default?
      
        new Column<Employee> 
        {
            Key = "Name",
            ValueExpression = e => 
                e.FirstName + " " + e.LastName,
            SortExpression = e => e.LastName
      } ,
    },

    // Some other properties here that are irrelevant.
}

Answer 1

SortExpression should be of the type Expression<Func<T, object>>.

By making it a Func<T, object>, you cause the compiler to reduce it directly down to IL, ready to execute. Linq to SQL (or entities, or NHibernate, or whatever) will need the code captured in the form of an expression tree so it can translate it into SQL or some other query language for execution elsewhere. By assigning your lambda to Expression<Func<...>> you trigger compilation to an expression tree.

Does it work if you put this?

Elements = Elements.OrderBy(e => e.ShippingDate);

And how about this?

Expression<Func<OrderViewData, object>> sortExpression = e => e.ShippingDate;
Elements = Elements.OrderBy(sortExpression);

Based on your updated question, it sounds like you need to capture an expression with a static-typed return value, instead of object.

It's hard to know what would be the ideal arrangement for you, but in your original setup code you had:

new Column<OrderViewData>{Key = "ShippingDate", SortExpression = e => e.ShippingDate}

Suppose Column took two type parameters, TElem (the kind of value stored in the column) and TSort (the type of the value to sort by).

new Column<Employee, DateTime?> { SortExpression = e => e.BirthDate }

That doesn't look too unwieldy to me, and SortExpression would then just be:

Expression<Func<TElem, TSort>> SortExpression { get; set; }