Pagination with Entity Framework

• Download demo - 760.4 KB

Introduction

Suppose we have several millions of records in a table, we definitely don't want to load all of them into memory. We break them into pieces (or pages), and load each of them on-demand.

Before going into details, you might want to have a look at SQLite CRUD Operation Using Entity Framework 7, because we're going to use the Chinook database sample again.

The DbContext and models are the same. For readability, the Track model is shown below:

... other entities such as Artist, Album, etc.
public class Track
{
    public int TrackId { get; set; }
    public string Name { get; set; }
    public double UnitPrice { get; set; } = 0.99;
    public string Composer { get; set; } = "U/A";
    public int Milliseconds { get; set; } = 5000;
    public int Bytes { get; set; } = 10000;

    public int AlbumId { get; set; }
    public Album Album { get; set; }

    public int GenreId { get; set; }
    public Genre Genre { get; set; }

    public int MediaTypeId { get; set; }
    public MediaType MediaType { get; set; }

    public virtual ICollection<playlisttrack> PlaylistTracks { get; set; }
        = new HashSet<playlisttrack>();
}
...

We're going to paginate a collection of tracks based on some filters (can be price, album, artist, etc.) or no filter at all (all tracks, currently total 3503):

Approach

What naturally comes to mind is to encapsulate some related properties into a pagination object. First comes the total items to be paginated (check), then number of items per page, then starting page (which is 1, check), and finally, the ending page (which is also the number of pages needed, which is calculated based on the first 2 properties).

The number of items per page is from the user input, and the total number of items is from the database. Those are two items to construct our pagination object:

public class MyEFPagination
{
    public int TotalItems { get; private set; } // Equal to PageSize * MaxPage
    public int PageSize { get; private set; } // Number of items per page
    public int MinPage
    { get; private set; } = 1; // Page index starting from MinPage to MaxPage
    public int MaxPage { get; private set; } //Total pages

    public MyEFPagination(int totalItems, int itemsPerPage)
    {
        if (itemsPerPage < MinPage)
        {
            throw new ArgumentOutOfRangeException
            (null, $"*** Number of items per page must > 0! ***");
        }

        TotalItems = totalItems;
        PageSize = itemsPerPage;
        MaxPage = CalculateTotalPages(totalItems, itemsPerPage);
    }

    private int CalculateTotalPages(int totalItems, int itemsPerPage)
    {
        int totalPages = totalItems / itemsPerPage;

        if (totalItems % itemsPerPage != 0)
        {
            totalPages++; // Last page with only 1 item left
        }

        return totalPages;
    }
}

From the above class, obviously, the number of items should be greater than zero, or an exception will be thrown.
That's all for the pagination object to be concerned about.

Now, we want to query for a specific piece of data (or data that is corresponding to a specific page index) the same way as we usually query data. So, an extension method that returns IQueryable<t></t> is most suitable.

public static class MyEFPaginationExtensions
{
    public static IQueryable<t> PagedIndex<t>(this IQueryable<t> query,
        MyEFPagination pagination, int pageIndex) //where T : Entity
    {
        if (pageIndex < pagination.MinPage || pageIndex > pagination.MaxPage)
        {
            throw new ArgumentOutOfRangeException(null,
            $"*** Page index must >= {pagination.MinPage}
            and =< {pagination.MaxPage}! ***");
        }

        // Return IQueryable<t> to enable chained-methods calls
        return query
            //.OrderBy(T => T.EntityProperty) [NOT this extension responsibility]
            .Skip(GetSkip(pageIndex, pagination.PageSize))
            .Take(pagination.PageSize);
    }

    private static int GetSkip(int pageIndex, int take)
    {
        return (pageIndex - 1) * take;
    }
}

Take a close look at PagedIndex<t></t> extension method above:

• It takes in the pagination object parameter to get the knowledge about the collection to be paginated (4 properties above)
• A specific page index parameter: again, this number should be valid or an exception will be thrown.
• We could have made T: Track, then sort based on T.TrackId (or any other properties) before dividing the collection into pages. But it better lets the caller decide.

Example

We're going to paginate high-priced tracks ($1.99 and above), and order tracks in each page by their names.

private static void TestPagination()
{
    try
    {
        using (var context = new MyDbContext())
        {
            Console.Write("Specify number of items per page you like: ");
            int itemsPerPage = Convert.ToInt32(Console.ReadLine());

            var highPriceTracksQuery = context.Tracks
                .Where(t => t.UnitPrice >= 1.99)
                .OrderBy(t => t.TrackId);

            MyEFPagination pagination =
            new MyEFPagination(highPriceTracksQuery.Count(), itemsPerPage);

            Console.WriteLine("Pagination info");
            Console.WriteLine($"\t Total items: {pagination.TotalItems}");
            Console.WriteLine($"\t Total
            {pagination.MaxPage} pages needed for the above items");

            Console.Write($"Which page (must be >= {pagination.MinPage}
            and =< {pagination.MaxPage}) would you like to retrieve? ");
            int pageIndex = Convert.ToInt32(Console.ReadLine());

            var result = highPriceTracksQuery
                .PagedIndex(pagination, pageIndex)
                .OrderBy(t => t.Name)
                .ToList();

            foreach (var item in result)
            {
                Console.WriteLine($"\t {item.Name}");
            }

            //Console.WriteLine(pageIndex);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"\n\tException:\n\t{ex.Message}\n");
    }
}

Execution results: