Linq to SQL - How to get total number of results when paging without two queries

I've got the following L开发者_开发问答INQ query to my IQueryable service

Dim eventcount = EventService.QueryEvents().Count
Dim eventlist = EventService.QueryEvents().Skip((page - 1) * 5).Take(5).ToList   

But I'm wondering if there's a way to do this without making two calls to the database?

I would consider implementing level 2 caching on the count, being careful to get the caching key to contain the relevant criteria, and invalidating it appropriately.

Well i was facing the same issue some time back and then i came up with LINQ to SP =).Make and SP and drop that into your entities and use it. you can write Sp according to your need like pulling your records and total record column too. It is more easy and fast as compare to that whet you are using right now.

I was having the same issue last year and then when it came to performance, then instead of making two queries, I moved to LINQ to SP, and made a stored procedure and called it through entities like LINQ to stored procedure =). And my stored procedure did all and returned only those records that I wanted and I did paging to at the stored procedure end so that I got limited data at a time rather than all data at application.

You can make and SP and drop that into your entities and use it. you can write Sp according to your need like pulling your records and total records too as column . Consider having 1000K of records of 100 of tables. Slow performance ?? you can do that by Linq to SQL by making a Sp at database level and calling through entities. It will be much faster then the one you trying to achieve =)