Server-side paging with Entity Framework

Source code for article – https://github.com/RossWhitehead/ServerSidePaging

(Note. I deleted the original source code as part of a clean-up of my repos. It was very cathartic but I got a little carried away. I’ve recreated the code in the repo above. But a few of the objects are named differently. Sorry!)

In my last post – Unit of Work, Repository, Entity Framework, and Persistence Ignorance – I presented a generic entity framework implementation of the repository pattern. In this post I’m going to present some changes to this implementation to enable server-side paging.

I have a large data-set that I wish to display in a table or grid. To minimize the size of the response, and to improve user experience, I want to present the data one page at a time, and allow the user to paginate back and forth through the data-set.

Below you can see that I am presenting the 1st page of 5 products from my BikeStore in a grid. If I click on the next button I will see the next page. (The eagle-eyed amongst you will notice that I only have 6 products in total. Ignore this and pretend I have a large data-set.)

Paging

I am using the Telerik Kendo UI Grid, and I can retrieve a list of products, and bind it to the grid using the Get() method of the aforementioned repository. So code similar to that shown below –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    IEnumerable products = unitOfWork.ProductRepository.Get();
    return View(products);
}

The Grid is clever enough to determine the number of pages and display accordingly. There are a number of other grid helpers on the market which also do this work for us.

However, the paging is being done on the client, and each time the user clicks on a pager button the full product list is requested from the server – via an ajax call in this case – even though only a single page will be displayed. This will obviously be very inefficient and very slow with large data-sets, as all products will be retrieved from the database every time the user navigates to a new page.

How can we improve the performance?

The Get() method returns IEnumerable. We could modify the Get() method to instead return IQueryable. In so doing we are deferring execution, and we can then allow the consumer to restrict the query using the Skip and Take methods. This does in fact simplify our entity framework implementation of repository. Also, it works well with some grid helpers – such as Grid.Mvc – which emits the Skip and Take automatically.

However, the problem with this is that we are assuming that the data provider supports IQueryable. Entity Framework does, but what if we are required to switch to provider that does not. Also, mocking an IQueryable interface is not a simple task and so our unit testing becomes more difficult.

I have chosen not to change the repository to return IQueryable. But instead allow the consumer to request a page of data, and restrict the IEnumerable set accordingly.

IRepository

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace Framework.Data
{
    public interface IRepository<TEntity> where TEntity : class
    {
        IEnumerable<TEntity> Get();

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null);

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null);

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null,
           int? page = 0,
           int? pageSize = null,
           params SortExpression<TEntity>[] sortExpressions);

        void Add(TEntity entity);

        void Alter(TEntity entity);

        void Remove(TEntity entity);
    }
}

As before, my IRepository is returning IEnumerable. However, I have overloaded the Get method. The final overload, which enables server-side paging, accepts the following parameters –

  • Expression<Func<TEntity, bool>> filter = null
    • A Linq expression defining a filter
  • string[] includePaths = null
    • An array of strings, with each string denoting an include path for a related entity
  • int? page = 0
    • The required page of data.
  • int? pageSize = null
    • The page size.
  • params SortExpression<TEntity>[] sortExpressions
    • A variable number of SortExpression instances, with each instance denoting a Linq Expression to sort and a sort direction. The SortExpression class is shown below.

SortExpression<TEntity>

using System;
using System.ComponentModel;
using System.Linq.Expressions;

namespace Framework.Data
{
    public class SortExpression<TEntity> where TEntity : class
    {
        public SortExpression(Expression<Func<TEntity, object>> sortBy, ListSortDirection sortDirection)
        {
            SortBy = sortBy;
            SortDirection = sortDirection;
        }

        public Expression<Func<TEntity, object>> SortBy { get; set; }
        public ListSortDirection SortDirection { get; set; }
    }
}

Repository

The generic entity framework implementation of the repository is as follows –

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace Framework.Data
{
    public class EFRepository<TEntity> : IRepository<TEntity> where TEntity : class
    {
        private DbContext db;
        private DbSet<TEntity> dbSet;

        public EFRepository(DbContext context)
        {
            this.db = context;
            this.dbSet = context.Set<TEntity>();
        }

        public IEnumerable<TEntity> Get()
        {
            return Get(null, null, null, null, null);
        }

        public IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null)
        {
            return Get(filter, null, null, null, null);
        }

        public IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            string[] includePaths = null)
        {
            return Get(filter, includePaths, null, null, null);
        }

        public IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null,
           int? page = null,
           int? pageSize = null,
           params SortExpression<TEntity>[] sortExpressions)
        {
            IQueryable<TEntity> query = dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            if (includePaths != null)
            {
                for (var i = 0; i < includePaths.Count(); i++)
                {
                    query = query.Include(includePaths[i]);
                }
            }

            if (sortExpressions != null)
            {
                IOrderedQueryable<TEntity> orderedQuery = null;
                for (var i = 0; i < sortExpressions.Count(); i++)
                {
                    if (i == 0)
                    {
                        if (sortExpressions[i].SortDirection == ListSortDirection.Ascending)
                        {
                            orderedQuery = query.OrderBy(sortExpressions[i].SortBy);
                        }
                        else
                        {
                            orderedQuery = query.OrderByDescending(sortExpressions[i].SortBy);
                        }
                    }
                    else
                    {
                        if (sortExpressions[i].SortDirection == ListSortDirection.Ascending)
                        {
                            orderedQuery = orderedQuery.ThenBy(sortExpressions[i].SortBy);
                        }
                        else
                        {
                            orderedQuery = orderedQuery.ThenByDescending(sortExpressions[i].SortBy);
                        }

                    }
                }

                if (page != null)
                {
                    query = orderedQuery.Skip(((int)page - 1) * (int)pageSize);
                }
            }

            if (pageSize != null)
            {
                query = query.Take((int)pageSize);
            }

            return query.ToList();
        }

        public virtual void Add(TEntity entity)
        {
            this.dbSet.Add(entity);
        }

        public virtual void Remove(TEntity entityToDelete)
        {
            if (this.db.Entry(entityToDelete).State == EntityState.Detached)
            {
                this.dbSet.Attach(entityToDelete);
            }
            this.dbSet.Remove(entityToDelete);
        }

        public virtual void Alter(TEntity entityToUpdate)
        {
            this.dbSet.Attach(entityToUpdate);
            this.db.Entry(entityToUpdate).State = EntityState.Modified;
        }

    }
}

Please note that the page parameter will be ignored if there is not at least one SortExpression. This is because the Skip will raise an exception is the query is not ordered.

IUnitOfWork, IBikeStoreUnitOfWork, BikeStoreUnitOfWork, and BikeStoreContext do not require any modifications. For completeness I have listed these below.

IUnitOfWork

using System;

namespace Framework.Data
{
    public interface IUnitOfWork
    {
        void Save();
    }
}

IBikeStoreUnitOfWork

using BikeStore.Model;
using Framework.Data;

namespace BikeStore.Data
{
    public interface IBikeStoreUnitOfWork : IUnitOfWork
    {
        IRepository ProductRepository { get; }
        IRepository BasketRepository { get; }
        IRepository CustomerRepository { get; }
    }
}

BikeStoreUnitOfWork

using BikeStore.Model;
using Framework.Data;
using System;
using System.Data.Entity.Core.Objects;

namespace BikeStore.Data
{
    public class BikeStoreUnitOfWork : IBikeStoreUnitOfWork, IDisposible
    {
        private BikeStoreContext _db = new BikeStoreContext();

        private EFRepository _productRepository;
        private EFRepository _basketRepository;
        private EFRepository _customerRepository;

        public IRepository ProductRepository
        {
            get
            {
                if (_productRepository == null)
                    _productRepository = new EFRepository(_db);
                return _productRepository;
            }
        }

        public IRepository BasketRepository
        {
            get
            {
                if (_basketRepository == null)
                    _basketRepository = new EFRepository(_db);
                return _basketRepository;
            }
        }

        public IRepository CustomerRepository
        {
            get
            {
                if (_customerRepository == null)
                    _customerRepository = new EFRepository(_db);
                return _customerRepository;
            }
        }

        public void Save()
        {
            _db.SaveChanges();
        }

        private bool _disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    _db.Dispose();
                }
            }
            this._disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

BikeStoreContext

using BikeStore.Model;
using System.Data.Entity;

namespace BikeStore.Data
{
    public class BikeStoreDbContext : DbContext
    {
        public BikeStoreDbContext() : base("DefaultConnection")
        { }

        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<Basket> Baskets{ get; set; }
        public virtual DbSet<Customer> Customers{ get; set; }
    }
}

Example of Server-side Paging

And this is an example of how to use the repository to request a page of entities –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    IEnumerable products = unitOfWork.ProductRepository.Get (
        p => p.Price > 2000,
        null, // e.g. new string[] { "ProductCategory", "ProductSubCategory.ProductCategory" },
        2,
        10,
        new SortExpression(p => p.Name, ListSortDirection.Ascending),
        new SortExpression(p => p.Description, ListSortDirection.Descending));
}

I am filtering the product list to only retrieve products priced over £2000. I’m requesting the inclusion of the ProductSubCategory and ProductSubCategory.ProductCategory entities. I’m specifying that the entities be ordered by Name ASC and then Description DESC, and I’m requesting the 2nd page of 10 entities.

And here’s the resulting query –

SELECT
    [Extent1].[ProductId] AS [ProductId],
    [Extent1].[Name] AS [Name],
    [Extent1].[Description] AS [Description],
    [Extent1].[Price] AS [Price],
    [Extent1].[ProductSubCategoryId] AS [ProductSubCategoryId],
    [Extent2].[ProductSubCategoryId] AS [ProductSubCategoryId1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[ProductCategoryId] AS [ProductCategoryId],
    [Extent3].[ProductCategoryId] AS [ProductCategoryId1],
    [Extent3].[Name] AS [Name2]
    FROM   [dbo].[Product] AS [Extent1]
    INNER JOIN [dbo].[ProductSubCategory] AS [Extent2] ON [Extent1].[ProductSubCategoryId] = [Extent2].[ProductSubCategoryId]
    INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent2].[ProductCategoryId] = [Extent3].[ProductCategoryId]
    WHERE [Extent1].[Price] > cast(2000 as real)
    ORDER BY [Extent1].[Name] ASC, [Extent1].[Description] DESC
    OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Advertisements

4 thoughts on “Server-side paging with Entity Framework

  1. Hi Ross thanks for your very nice articles explaining the patterns with EF. While trying to follow your guide from scratch, I am now missing and tried While following your I followed your article however I am missing the class TEntity, which was not

    Like

  2. Very easy to followup and understand concepts, however the code example above don’t compile un VS 2013 framework 4.5

    Unfortunately no code was provided, nor the version of packages used at the time the article was written.

    Like

    • Hi, Apologies for taking so long to reply. I’ve been on holiday, so mostly off the grid.
      I’m afraid there were a few code error in my example. A combination of WordPress reformating the code snippets, and my mixing up of code bases.
      I don’t have the original project any more, so I have put together another – not too dissimilar – project which demonstrates the concepts discussed. You can find the code here – https://github.com/RossWhitehead/ServerSidePaging. I plan to add a description of the features to the README.md when I get a moment spare.
      FYI, the project is a VS 2015 .NET 4.61 project. But there’s nothing in the code that will not work with 4.5.
      Regards, Ross

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s