alex2
10/26/2017 - 2:37 AM

Get total count in 1 trip

// Source: https://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1/7771298#7771298

// The following query will get the count and page results 
// in one trip to the database, but if you check the 
// SQL in LINQPad, you'll see that it's not very pretty. 
// I can only imagine what it would look like for a more 
// complex query.

var query = ctx.People.Where (p => p.Name.StartsWith("A"));

var page = query.OrderBy (p => p.Name)
                .Select (p => new PersonResult { Name = p.Name } )          
                .Skip(skipRows).Take(pageSize)
                .GroupBy (p => new { Total = query.Count() })
                .FirstOrDefault();

int total = page.Key.Total;
var people = page.Select(p => p);