Dynamic order (SQL ORDERBY) in LINQ CompiledQuery

Question

How can I create a dynamic ORDERBY in my LINQ CompiledQuery (e.g. delivery order field and direction as parameters for a compiled query)?

Answer 1

I think I found:

The link will open. He will point you to the VS2008 code samples that contain the Linq dynamic query library, which contains the extension method below. This will allow you to go:

Object.OrderBy("ColumnName"); 

Here are extension methods, but you may need the whole library.

 public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values) { return (IQueryable<T>)OrderBy((IQueryable)source, ordering, values); } public static IQueryable OrderBy(this IQueryable source, string ordering, params object[] values) { if (source == null) throw new ArgumentNullException("source"); if (ordering == null) throw new ArgumentNullException("ordering"); ParameterExpression[] parameters = new ParameterExpression[] { Expression.Parameter(source.ElementType, "") }; ExpressionParser parser = new ExpressionParser(parameters, ordering, values); IEnumerable<DynamicOrdering> orderings = parser.ParseOrdering(); Expression queryExpr = source.Expression; string methodAsc = "OrderBy"; string methodDesc = "OrderByDescending"; foreach (DynamicOrdering o in orderings) { queryExpr = Expression.Call( typeof(Queryable), o.Ascending ? methodAsc : methodDesc, new Type[] { source.ElementType, o.Selector.Type }, queryExpr, Expression.Quote(Expression.Lambda(o.Selector, parameters))); methodAsc = "ThenBy"; methodDesc = "ThenByDescending"; } return source.Provider.CreateQuery(queryExpr); } 

Answer 2

I would do it like this, first you really need a way to access the property value by a string on the object. You could use reflection, but its slow. Therefore, use this helper class approach based on the tests http://stefan.rusek.org/Posts/LINQ-Expressions-as-Fast-Reflection-Invoke/3/

  public static class LINQHelper { public static IComparable OrderByProperty<TClass>(TClass item, string propertyName) { var t = Expression.Parameter(typeof(TClass), "t"); var prop = Expression.Property(t, propertyName); var exp = Expression.Lambda(prop, t).Compile(); return (IComparable)exp.DynamicInvoke(item); } } 

In your code, where you want to receive your order by the property name string, in this col1 example, you simply do the following.

  var myQuery = from i in Items select i; myQuery.OrderBy(i=>LINQHelper.OrderByProperty(i,"col1")); 

Hope this helps.