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

Unit of Work, Repository, Entity Framework, and Persistence Ignorance

Let me lay my cards on the table. I’m not an expert on design patterns. But I’ve read some books, some blog posts, and followed many a debate online. I’ve developed a good few applications and have generally gained a good appreciation for the principles, what we are trying to achieve, and what works in practice.

I’m going to present my go-to implementation when starting a new solution. I will discuss the basics of the patterns and principles. Just enough to answer the Why questions.

I am not presenting anything new or unique. These are tried and tested patterns which are industry best practice. If the code is similar, or even the same, as code published by other sources then I apologize for not referencing those sources. I have used this implementation over a number of years and so would not be able to tell you where the ideas came from, or which snippets of code have been copied or created from scratch.

My choice of weapons at the moment are ASP.NET MVC, C#, Entity Framework (Code First), Unity, and Moq. However, the data persistence layer and service layer that I will present is mostly technology agnostic, so it should be easy enough to implement the classes using other technologies.

I will be presenting this information in a series of posts –

1) Unit of Work, Repository, Entity Framework, and Persistence Ignorance

2) Service Layer, MVC Controllers, and Dependency Injection

3) Unit Testing using Moq

Unit Of Work

According to Fowler Unit of Work –

“Maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems.

When you’re pulling data in and out of a database, it’s important to keep track of what you’ve changed; otherwise, that data won’t be written back into the database. Similarly you have to insert new objects you create and remove any objects you delete.”

The Entity Framework’s DbContext class is essentially an implementation of Unit of Work. It manages the connection to the database. It populates entity objects from the database as and when needed. It tracks changes to the entity objects. And it persists these changes to the database, managing transactions and concurrency when doing so.

So most of the work is done for us.

The recommended way of working with DbContext is to derive a class from DbContext and expose the required entities as DBSet properties. So for my BikeStore application, I could create the following class –

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

namespace BikeStore.Data
{
    public class BikeStoreContext : DbContext
    {
        public virtual DbSet<Basket> Baskets { get; set; }
        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<Customer> Customers{ get; set; }
    }
}

I could then create an instance of this class, and then query, add, modify, and delete entities using this instance.

using(var context = new BikeStoreContext())
{
    Product product = context.Products.Where(p => p.ProductId == 1).First();
    product.Name = "CAAD 10";
    context.SaveChanges();
}

If I’m developing using ASP.NET MVC I may choose to include this code in a controller action method, or maybe refactor it a little and include it in a helper class or even in a business logic or service layer class.

The problem with the above is that the DbContext and DbSet classes are specific to the System.Data.Entity library. We would need to reference this library in the controller, and as such the controller would now be aware of and dependent on Entity Framework. If we had to switch to another data persistence technology, such as nHibernate, then we would need to re-write and re-test all controller methods that are now dependent on Entity Framework. Admittedly, I have never yet had to do this. But never say never.

A bigger concern is that by creating a dependency between the controllers and Entity Framework, we are making our lives very difficult when it comes to unit testing. We will find it very difficult to isolate the controllers from the database.

So how do we decouple the controllers from the persistence layer, and hence make the controllers persistence ignorant?

IUnitOfWork

The first thing to do is create an IUnitOfWork interface. It’s a very simple interface, with a single method – Save().

using System;

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

Later on we will create a concrete Entity Framework implementation of this interface, wrapping up the DbContext. DbContext would essentially be hidden from the controllers, and as such the dependency will have been removed.

IRepository

But we still need to remove the dependency on DBSet whilst allowing the querying, adding, modifying, and deleting of entities.

We will use the repository pattern to achieve this. According to Fowler, the Repository pattern –

“Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.”

Rather than DBSet being the interface for accessing the entities, we will use IEnumerable.

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

namespace Framework.Data
{
    public interface IRepository<TEntity> where TEntity : class
    {
        IEnumerable<TEntity> Get(
            Expression<func<TEntity, bool>> filter = null,
            Func<iqueryable<TEntity>, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        IEnumerable<TEntity> GetAll(
            Func<iqueryable<TEntity>, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        IEnumerable<TEntity> GetTopN(
            int? N,
            Expression<Func<TEntity, bool>> filter = null,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        void Add(TEntity entity);

        void Alter(TEntity entity);

        void Remove(TEntity entity);
    }
}

The first method in the interface, Get, enables the retrieving of a collection of entities. It has 3 optional parameters –

  • filter – which accepts a lambda expression defining which set of entities to retrieve
  • orderBy – which defines how the set should be ordered
  • includeProperties – which accepts a comma-delimited string of related property names to retrieve with each entity.

As its name suggests, the second method, GetAll, enables the retrieving of all entities. Concrete implementations of this method may simple call the Get method. It’s not necessary to include this method in you implementation. It’s there only for convenience.

GetTopN accepts the same parameters as Get, and additionally has a parameter which defines the number of entities to return.

Add, Alter, and Remove all accept an entity, and as their names suggest enable the modification of entities.

EFRepository

We have defined a generic IRespository interface, and could now use this for creating a concrete Entity Framework implementation of Repository for each entity in our system. But we can simplify things by creating a generic Entity Framework implementation. So a single generic class rather than one class per entity.

The generic Entity Framework implementation shown below implements a constructor that take an instance if DbContext, and it used this instance to access the required DbSet.

We will create a  an instance of EFRepository for each entity in our model, and pass the same DbContext into each. We can then save changes to different entity types in a single transaction.

using System.Linq.Expressions;

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

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

        public virtual IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            IQueryable<TEntity> query = _dbSet;

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

            if (includeProperties != null)
                foreach (var includeProperty in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                    query = query.Include(includeProperty);

            if (orderBy != null)
                orderBy(query);

            return query;
        }

        public virtual IEnumerable GetAll(
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            return this.Get(null, orderBy, includeProperties);
        }

        public virtual IEnumerable GetTopN(
            int? N,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            if (N == null)
                return this.Get(null, orderBy, includeProperties);
            else
                return this.Get(null, orderBy, includeProperties).Take((int)N);
        }

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

        public virtual void Remove(TEntity entityToDelete)
        {
            if (_db.Entry(entityToDelete).State == EntityState.Detached)
                _dbSet.Attach(entityToDelete);
            _dbSet.Remove(entityToDelete);
        }

        public virtual void Alter(TEntity entityToUpdate)
        {
            _dbSet.Attach(entityToUpdate);
            _db.Entry(entityToUpdate).State = EntityState.Modified;
        }
    }
}

IBikeStoreUnitOfWork

If you look at the namespaces for the classes shown so far, you’ll see that I have included all the classes in the Framework.Data namespace. This is because there is nothing that is application specific.

I started this article by presenting the BikeStoreContext class, and pointing out that by consuming it in our controllers we are tying the controllers to Entity Framework.

I will now replicate the BikeStoreContext class but in a way that hides the implementation of the persistence technology.

Firstly, let’s create an IBikeStoreUnitOfWork interface, which exposes an IRepository class for each entity in our BikeStore model (I have only shown a few of the entities to keep the examples short) –

using BikeStore.Model;
using Framework.Data;

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

BikeStoreUnitOfWork

We now want to create our concrete Entity Framework implementation of this interface.

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<Product> _productRepository;
        private EFRepository<Basket> _basketRepository;
        private EFRepository<Customer> _customerRepository;

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

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

        public IRepository CustomerRepository
        {
            get
            {
                if (_customerRepository == null)
                    _customerRepository = new EFRepository<Customer>(_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);
        }
    }
}

BikeStoreUnitOfWork contains a single instance of BikeStoreContext, and passes it into the repositories when they are utilized. By sharing this instance across repositories we can include changes to multiple entity types within a single transaction.

BikeStoreUnitOfWork implements the IDisposible interface, and ensures that the context is correctly disposed of.

We can now replace the code shown at the start of the article with the following –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    Product product = unitOfWork.ProductRepository.Get(p => p.ProductId == 1).First();
    product.Name = "CAAD 10";
    unitOfWork.Save();
}

This code can be included in a controller without the need for the controller to reference the System.Data.Entity assembly. The controller is persistence ignorant. It is no longer aware that Entity Framework is being used in the persistence layer. And we could change the implementation of BikeStoreUnit of work to use nHibernate or any other technology, without the need to change any code or references in the controller.

In the next post in this series I will present the Service Layer pattern, and demonstrate how we can use this to separate business logic from request processing/presentation logic. And how we can use dependency injection to inject concrete implementations of unit of work into the service classes, and concrete implementations of the service classes into the controllers. Hence fully decoupling the controllers from the service layer, and the service layer from the persistence layer. In the third and final post I will talk about how this helps us to isolate the controllers and service layers during unit testing.