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

Getting COUNT and SKIP TAKE in one operation with Linq to Entities(使用 Linq to Entities 在一次操作中获取 COUNT 和 SKIP TAKE)

问题描述

我在 Linq to Entities 支持的数据访问层中有一个数据调用，旨在进行分页调用.

I have a data call in a Linq to Entities powered data access layer that is designed to make paged calls.

在此过程中，我需要选择数据的一个子集，比如 50 行，但还要获取所有匹配项的计数，以了解要分页的总匹配项数.

In doing so, I need to select a subset of the data, say 50 rows, but also get the count of all matches to know how many total matches exist to be paged through.

目前，我正在做以下事情:

Currently, I'm doing the following:

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 操作花费的时间更长.

This results in two costly database operations. The COUNT operation for some reason actually takes longer than the SELECT operation.

有没有办法在同一操作中获取计数和子集?

Is there a way to get count and a subset in the same operation?

对我来说，逻辑流程表明我们执行以下操作:

The logical flow to me says we do the following:

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

这在一个操作中似乎是可能的，但我不知道如何.

This seems possible in one operation, but I cannot figure out how.

尝试一，慢

尝试了 D Stanley 将完整结果集转换为 List 并在分页中进行计数和内存的建议，但它的速度大约慢了 2 倍(平均 6.9 秒 vs 平均 3.9 秒)

Tried D Stanley's suggestion of casting the full result set to a List and doing count and memory in paging, but it is roughly 2x slower (6.9s avg vs 3.9s avg)

值得一提的是，该数据集大约有 25,000 条记录，在 JOIN 中搜索了十几个相关表.

It's worth mentioning the data set is roughly 25,000 records, with over a dozen related tables that are searched in a JOIN.

推荐答案

这可能是可能的，但由于您使用的标准，它可能不会更快.由于您要在列值中搜索文本，因此不能使用索引，因此必须进行表扫描.您可以执行单个查询以获取 所有 记录并在 linq-to 中执行 Count 和 Skip/Take-对象:

It may be possible, but it probably won't be much faster because of the criteria you're using. Since you're searching for text within a column value, you cannot use an index and thus must do a table scan. You could do a single query to get all records and do the Count and Skip/Take in linq-to-objects:

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

但您必须尝试一下，看看它是否会更快.

but you'd have to try it to see if it would be any faster.

这篇关于使用 Linq to Entities 在一次操作中获取 COUNT 和 SKIP TAKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！