从一个IQueryable中删除OrderBy。

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

我有一个分页API，它返回用户请求的行，但一次只返回这么多，而不是整个集合。API的工作是设计的，但我必须计算可用的记录总数(用于适当的页面计算)。在API中，我使用Linq2Sql，在我最终提出请求之前，我用IQueryable做了很多工作。当我去获取计数时，我调用了一些类似的东西: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.

由此产生的SQL非常有趣，但是它也添加了不必要的顺序，使得查询非常昂贵。

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.

因为我使用的是IQueryable，所以在它到达SQL服务器之前，我可以操作IQueryable。

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

我的问题是，如果我已经有一个IQueryable，在它里面有一个OrderBy，那么在我调用Count()之前，是否可以删除OrderBy ?

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

如: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.

如果不是,不要紧。我看到许多关于如何使用OrderBy的问题，但没有涉及从Linq表达式中删除OrderBy。

Thanks!

谢谢!

6 个解决方案

#1

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

这里不仅有一个不需要的顺序，还有一个虚假的顶部(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.

该子选择只返回0或1行。事实上，如果没有顶部，在子选择中有顺序是不合法的。

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 )

ORDER BY子句在视图、内联函数、派生表、子查询和公共表表达式中是无效的，除非TOP或XML也被指定。:SELECT COUNT(*) FROM(从表1中选择*)

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

我想你可能做错了什么事。在调用.Count()之前，您确定没有写入.Take(1)或类似的地方吗?

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

#2

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.

因此，下面的代码是对内存数组的一个峰值。使用实体框架(或其他任意的IQueryProvider实现)可能会遇到一些困难。基本上，我们要做的是访问表达式树并查找任何排序方法调用，并将其从树中删除。希望这一点你能找到正确的方向。

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]);
    }
}

#3

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

我恐怕没有一种简单的方法可以从queryable中删除OrderBy操作符。

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.

但是，您可以做的是根据从重写queryable获得的新表达式重新创建可查询的。表达式(参见此处)省略了OrderBy调用。

#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.

SQL Server的查询优化器将删除这个无用的排序。它不会有运行时成本。

#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

过滤和排序是使用动态linq库完成的。

#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.

我知道这并不是您想要的，但是包含DataTimeStamp的DataOwnerID索引可以使您的查询更便宜。

#1