在数据库的单次往返中执行两个查询

I have the following code to perform a full-text search. It creates a query, gets the total number of rows returned by that query and then retrieves the actual rows for only the current page.

我有以下代码来执行全文搜索。它创建一个查询,获取该查询返回的总行数,然后仅检索当前页面的实际行数。

// Create IQueryable
var query = from a in ArticleServerContext.Set<Article>()
            where a.Approved
            orderby a.UtcDate descending
            select a;

// Get total rows (needed for pagination logic)
int totalRows = query.Count()

// Get rows for current page
query = query.Skip((CurrentPage - 1) * RowsPerPage).Take(RowsPerPage);

This works fine, but it requires two round trips to the database. In the interest of optimizing the code, is there any way to rework this query so it only had one round trip to the database?

这工作正常,但它需要两次往返数据库。为了优化代码,有没有办法重新编写这个查询,所以它只有一次往返数据库?

2 个解决方案

#1

Yes, you can perform this two operations with the help of the only one query to database:

是的,您可以在对数据库的唯一一个查询的帮助下执行这两个操作:

// Create IQueryable
var query = from a in ArticleServerContext.Set<Article>()
            where a.Approved
            orderby a.UtcDate descending
            select new { a, Total = ArticleServerContext.Set<Article>().Where(x => x.Approved).Count() };

//Get raw rows for current page with Total(Count) field
var result = query.Skip((CurrentPage - 1) * RowsPerPage).Take(RowsPerPage).ToList();

//this data you actually will use with your logic 
var actualData = result.Select(x => x.a).ToList();

// Get total rows (needed for pagination logic)
int totalRows = result.First().Total;

If you use MSSQL query wil be look that way:

如果您使用MSSQL查询将看起来那样:

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[UtcDate] AS [UtcDate], 
    [Extent1].[Approved] AS [Approved],     
    [GroupBy1].[A1] AS [C1]
    FROM  [dbo].[Articles] AS [Extent1]
    CROSS JOIN  (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Articles] AS [Extent2]
        WHERE [Extent2].[Approved] ) AS [GroupBy1]
    WHERE [Extent1].[Approved]
    ORDER BY [Extent1].[UtcDate] DESC

#2

I'm not sure whether it's worth enough, but it's doable under the following constraints:

我不确定它是否足够值,但在以下限制条件下它是可行的:

(1) CurrentPage and RowsPerPage are not affected by the totalRows value.
(2) The query is materialized after applying the paging parameters.

(1)CurrentPage和RowsPerPage不受totalRows值的影响。 (2)在应用寻呼参数后实现查询。

The trick is to use group by constant value, which is supported by EF. The code looks like this:

诀窍是使用由恒定值组成的组,由EF支持。代码如下所示:

var query = 
    from a in ArticleServerContext.Set<Article>()
    where a.Approved
    // NOTE: order by goes below
    group a by 1 into allRows
    select new
    {
        TotalRows = allRows.Count(),
        PageRows = allRows
            .OrderByDescending(a => a.UtcDate)
            .Skip((CurrentPage - 1) * RowsPerPage).Take(RowsPerPage)
    };

var result = query.FirstOrDefault();
var totalRows = result != null ? result.TotalRows : 0;
var pageRows = result != null ? result.PageRows : Enumerable.Empty<Article>();

#1