Blog For .NET

A common paradigm in my current work involves separating sort logic from the data access layer that applies it. The end user may sort a table/view by any number of criteria and in fairly complex ways.

One simple way to achieve this is through a switch statement:

private IOrderedQueryable<Customer> SortData(String sortExpression, IQueryable<Customer> query)
  {
   sortExpression = sortExpression ?? "CompanyName";
   IOrderedQueryable<Customer> orderedQuery = null;
   switch (sortExpression)
   {
    case "CompanyName":
     orderedQuery = query.OrderBy(x => x.CompanyName);
     break;
    case "Count":
     orderedQuery = query.OrderBy(x => x.Orders.Count());
     break;
    default:
     orderedQuery = query.OrderBy(x => x);
     break;
   }
   return orderedQuery;
  }

This quickly breaks down because we would need to include a case for every possible sort expression. There’s a fairly common approach at http://weblogs.asp.net/davidfowler/archive/2008/12/11/dynamic-sorting-with-linq.aspx to dynamically sort on a table’s columns. This works well but the approach does not lend itself to more complicated expressions e.g. sort customers by their number of orders.

We sought to create a framework for developers to filter and sort listings by any combination of criteria. As long as Linq to Sql supported the lamda expression, the developer should be free to populate a custom “filter” object with a list of “where” and “order by” information needed to act upon the table or view.

When filtering a sequence, Linq to Sql has no trouble converting lamda expressions that return booleans into t-sql “where” clauses – the boolean return value matches the necessary return value of the sql expression generated. The filter expression then can simply be:

Expression<Func<T, Boolean>> filterExpression

When sorting a sequence, however, the lamdba expression must return a Type that supports IComparable. A simplistic approach to dynamic sorting would then be to use sort expressions of the form:

Expression<Func<T, IComparable>> sortExpression

and apply them as

sortedQry = qry.OrderBy(sortExpression);

This will work just fine when querying objects, but will fail when used with Linq to Sql. Depending on the return type, the runtime may force a cast to IComparable – something Linq to Sql doesn’t know how to do.

The error specifically ocurrs when the return value of the expression is a DateTime, Int32, etc. 

To work around this we need to avoid casting the return value of the lamdba expression and instead let Linq to Sql know explicitly what type the expression returns.

instead of this:

We could, for instance, create a variable for each return type:

Expression<Func<T, DateTime>> sortExpressionDate

Expression<Func<T, Int32>> sortExpressionInt32

...

Such a framework is too cumbersome to use effectively. Instead, we can avoid the cast by telling .net at compile time the return type of the lamda expression.

Expression<Func<T, TKey>> SortExpression

 for example:

Expression<Func<Customer, Int32>> sortExpression = x => x.Orders.Count();

In my specific case, I used a filter to encapsulate the logic needed to filter and sort a sequence.

public class Filter<T>
 {
  /// <summary>
  /// the list of filters applied to the sequence. The filters will act as a series of "and" clauses against the data source.
  /// </summary>
  public List<Expression<Func<T, Boolean>>> Filters...

   /// <summary>
  /// the list of sorts to apply to the resulting sequence of items.
  /// </summary>
  public List<ISortOrder<T>> SortCriteria...
  }

public interface ISortOrder<T>
 {
  SortDirection Direction { get; set; }
  IOrderedQueryable<T> ApplyOrdering(IQueryable<T> query, Boolean useThenBy);
 }

For example, the following code filters by companyName and a minimum number of orders; then sorts by the column name specified by the “propertyNameToSortBy” variable followed by the count of orders:

var filter = new Filter<Customer>();

filter.Filters.Add(x => x.CompanyName.Contains(searchText));

filter.Filters.Add(x => x.Orders.Count < numberOfOrders);

filter.SortCriteria.Add(new FieldSortOrder<Customer>(propertyNameToSortBy));

var sortBy = new ExpressionSortOrder<Customer, Int32>()
     {
      SortExpression = x => x.Orders.Count,
      Direction = (sortExpression.ToLowerInvariant().EndsWith("desc")) ? DynamicSorter.SortDirection.Descending : DynamicSorter.SortDirection.Ascending
     };
     filter.SortCriteria.Add(sortBy);

return FilterItems(filter);

 the code for the FilterItems method then is relatively simple:

public static IEnumerable<Customer> FilterItems(Filter<Customer> filter)
  {
   var dc = new NorthwindDataContext();

   IQueryable<Customer> sequence = dc.Customers;
   foreach (var filterClause in filter.Filters)
   {
    sequence = sequence.Where(filterClause);
   }
   if (filter.SortCriteria.Count > 0)
   {
    var sortCriteria = filter.SortCriteria[0];
    var orderedSequence = sortCriteria.ApplyOrdering(sequence, false);
    if (filter.SortCriteria.Count > 1)
    {
     for (var i = 1; i < filter.SortCriteria.Count; i++)
     {
      sortCriteria = filter.SortCriteria[i];
      orderedSequence = sortCriteria.ApplyOrdering(sequence, false);
     }
    }
    sequence = orderedSequence;
   }
   return sequence;
  }

In the end, we used T4 templates to codegen entity specific filter objects and static methods to filter tables and views – more about that in my next post.

A sample northwind based sample can be found here.

No Comments yet »