Performing two queries in a single round trip to the database

Question

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?

Answer 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:

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