使用Linq to Entities一次获取COUNT和SKIP TAKE的操作

Question

我有一个基于Linq to Entities的数据访问层中的数据调用，旨在进行分页调用。

这样做，我需要选择数据的子集，例如50行，但也要获取所有匹配项的计数，以知道有多少总匹配项需要进行分页。

目前，我的做法如下：

var queryResult = DatabaseContext.Table
    .Where(x => !x.IsDeleted)
    .Where(p => (
            p.PropertyOne.ToLower().Contains(query) ||
            p.PropertyTwo.ToLower().Contains(query) 
            ));

int count = queryResult.Count();

var returnData = queryResult
    .OrderBy(i => i.ID)
    .Skip(start).Take((length))
    .Select(y => new ObjectDTO
    {
        PropertyOne = y.PropertyOne,
        PropertyTwo = y.PropertyTwo
    }
    .AsEnumerable();

这将导致两个昂贵的数据库操作。由于某种原因，COUNT操作实际上比SELECT操作需要更长的时间。有没有办法在同一操作中获取计数和子集？在逻辑上，我们应该按照以下步骤进行：

• 查看表格
• 查找符合条件的表格项目
• 获取所有匹配项的数量
• 返回一个编号的匹配项子集

这似乎可以在一个操作中实现，但我无法想出如何做到。尝试过D Stanley的建议，将完整结果集强制转换为List并进行计数和分页内存，但速度大约慢了2倍（6.9s平均值与3.9s平均值）。值得一提的是，数据集大约有25,000条记录，并且有十几个相关表格在JOIN中被搜索。

- Wesley

---

如果将queryResult枚举到List<T>并在列表上使用.Count，速度会更快吗？ - mausworks

FYI，计数需要更长时间，因为它必须遍历表中的所有行，而第二个可以在获取“start”+“length”匹配项后立即停止。 - juharr

@diemaus 这会拉取比需要的更多的数据，并且根据数据量的大小可能会更慢。 - juharr

代码中没有使用 count 的值。我的意思是 int count = queryResult.Count();。 - Hui Zhao

@HuiZhao 这发生在后面，这里不相关。 - Wesley

Answer 1

这可能是可行的，但由于您使用的条件，它可能不会更快。由于您正在搜索列值中的文本，因此无法使用索引，必须进行表扫描。您可以使用单个查询在linq-to-objects中获取所有记录并执行Count和Skip/Take操作：

var queryResult = DatabaseContext.Table
    .Where(x => !x.IsDeleted)
    .OrderBy(i => i.ID)
    .Where(p => (
            p.PropertyOne.ToLower().Contains(query) ||
            p.PropertyTwo.ToLower().Contains(query) 
            ))
    .ToList();

int count = queryResult.Count();  // now this will be a linq-to-objects query

var returnData = queryResult
    .Skip(start).Take((length))
    .AsEnumerable();

但你需要尝试一下才能确定它是否更快。

- D Stanley

---

但是，如果表中没有几行数据，这当然就成了问题。 - Farhad Jabiyev

一旦调用了.ToList()，你就在内存中得到了一个列表。只需访问Count属性而不是调用.Count()方法--虽然LINQ应该会看到底层属性并使用它。 - afrazier

然后只需将Select添加到基本查询中 - 这样你只传输需要的列，不会影响Count。 - D Stanley

我会两种方式都尝试一下，然后告诉你。 - Wesley

很遗憾，@DStanley，在大多数表格上进行列表转换和内存过滤实际上更慢。 - Wesley

Answer 2

这样怎么样：

db.Products
   .Take(10)
   .Select(p => new 
                {
                    Total = db.Products.Count, 
                    Product = p
                })

如果这不好，了解一下在SQL中，您可以使用OVER()获取总结果和其中一页的结果。

- MGOwen

Answer 3

我已经非常努力地尝试自己找到答案，但没有成功。

我决定这是LINQ to Entities的一个限制，所以我选择了使用存储过程，而不是在LINQ上花更多时间。

在存储过程中，我会做类似这样的操作。

--Get expensive query into temp table
SELECT *
INTO #t
FROM table
WHERE <expensive query here>

--Set the output param of your stored procedure
SET @totalRows = (SELECT COUNT(*) FROM #t);

--Select your final result set
SELECT *
FROM #t
OFFSET (0) ROWS
FETCH NEXT (50) ROWS ONLY;

--Cleanup
DROP TABLE IF EXISTS #t;

- Kevin UI