I am using datatables in quite a few pages in my ASP.NET MVC 3 site. They use server side paging and now I want to implement sorting based on the column headers. Datatables comes with iSortCol_0
which is an int value of what column is clicked.
I did not like this approach as the query would end up something like:
if(iSortCol_0 == 0)
{
// query
// OderBy(x => x.CarName)
}
Then this would be repeated for each column (plus an else clause on each to order by descending). So I have changed my approach and now pass the column name to the server.
I have come up with the following:
Expression<Func<vw_Car, string>> sortExpression1 = null;
Expression<Func<vw_Car, int>> sortExpression2 = null;
switch(columnToSort)
{
case "InvoiceNo": sortExpression1 = x => x.CarNo; break;
case "ClientNumber": sortExpression1 = x => x.ForeName; break;
case "ClientName": sortExpression1 = x => x.SurName; break;
default: sortExpression2 = x => x.Age.Value; break;
}
// start of query
.OrderByDir(sortDirection, sortExpression1 , sortExpression2)
Now the OrderByDir
looks like below:
public static IOrderedQueryable<T> OrderByDir<T>(this IQueryable<T> source, string dir, Expression<Func<T, string>> column1, Expression<Func<T, int>> column2)
{
if (column1 != null)
{
return dir == "asc" ? source.OrderBy(column1) : source.OrderByDescending(column1);
}
if (column2 != null)
{
return dir == "asc" ? source.OrderBy(column2) : source.OrderByDescending(column2);
}
return null;
}
This works in that it sorts columns that are of type string
or int
. I have another column of DateTime
that I want to sort, so I would need to write another sortExpression3
and then add that to my OrderByDir
.
However I don't really like the implementation - I had attempted to write a generic sort expression that took an object as second parameter instead of string
, int
, Datetime
, but when had this code in place, I was getting Unable to cast the type 'System.DateTime' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.
Any one any ideas as to a possible better approach to this?
Such extension should fits your needs:
public static class LinqExtensions
{
public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
{
return ApplyOrder(source, property, "OrderBy");
}
public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
{
return ApplyOrder(source, property, "OrderByDescending");
}
private static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodName)
{
var props = property.Split('.');
var type = typeof(T);
var arg = Expression.Parameter(type, "x");
Expression expr = arg;
foreach (var prop in props)
{
// use reflection (not ComponentModel) to mirror LINQ
var pi = type.GetProperty(prop);
expr = Expression.Property(expr, pi);
type = pi.PropertyType;
}
var delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
var lambda = Expression.Lambda(delegateType, expr, arg);
var result = typeof(Queryable).GetMethods().Single(
method => method.Name == methodName
&& method.IsGenericMethodDefinition
&& method.GetGenericArguments().Length == 2
&& method.GetParameters().Length == 2)
.MakeGenericMethod(typeof(T), type)
.Invoke(null, new object[] { source, lambda });
return (IOrderedQueryable<T>)result;
}
}
Invoke example:
query.OrderBy("Age.Value");