Remove OrderBy from an IQueryable<T>

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();