How to return partial results and the total count for a query with Entity Framework?

Question

I have this query with Entity Framework:

_context.Onlines
                .Where(x => x.Date >= startDate.Date && x.Date <= endDate.Date && x.User.Id == userId)
                .OrderByDescending(x => x.Date)
                .Skip((int)size * (page - 1))
                .Take((int)size)
                .ToList();

How you can see, I'm doing a pagination search with "Skip" and "Take", and I need to enjoy this query to count the total results for this query (not only the partial count with pagination), and return the results, everything in the same request to the database.

I need this for calculating the pagination in the View.

Answer 1

You can do it in such way:

var query = _context.Onlines
    .Where(x => x.Date >= startDate.Date && x.Date <= endDate.Date && x.User.Id == userId);

// getting just count
int totalCount = query.Count();

// and then getting items itself
var items = query
    .OrderByDescending(x => x.Date)
    .Skip((int)size * (page - 1))
    .Take((int)size)
    .ToList();

It will produce two SQL queries based on original conditions. One for count, and second one for items.

Answer 2

There's a way you can do this in one transaction using Entity Framework Extended. It's a nuget package you can find. I'll walk you through what we decided on doing for our server side pagination.

We need the list that is returned and the count of total records. A KeyValuePair<int, List<WhateverReturnModel>> should work nicely.

Lets take a look at what our requests should now take.

1. Create a pagination request model that has a default PageNumber of x and a PageSize of z.

   public class PagedRequest
   {
       private int? _pageNumber;
       private int? _pageSize;
       private int? _pageSkip;
   
       public int PageNumber
       {
           get { return this._pageNumber ?? 1; }
           set { this._pageNumber = value; }
       }
   
       public int PageSize
       {
           get { return this._pageSize ?? 15; }
           set { this._pageSize = value; }
       }
   }
   

2. Setup your return method to handle the KeyValuePair result

   protected KeyValuePair<int, List<T>> PagedResult<T>(int count, List<T> list)
   {
       return new KeyValuePair<int, List<T>>(count, list);
   }
   

3. Add these to your endpoints that require paging

   public KeyValuePair<int, List<WhateverModel>> DoSomething([FromUri] PagedRequest paged) 
   {
       var records = yourContext.YourTable.Where(t => true);
   
       var count = records.FutureCount() // this will not execute right away.. only when it is finally called
   
       var data = yourContext.YourTable
                             .Where(t => t.Something)
                             .OrderBy(i => i.Anything)
                             .Skip(this.PageSkip(paged.PageNumber, paged.PageSize))
                             .Take(paged.PageSize)
                             .Future() // again this will not execute right away
   
       return this.PagedResult(count, data.ToList()); // now both queries will execute in one call
   }
   

4. An API method consuming the paginated method.

   public HttpResponseMessage DoAnotherThing() 
   {
       var test = new WhateverClass.DoSomething();
       return this.Paged(test);
   }
   

5. You can then write the method Paged() where it will return the KeyValuePair Value as the response Content and you can add a header for the total count. Something like this..

   protected HttpResponseMessage OkPaged<T>(KeyValuePair<int, List<T>> content)
     var response = new HttpResponseMessage(HttpStatusCode.OK)
     {
         Content = new ObjectContent<Object>(content.Value, this.GetConfiguration().Formatters.JsonFormatter)
     };
   
     response.Headers.Add("x-paging-total", content.Key.ToString());
   
     return response; 
   }
   

Answer 3

Doing both in the single query will not yield any performance benefit. However, the pagination data can be captured in an elegant way using a nuget package EntityFrameworkPaginate. The way it works is you set up your dynamic filtering and sorting and you will get a Page object as result which will have the result along with the metadata. (CurrentPage, PageCount, PageSize, RecordCount and Results).

First you need to setup your filters. In your case you have one condition. In scenario where you have multiple where clause based on different conditions, you can add those filters to the Filters object.

 var filters = new Filters<Onlines>(); 
 filters.Add(true, x => x.Date >= startDate.Date 
                  && x.Date <= endDate.Date && x.User.Id == userId);

Next, you need to set up your order by. Again in your case there is just one, but Sorts class can handle dynamic sorting as well.

 var sorts = new Sorts<Onlines>();
 sorts.Add(true, x => x.Date, true);

Now to finally get your paginated data, you need to call the extension method paginate on your dbset.

Page<Onlines> paginatedData = context.Employees.Paginate(page, size, sorts, filters);

Voila, you will get the paginated data with all the required metadata. This provides a clean approach and is very helpful when you have dynamic filtering and sorting. You can check the detailed article here.