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

Question

I have the following LINQ query for my IQueryable service

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

But I wonder if there is a way to do this without two calls in the database?

Answer 1

I would think about using level 2 caching in a counter, being careful that the cache key contains the appropriate criteria and is accordingly invalid.

Answer 2

Well, I ran into the same problem a while ago, and then I came up with LINQ for SP =). Do both SP and put it in your entities and use it. you can write Sp according to your needs, for example, pull your records and the column with the general record. It is simpler and faster than what you are using right now.

I had the same problem last year, and then when it came to performance, instead of making two queries, I switched to LINQ to SP and made a stored procedure and called it through objects like LINQ for the stored procedure =). And my stored procedure did everything and returned only those records that I wanted, and I paged at the end of the stored procedure to get limited data at the same time, and not all the data in the application.

You can do both SP and delete it in your objects and use it. you can write Sp according to your needs, for example, pull your records and full records also as a column. Think about whether there are 1000K records from 100 tables. Slow performance? you can do this with Linq to SQL by creating a Sp at the database level and invoking entities. It will be much faster than the one you are trying to achieve =)