Page of Results and Total Count

Can you recommend the most performant way to return a "total count" on a query that is returning a paged list.  I'm using linq skip and take to get the page.  Just looking for the best way to also get the total count with as little extra work as possible from the DB.

Thanks!

Eric

Use the Count() operator:

// total number
var count = unitOfWork.Penguins.Count()

// total number matching query criteria
var count = (from unitOfWork.Penguins
                 where p.LooksLikeElvis == true
                 select p).Count()

This will result in LightSpeed sending SELECT COUNT(*) to the database which is going to be as efficient as you can get.

This will result in an additional round-trip on top of the actual paged query, but there's no way round that at the moment: you can't bundle up a count and a paged query into a single operation and have the database engine process them together.

Note that because LINQ operators are composable, you can avoid reuse the same query definition in both the count and paged queries:

var myQuery = BuildQueryBasedOnUICriteria();
var count = myQuery.Count();
var pagedResults = myQuery.Skip(startIndex).Take(pageSize);

This doesn't make any different to performance, but it does help with reducing code duplication.

Thanks Ivan, that's pretty much what I'm doing.