Linq to entities query that returns a count and a list of objects

Question

Is it possible to create a linq to entities query that will give me the total count of things in a query, along with a list of some of those items?

For example if the query unconstrained would return 40,000 items but I just want the 5th page of 500 items the object would say Count = 40,000 and List<items> would contain just 500 items.

David · Accepted Answer · 2015-01-27T20:39:50.870

Here is code that returns the specific customer's # of orders, and then a 'page' of those orders. (This will retrieve orders 501 through 600.)

Customer[] CustomerList;
int CustomerID;
var x = CustomerList
   .Where(r => r.CustomerID == CustomerID)
   .Select(r => new { 
      OrderCount = r.Orders.Count(), 
      OrderPageList = r.Orders.Skip(500).Take(100).ToArray() });
int totalCount = x.OrderCount;
Order[] orderList = x.OrderPageList;

score 1 · Answer 2 · edited May 23 '17 at 12:23

Yes it's possible (another example here: Better way to query a page of data and get total count in entity framework 4.1?), but not recommended. It usually takes contrived query constructs to get the count and the data in one LINQ statement (David's answer evades that by only selecting child items).

You better create an IQueryable and get its count and its paged subset separately.

Even better, add PagedList to your project and use code like:

var prd = Products.Where(p => p.Categories.Any(c => c.CategoryName == "Sports" ));
var page = prd.OrderBy(p => p.Name).ToPagedList(5,500);

This simply executes the count and a Skip/Take query to get page 5 of all pages containing 500 items.

The result, an IPagedList<T>, is an IEnumerable<T> that has some useful properties like HasNextPage or PageCount.

Linq to entities query that returns a count and a list of objects

2 Answers2