Linq Expression for Paging / Pagination with dynamic OrderBy

All,

I am trying to get paging working with a grid. In order to do this, I have to pass in which field to sort by. I cannot figure out how to do this with a Linq query. I am using .NET 4 / EF 4.1. In the two examples below, #1 works just fine. The problem is, I am passing in the field to sort by, and so I need to be able to dynamically cha开发者_StackOverflow中文版nge what we are sorting by. When I try to use a string as in example 2, it does not sort by my expression. Is there any way to accomplish this? It seems like lots of people should need this functionality.

    [Example 1]
(from e in _context.MyEntity
 where (MyWhereClause)
 orderby e.SomeProperty Ascending
 select e).Skip(Offset).Take(MyCountPerPage);

    [Example 2]
(from e in _context.MyEntity
 where (MyWhereClause)
 orderby "SomeField, ASC"
 select e).Skip(Offset).Take(MyCountPerPage);

-Thanks-

Use Dynamic LINQ

First, you'll want to pull your orderby out of the query and use the extension method version,

var query = from e in _context.MyEntity
            where (MyWhereClause)
            select e;

query = query.DynamicOrderBy("property");

query = query.Skip(Offset).Take(MyCountPerPage);

Next, we have to build the DynamicOrderBy, I'm assuming the query is against some sort of IQueryable<T>.

//Need this to construct the query correctly
static MethodInfo s_orderBy = typeof(Queryable).GetMethods().First(m => m.Name == "OrderBy");

static IOrderedQueryable<T> DynamicOrderBy<T>(this IQueryable<T> source, string property)
{
    var expr = source.Expression;
    var p = Expression.Parameter(typeof(T), "x");
    var propInfo = typeof(T).GetProperty(property, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
    var sortExpr = Expression.Lambda(Expression.Property(p, propInfo), p)
    var method = s_orderBy.MakeGenericMethod(typeof(T), propInfo.PropertyType);
    var call = Expression.Call(method, expr, sortExpr);
    var newQuery = source.Provider.CreateQuery<T>(call);
    return newQuery as IOrderedQueryable<T>;
}

I suppose, that you need this for jqGrid. I posted before the answer with the full VS2008 project which you can use as an example.

The main idea is that Entity Framwork can be used with ObjectQuery<T> which supports sorting like "SomeField, ASC" which you need. So you are able implement all you need without Dynamic LINQ extension. Even more (see here) you can use construction like

.Where("it.equipmentID < @maxId", new ObjectParameter ("maxId", 100))

with the WHERE having string arguments ("it.equipmentID < @maxId"). So you can implement all paging, sorting and filtering using string arguments, which you need for jqGrid. The example from which I started my answer demonstrate how you can do this.

   var query = (from e in _context.MyEntity
     where (MyWhereClause)
     orderby e.SomeProperty Ascending
     select e).Skip(Offset).Take(MyCountPerPage);

    if(Ascendingflag)
       query = query.OrderBy(a = > SortExpression);
    else
       query = query.OrderByDescending(a = > SortExpression);