Order a linq query

Question

Hello Stackers.

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, ofcourse 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 strongtyped 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 an 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; }

Answer 2

I think the root of the problem is due to the fact that you did OrderBy before converting to a Queryable. If you order on an IEnumerable<T>, then you need something to order first. Unlike IQueryable<T>, IEnumerable<T> does not build expression trees...it wraps one enumerable around the other. To gain the benefits of deferred execution, you need to make sure that your working with an IQueryable from the beginning. Converting to one after you have performed all of your filtering, sorting, etc. won't offer any benefit.

Answer 3

I came up with a solution that achieves what I was after.

Usage: Setting strong typed sortexpression on columns:

Columns = new List<Column<Employee>>{
            new Column<Employee> {
                Key = "EmployeeId",   
                SortExpression = new SortExpression<Employee>(e => e.EmployeeID)
                // ... other irrelevant column properties ...  
            },
            new Column<Employee> {
                Key = "EmployeeBirthDate",      
                SortExpression = new SortExpression<Employee>(e => e.BirthDate)
            }
          };

Underlying code

These few lines in the "buildGrid()" method in class RainbowGridBuilder apply the sorting:

if (columnToSort != null) //sort the elements according to the set column key and the sortexpression
{
    var column = Columns.Where(c => c.Key == columnToSort).SingleOrDefault();
    if (column != null)
    {
        Elements = column.SortExpression.ApplySortExpression(Elements, descending);
    }
}

How it works:

Trough the use of overloaded constructors in the SortExpression class that I made I can set a private Expression<Func<T, some_specific_Type>>.

Inside the SortExpression class is a method ApplySortExpression that searches for a non-null private member and sorts accordingly:

public class SortExpression<T>
{
    private Expression<Func<T, DateTime?>> DateTimeExpression { get; set; }
    private Expression<Func<T, int?>> intExpression { get; set; }
    private Expression<Func<T, string>> stringExpression { get; set; }

    public SortExpression(Expression<Func<T, DateTime?>> expression)
    {
        DateTimeExpression = expression;
    }

    public SortExpression(Expression<Func<T, int?>> expression)
    {
        intExpression = expression;
    }

    public SortExpression(Expression<Func<T, string>> expression)
    {
        stringExpression = expression;
    }

    public IQueryable<T> ApplySortExpression(IQueryable<T> elements, bool? descending)
    {
        if (DateTimeExpression != null)
        {
            if (descending.HasValue && descending.Value)
                return elements.OrderByDescending(DateTimeExpression);
            else
                return elements.OrderBy(DateTimeExpression);
        }
        else if (intExpression != null)
        {
            if (descending.HasValue && descending.Value)
                return elements.OrderByDescending(intExpression);
            else
                return elements.OrderBy(intExpression);
        }
        else if (stringExpression != null)
        {
            if (descending.HasValue && descending.Value)
                return elements.OrderByDescending(stringExpression);
            else
                return elements.OrderBy(stringExpression);
        }
        else
            throw new Exception("Unsuported sortkey type");
    }
}

This solution may not be super clean under the hood, but it's the usability that I was after. The only change to the consuming code was to add "new SortExpression<Employee>".