Better Way to Query a Page of Data and Get Total Count in Entity Framework 4.1

Better way to query a page of data and get total count in entity framework 4.1?

The following query will get the count and page results in one trip to the database, but if you check the SQL in LINQPad, you'll see that it's not very pretty. I can only imagine what it would look like for a more complex query.

var query = ctx.People.Where (p => p.Name.StartsWith("A"));

var page = query.OrderBy (p => p.Name)
                .Select (p => new PersonResult { Name = p.Name } )          
                .Skip(skipRows).Take(pageSize)
                .GroupBy (p => new { Total = query.Count() })
                .First();

int total = page.Key.Total;
var people = page.Select(p => p);

For a simple query like this, you could probably use either method (2 trips to the database, or using GroupBy to do it in 1 trip) and not notice much difference. For anything complex, I think a stored procedure would be the best solution.

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

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; 
   }
   

Get Total Records Count and Custom No of Records in One Database Hit

I guess you are using MS-SQL Server? Well this cannot be done. You will at least need a subselect for count. But that would lead you to a anonymous result type which is kind of ugly.

I recommend you to write a view for such Tables which adds a total count field. Note that MS SQL Server supports write operations for views which only addresses one table. If you have more simply write some stored procedures and assign them to the entity model of the view.

How should I expose the total record count and IEnumable collection of paged records from my service layer method?

You can do something like this

public class Repository<TEntity>
{
   public IEnumerable<TEntity> GetCollection(Expression<Func<TEntity, bool>> filter, 
      int pageSize, int pageIndex)
   {
      return YourDbSet.Where(filter).OrderBy(sortExpression).Skip(pageSize * pageIndex).Take(pageSize);
   }

   public int Count(Expression<Func<TEntity, bool>> filter)
   {
      return YourDbSet.Where(filter).Count();
   }
}

Then You can write an extension method to use both of these methods

public static Pagination<TEntity> GetPagination<TEntity>(this Repository<TEntity> repository, 
   Expression<Func<TEntity, bool>> filter, int pageSize, int pageIndex)
{
   var entities = repository.GetCollection(filter, pageSize, pageIndex);
   var count = repository.Count(filter);

   return new Pagination(entities, pageSize, pageIndex + 1, count);
}

This way you can reuse GetCollection and Count methods independently.
You can build the where condition dynamically. Take a look at my answer

Linq to entities query that returns a count and a list of objects

Here is code that returns the specific customer's # of orders, and then a 'page' of those orders. (This will retrieve orders 501 through 600.)

Customer[] CustomerList;
int CustomerID;
var x = CustomerList
   .Where(r => r.CustomerID == CustomerID)
   .Select(r => new { 
      OrderCount = r.Orders.Count(), 
      OrderPageList = r.Orders.Skip(500).Take(100).ToArray() });
int totalCount = x.OrderCount;
Order[] orderList = x.OrderPageList;

---

Related Topics