我们可以使用Skip()，Take()和OrderBy()控制LINQ表达式的顺序吗 [英] Can we control LINQ expression order with Skip(), Take() and OrderBy()

问题描述

我正在使用LINQ to Entities显示分页结果.但是Skip()，Take()和OrderBy()调用的组合存在问题.

I'm using LINQ to Entities to display paged results. But I'm having issues with the combination of Skip(), Take() and OrderBy() calls.

一切正常，除了OrderBy()分配得太晚.在结果集被Skip()和Take()缩减后执行.

Everything works fine, except that OrderBy() is assigned too late. It's executed after result set has been cut down by Skip() and Take().

因此，结果的每一页都有顺序排列的项目.但是，排序是在很少的数据页面上进行的，而不是对整个数据集进行排序，然后用Skip()和Take()限制这些记录.

So each page of results has items in order. But ordering is done on a page handful of data instead of ordering of the whole set and then limiting those records with Skip() and Take().

如何设置这些语句的优先级?

var query = ctx.EntitySet.Where(/* filter */).OrderByDescending(e => e.ChangedDate);
int total = query.Count();
var result = query.Skip(n).Take(x).ToList();

一个可能(但不好)的解决方案

一种可能的解决方案是按列顺序应用聚簇索引，但是此列经常更改，这将降低数据库在插入和更新时的性能.而且我真的不想这么做.

One possible (but a bad) solution

One possible solution would be to apply clustered index to order by column, but this column changes frequently, which would slow database performance on inserts and updates. And I really don't want to do that.

我在查询中运行了ToTraceString()，实际上可以看到将order by应用于结果集的情况.不幸的是最后. :(

I ran ToTraceString() on my query where we can actually see when order by is applied to the result set. Unfortunately at the end. :(

SELECT 
-- columns
FROM  (SELECT 
    -- columns
    FROM   (SELECT -- columns
        FROM ( SELECT 
            -- columns
            FROM table1 AS Extent1
            WHERE  EXISTS (SELECT 
                -- single constant column
                FROM table2 AS Extent2
                WHERE (Extent1.ID = Extent2.ID) AND (Extent2.userId = :p__linq__4)
            )
        )  AS Project2
        limit 0,10  ) AS Limit1
    LEFT OUTER JOIN  (SELECT 
        -- columns
        FROM table2 AS Extent3 ) AS Project3 ON Limit1.ID = Project3.ID
UNION ALL
    SELECT 
    -- columns
    FROM   (SELECT -- columns
        FROM ( SELECT 
            -- columns
            FROM table1 AS Extent4
            WHERE  EXISTS (SELECT 
                -- single constant column
                FROM table2 AS Extent5
                WHERE (Extent4.ID = Extent5.ID) AND (Extent5.userId = :p__linq__4)
            )
        )  AS Project6
        limit 0,10  ) AS Limit2
    INNER JOIN table3 AS Extent6 ON Limit2.ID = Extent6.ID) AS UnionAll1
ORDER BY UnionAll1.ChangedDate DESC, UnionAll1.ID ASC, UnionAll1.C1 ASC

推荐答案

我的解决方法

我设法解决了这个问题.不要误会我的意思.到目前为止，我还没有解决优先权问题，但是我已经解决了.

My workaround solution

I've managed to workaround this problem. Don't get me wrong here. I haven't solved precedence issue as of yet, but I've mitigated it.

我做了什么?

这是我一直使用的代码，直到得到 Devart 的答复.如果他们不能解决这个问题，我将不得不最终使用此代码.

This is the code I've used until I get an answer from Devart. If they won't be able to overcome this issue I'll have to use this code in the end.

// get ordered list of IDs
List<int> ids = ctx.MyEntitySet
    .Include(/* Related entity set that is needed in where clause */)
    .Where(/* filter */)
    .OrderByDescending(e => e.ChangedDate)
    .Select(e => e.Id)
    .ToList();

// get total count
int total = ids.Count;

if (total > 0)
{
    // get a single page of results
    List<MyEntity> result = ctx.MyEntitySet
        .Include(/* related entity set (as described above) */)
        .Include(/* additional entity set that's neede in end results */)
        .Where(string.Format("it.Id in {{{0}}}", string.Join(",", ids.ConvertAll(id => id.ToString()).Skip(pageSize * currentPageIndex).Take(pageSize).ToArray())))
        .OrderByDescending(e => e.ChangedOn)
        .ToList();
}

首先，我获得了实体的订购ID.即使使用更大的数据集，仅获取ID的性能也很好. MySql查询非常简单并且执行得非常好.在第二部分中，我对这些ID进行分区，并使用它们来获取实际的实体实例.

First of all I'm getting ordered IDs of my entities. Getting only IDs is well performant even with larger set of data. MySql query is quite simple and performs really well. In the second part I partition these IDs and use them to get actual entity instances.

考虑到这一点，它的性能应该比我一开始就做的更好(如我的问题所述)，因为由于简化了查询，所以获得总数要快得多.第二部分实际上非常相似，除了我的实体不是按Skip和Take ...

Thinking of it, this should perform even better than the way I was doing it at the beginning (as described in my question), because getting total count is much much quicker due to simplified query. The second part is practically very very similar, except that my entities are returned rather by their IDs instead of partitioned using Skip and Take...

希望有人会发现此解决方案有帮助.

Hopefully someone may find this solution helpful.

这篇关于我们可以使用Skip()，Take()和OrderBy()控制LINQ表达式的顺序吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！