EntityFramework 4 OrderBy overwrites previous OrderBy calls

I often want to provide orderings for IQueryables that should act like secondary orderings if another is specified later. For example, the following:

Repository.All.OrderBy(o => o.Name).OrderBy(o => o.SerialNumber)  [A]

Should be equivalent to:

Repository.All.OrderBy(o 开发者_如何学编程=> o.SerialNumber).ThenBy(o => o.Name)

This worked correctly using LINQ to SQL. However, in EntityFramework 4, the Order-By clause in the generated SQL looks like this:

ORDER BY [Project1].[SerialNumber] ASC

It completely ignores the first OrderBy statement, which actually breaks OrderBy being a stable sort. ThenBy is not an option for me, because the orderings are not always defined in the same place (for example, in statement [A] above, the OrderBy(o => o.Name) could be defined in the Repository. Extensions to IQueryable<TModel> are not a good solution either, because it doesn't allow different Repositories to sort differently, and the consuming code should not have to call some .SortDefault() code since that's not its concern.

Is there any good way to force Linq to Entities to respect multiple OrderBy statements?

Thanks

I don't agree that a subsequent OrderBy should be equivalent to a ThenBy. If that were so, there would be no need for ThenBy and you could never override an existing sort.

I can't say I like it, but off the top of my head this would seem an option for downstream sorting:

IQueryable<Item> items = Repository.GetAllWhichMightBeOrderedAlready();
return items is IOrderedEnumerable<Item>
    ? ((IOrderedQueryable<Item>)items).ThenBy(x => x.SomeProperty)
    : items.OrderBy(x => x.SomeProperty);

Substitute IOrderedEnumerable<T> as appropriate.

Ok, it's not the most elegant solution, but I was able to overcome this in a way that seems to work fine, although I suspect all the funky reflection may make it too slow. I created my own custom IQueryable class and associated query provider that take an ExpressionVisitor and call .Visit() on that visitor on GetEnumerator and Execute calls. My base repository class returns a new MappedExpressionQuery and passes it the query returned from DbContext.Set() along with an ExpressionVisitor that produces the desired ordering. The custom queryable and provider classes:

public class MappedExpressionQuery<T> : IOrderedQueryable<T>
{
  private IQueryable<T> baseQuery;
  private MappedExpressionQueryProvider<T> provider;

  public MappedExpressionQuery(IQueryable<T> query, ExpressionVisitor expressionMap)
  {
    baseQuery = query;
    provider = new MappedExpressionQueryProvider<T>(query.Provider, expressionMap);
  }

  #region IOrderedQueryable<T> Members

  public IEnumerator<T> GetEnumerator()
  {
    return baseQuery.Provider.CreateQuery<T>(provider.ExpressionMap.Visit(baseQuery.Expression)).GetEnumerator();
  }

  IEnumerator IEnumerable.GetEnumerator()
  {
    return baseQuery.Provider.CreateQuery(provider.ExpressionMap.Visit(baseQuery.Expression)).GetEnumerator();
  }

  public Type ElementType
  {
    get { return baseQuery.ElementType; }
  }

  public Expression Expression
  {
    get { return baseQuery.Expression; }
  }

  public IQueryProvider Provider
  {
    get { return provider; }
  }

  #endregion
}

public class MappedExpressionQueryProvider<T> : IQueryProvider
{
  public ExpressionVisitor ExpressionMap { get; private set; }
  private IQueryProvider baseProvider;

  public MappedExpressionQueryProvider(IQueryProvider baseProvider, ExpressionVisitor expressionMap)
  {
    this.ExpressionMap = expressionMap;
    this.baseProvider = baseProvider;
  }

  #region IQueryProvider Members

  public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
  {
    return new MappedExpressionQuery<TElement>(baseProvider.CreateQuery<TElement>(expression), ExpressionMap);
  }

  public IQueryable CreateQuery(Expression expression)
  {
    throw new NotImplementedException();
  }

  public TResult Execute<TResult>(Expression expression)
  {
    return baseProvider.Execute<TResult>(ExpressionMap.Visit(expression));
  }

  public object Execute(Expression expression)
  {
    return baseProvider.Execute(ExpressionMap.Visit(expression));
  }

  #endregion
}

When my custom ExpressionVisitor class finds an OrderBy or ThenBy statement, it travels down the expression tree recording the proper orders each sort should be in until it finds a statement that is not an Order statement and is not commutative with an Order statement. Then it builds up all the statements again at the end of the expression. So OrderBy(A).ThenBy(B).OrderBy(C).OrderBy(D).ThenBy(E) is returned with the following additional expressions attached to the end: .OrderBy(D).ThenBy(E).ThenBy(C).ThenBy(A).ThenBy(B). Yes, it is redundant, but EntityFramework ignores the expressions further down the chain anyway, and I only use this QueryProvider with queryables that come from the DbContext. The code for this expression visitor (I also threw in a fix for the fact that .ToString() doesn't translate to SQL even when used on constants, so DbContext.Set<T>().Where(o => o.Name == SomeConstant.ToString()) works now):

public abstract class QueryModifier : ExpressionVisitor
{
  private bool OrganizedOrdering { get; set; }

  protected override Expression VisitMethodCall(MethodCallExpression node)
  {
    if (node.Method.Name == "ToString" && node.Method.DeclaringType == typeof(object))
    {
      try
      {
        //If the object calling ToString is parameterless, invoke the method and convert it into a constant.
        return Expression.Constant(Expression.Lambda(node).Compile().DynamicInvoke());
      }
      catch (InvalidOperationException)
      {
        throw new InvalidOperationException("ToString() can only be translated into SQL when used on parameterless expressions.");
      }
    }
    else if (IsOrderStatement(node.Method))
    {
      if (!OrganizedOrdering)
      {
        OrganizedOrdering = true;
        return RearrangeOrderStatements(node);
      }
      else
        return base.VisitMethodCall(node);
    }
    else if (OrganizedOrdering && !IsOrderCommutative(node.Method))
    {
      OrganizedOrdering = false;
      return base.VisitMethodCall(node);
    }
    else
    {
      return base.VisitMethodCall(node);
    }
  }

  private Expression RearrangeOrderStatements(MethodCallExpression node)
  {
    //List to store (OrderBy expression, position) tuples
    List<Tuple<MethodCallExpression, double>> orderByExpressions = new List<Tuple<MethodCallExpression, double>>();
    double low = 0;
    double high = 1;

    MethodCallExpression startNode = node;
    Expression lastNode = node.Arguments[0];

    //Travel down the chain and store all OrderBy and ThenBy statements found with their relative positions
    while (node != null && node.Method.DeclaringType == typeof(System.Linq.Queryable))
    {
      if (node.Arguments.Count == 0)
        break;

      if (node.Method.Name.StartsWith("OrderBy"))
      {
        orderByExpressions.Add(new Tuple<MethodCallExpression, double>(node, low));
        low = low + 1;
        high = low + 1;
      }
      else if (node.Method.Name.StartsWith("ThenBy"))
      {
        double pos = (high - low) * 0.9 + low;
        orderByExpressions.Add(new Tuple<MethodCallExpression, double>(node, pos));
        high = pos;
      }
      else if (!IsOrderCommutative(node.Method))
      {
        break;
      }

      lastNode = node.Arguments[0];
      node = lastNode as MethodCallExpression;
    }

    lastNode = startNode;
    var methods = typeof(Queryable).GetMethods().Where(o => IsOrderStatement(o));

    Type queryType = startNode.Arguments[0].Type.GetGenericArguments()[0];

    bool firstStatement = true;
    foreach (var tuple in orderByExpressions.OrderBy(o => o.Item2))
    {
      string methodName;
      if (firstStatement)
      {
        methodName = "OrderBy";
        firstStatement = false;
      }
      else
        methodName = "ThenBy";
      if (tuple.Item1.Method.Name.EndsWith("Descending"))
        methodName = methodName + "Descending";

      Type orderByTValueType = tuple.Item1.Arguments[1].Type.GetGenericArguments()[0].GetGenericArguments()[1];

      if (tuple.Item1.Arguments.Count == 3)
      {
        var method = methods.Single(o => o.Name == methodName && o.GetParameters().Length == 3)
          .MakeGenericMethod(queryType, orderByTValueType);
        lastNode = Expression.Call(method, lastNode, tuple.Item1.Arguments[1], tuple.Item1.Arguments[2]);
      }
      else
      {
        var method = methods.Single(o => o.Name == methodName && o.GetParameters().Length == 2)
          .MakeGenericMethod(queryType, orderByTValueType);
        lastNode = Expression.Call(method, lastNode, tuple.Item1.Arguments[1]);
      }
    }

    return Visit(lastNode);
  }

  /// <summary>
  /// Returns true if the given method call expression is commutative with OrderBy statements.
  /// </summary>
  /// <param name="expression"></param>
  /// <returns></returns>
  private bool IsOrderCommutative(MethodInfo method)
  {
    return new string[] { "Where", "Distinct", "AsQueryable" }.Contains(method.Name)
      && method.DeclaringType == typeof(System.Linq.Queryable);
  }

  private bool IsOrderStatement(MethodInfo method)
  {
    return (method.Name.StartsWith("OrderBy") || method.Name.StartsWith("ThenBy"))
      && method.DeclaringType == typeof(System.Linq.Queryable);
  }
}

So you can't use ThenBy because the initial OrderBy might be skipped? How about doing an initial dummy OrderBy, then all the others are ThenBy.

// Basically, everything gets the same orderby ranking
// I don't know offhand if you can use a constant here, but if you have an id,
// you should be able to this.
var list = context.MyTable.OrderBy(mt => mt.id - mt.id);

if (order by field1)
    list = list.ThenBy(mt => mt.field1);

if (order by field2)
    list = list.ThenBy(mt => mt.field2);

etc...

EDIT: Nevermind. This doesn't work. Can't use ThenBy on a seperate line by itself, as I was thinking.