Remove OrderBy from an IQueryable

Question

I have a paging API that returns rows a user requests, but only so many at one time, not the entire collection. The API works as designed, but I do have to calculate the total number of records that are available (for proper page calculations). Within the API, I use Linq2Sql and I work a lot with the IQueryable before i finally make my requests. When I go to get the count, I call something like: totalRecordCount = queryable.Count();

The resulting SQL is interesting none the less, but it also adds an unnecessary Order By which makes the query very expensive.

exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM (
    SELECT TOP (1) NULL AS [EMPTY]
    FROM [dbo].[JournalEventsView] AS [t0]
    WHERE [t0].[DataOwnerID] = @p0
    ORDER BY [t0].[DataTimeStamp] DESC
    ) AS [t1]',N'@p0 int',@p0=1

Because I am using the IQueryable, I can manipulate the IQueryable prior to it making it to the SQL server.

My question is, if I already have an IQueryable with a OrderBy in it, is it possible to remove that OrderBy before I call the Count()?

like: totalRecordCount = queryable.NoOrder.Count();

If not, no biggie. I see many questions how to OrderBy, but not any involving removing an OrderBy from the Linq expression.

Thanks!

Answer 1

So, the below code is a spike against an in-memory array. There may be some hurdles to get this working with Entity Framework (or some other arbitrary IQueryProvider implementation). Basically, what we are going to do is visit the expression tree and look for any Ordering method call and simply remove it from the tree. Hope this points you in the right direction.

class Program
{
    static void Main(string[] args)
    {
        var seq = new[] { 1, 3, 5, 7, 9, 2, 4, 6, 8 };

        var query = seq.OrderBy(x => x);

        Console.WriteLine("Print out in reverse order.");
        foreach (var item in query)
        {
            Console.WriteLine(item);
        }

        Console.WriteLine("Prints out in original order");
        var queryExpression = seq.AsQueryable().OrderBy(x => x).ThenByDescending(x => x).Expression;

        var queryDelegate = Expression.Lambda<Func<IEnumerable<int>>>(new OrderByRemover().Visit(queryExpression)).Compile();

        foreach (var item in queryDelegate())
        {
            Console.WriteLine(item);
        }


        Console.ReadLine();
    }
}

public class OrderByRemover : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (node.Method.DeclaringType != typeof(Enumerable) && node.Method.DeclaringType != typeof(Queryable))
            return base.VisitMethodCall(node);

        if (node.Method.Name != "OrderBy" && node.Method.Name != "OrderByDescending" && node.Method.Name != "ThenBy" && node.Method.Name != "ThenByDescending")
            return base.VisitMethodCall(node);

        //eliminate the method call from the expression tree by returning the object of the call.
        return base.Visit(node.Arguments[0]);
    }
}

Answer 2

There isn't just an unneeded ORDER BY, there's also a spurious TOP(1).

SELECT TOP (1) NULL AS [EMPTY] ...

That subselect will only return 0 or 1 rows. In fact without the TOP there it wouldn't be legal to have an ORDER BY in a subselect.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.: SELECT COUNT(*) FROM ( SELECT * FROM Table1 ORDER BY foo )

sqlfiddle

I think you have probably done something wrong in your LINQ. Are you sure you haven't written .Take(1) or similar somewhere in your query, before calling .Count()?

This is wrong:

IQueryable<Foo> foo = (...).OrderBy(x => x.Foo).Take(1);
int count = foo.Count();

You should do this instead:

IQueryable<Foo> foo = (...);
Iqueryable<Foo> topOne = foo.OrderBy(x => x.Foo).Take(1);
int count = foo.Count();

Answer 3

I am afraid there is no easy way to remove the OrderBy operator from queryable.

What you can do, however, is to re-create the IQueryable based on the new expression obtained from rewriting queryable.Expression(see here) omitting the OrderBy call.

Answer 4

If you can't eliminate the root cause, here is a workaround:

totalRecordCount = queryable.OrderBy(x => 0).Count();

SQL Server's query optimizer will remove this useless ordering. It won't have runtime cost.

Answer 5

I think you have implemented you paging code wrongly. You actually need to query the database twice, once for the paged datasource and once for the total row count. This is how the setup should look.

public IList<MyObj> GetPagedData(string filter, string sort, int skip, int take)
{
   using(var db = new DataContext())
   {
      var q = GetDataInternal(db);
      if(!String.IsNullOrEmpty(filter))
         q = q.Where(filter); //Using Dynamic linq

      if(!String.IsNullOrEmpty(sort))
         q = q.OrderBy(sort); //And here

      return q.Skip(skip).Take(take).ToList();
   }
}

public int GetTotalCount(string filter)
{
    using(var db = new DataContext())
    {
       var q = GetDataInternal(db);
       if(!String.IsNullOrEmpty(filter))
         q = q.Where(filter); //Using Dynamic linq

       return q.Count(); //Without ordering and paging.
    }
}

private static IQuerable<MyObj> GetDataInternal(DataContext db)
{
   return 
        from x in db.JournalEventsView 
        where ...
        select new ...;
}

The filtering and sorting is done using the Dynamic linq library

Answer 6

I know it is not quite what you are looking for, but index on [DataOwnerID] with inclusion of DataTimeStamp could make your query less expensive.