Developers Forum


Paging Entity Results

 
Like 0 Dislike
When we were setting up paging for the users grid, the social wall, and the wiki history we were noticing that the paging code we have in the data layer was loading the entire result set unpaged. This of course is a major scalabity problem. We found a solution to this problem while solving another problem of doing generic sorting on entities using expression trees.

As I am sure you know by now you have to sort before doing a skip in entity. If you check out the QueryExtensions in MvcCms.Service.Code (where we got this from) you will see we are calling it like below. Using the QueryExtensions SortBy<T> method keeps the paging from pulling the entire table. We also have setup a custom sproc to get the total rows for the result set as this performs better and keeps the paging code from pulling everything into an entity.

            var messages = QueryExtensions  .SortBy
                                            (
                                                _repository.ListSocialMessages(userId)
                                                .AsQueryable(), "AddedDate DESC"
                                            )                                     
                                            .AsPagination(pageNumber, resultsPerPage)
                                            .ToList();
2 years 4 months 15 days ago by jon

Replies

Like 1 Dislike
Reply posted by jon 1 years 7 months 1 days ago View Branch And Reply
If you sort in the repository though the service can not have to do this.

So now paging code is much better. Here is a snippet from a fix done for this forum...

            var query = _repository.ListReplies(threadId).AsPagination(index, count);
            var totalPages = query.TotalPages;
            var totalItems = query.TotalItems;
            var posts = query.ToList();


We did confirm with sql profiler that the main query was only returning the requested page and not the entire set so it all works great.


 

© MvcCms