If you’re using Entity Framework Core and building any non-trivial enterprise application then using data tables with paging is almost a certainty. There are two ways to do this: server side and client side. Which one you choose depends on your design and your needs. There are pros and cons associated with both so you need to choose the correct approach for what you want to accomplish.

Server Side Paging

With server side paging all user interactions will hit the server. This includes sorting, filtering, paging, and anything else that is done that requires manipulating the dataset. The up side of this is that the initial page load will be minimal depending on the maximum amount of results per page that you allow. The down side is that you will be hitting the server a lot more due to all interactions requiring the query to be hit. To start off, we’re going to need to create a PaginatedList that is going to allow us to handle the functions required for our datatable. Lets start off with our server side PaginatedList class:

public class PaginatedList<T>
{
    public int CurrentPage { get; private set; }
    public int From { get; private set; }
    public List<T> Items { get; private set; }
    public int PageSize { get; private set; }
    public int To { get; private set; }
    public int TotalCount { get; private set; }
    public int TotalPages { get; private set; }

    public PaginatedList(List<T> items, int count, int currentPage, int pageSize)
    {
        CurrentPage = currentPage;
        TotalPages = (int)Math.Ceiling(count / (double)pageSize);
        TotalCount = count;
        PageSize = pageSize;
        From = ((currentPage - 1) * pageSize) + 1;
        To = (From + pageSize) - 1;
        Items = items;
    }

    public bool HasPreviousPage
    {
        get
        {
            return (CurrentPage > 1);
        }
    }

    public bool HasNextPage
    {
        get
        {
            return (CurrentPage < TotalPages);
        }
    }

    public static async Task<PaginatedList<T>> CreateAsync(
        IQueryable<T> source, int currentPage, int pageSize, string sortOn, string sortDirection)
    {
        var count = await source.CountAsync();

        if (!string.IsNullOrEmpty(sortOn))
        {
            if (sortDirection.ToUpper() == "ASC")
                source = source.OrderBy(sortOn);
            else
                source = source.OrderByDescending(sortOn);
        }

        source = source.Skip((currentPage - 1) * pageSize)
            .Take(pageSize);

        var items = await source.ToListAsync();

        return new PaginatedList<T>(items, count, currentPage, pageSize);
    }
}

Much of this code should be self explanatory but lets run through a few things. First, you’ll notice in CreateAsync that we’re not doing any sort of filtering in here. This needs to occur in the code that calls CreateAsync and it needs to be an IQueryable. What CreateAsync does do though is handles the paged result set based on the current page and page size. From there it takes care of the sorting based on sort on , which is the property name you want to sort on and the direction you want to sort. The HasNextPage and HasPreviousPage are used to create the page links. When there isn’t a next page, we pass back null which prevents the page number from being created and vice versa. The OrderBy and OrderByDescending are using extensions found below:

public static class IQueryableExtensions
{
    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string propertyName)
    {
        return source.OrderBy(ToLambda <T > (propertyName));
    }

    public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string propertyName)
    {
        return source.OrderByDescending(ToLambda <T > (propertyName));
    }

    private static Expression<Func<T, object>> ToLambda<T>(string propertyName)
        {
        var parameter = Expression.Parameter(typeof(T));
        var property = Expression.Property(parameter, propertyName);
        var propAsObject = Expression.Convert(property, typeof(object));

        return Expression.Lambda<Func<T, object>> (propAsObject, parameter);
    }
}

I took this extension from stack overflow https://stackoverflow.com/questions/1689199/c-sharp-code-to-order-by-a-property-using-the-property-name-as-a-string and it is the second answer on the page. I’d be hard pressed to come up with a better solution than this for ordering based on a property name. Now that we have paging, we’re going to need to add in the ability to filter our query.

Server Side Filtering

There’s two ways to go about this. You can provide a text field client side for each possible query parameter such as FirstName, LastName, etc. or you can do a single text field and do a contains. Lets do both. We’ll start with what a query would look like if we were going to do query parameters. I’m going to abbreviate the query itself due to its size. Let’s start with our query model:

public class ContactsQueryModel
{
    public List<string> States { get; set; }
    public List<int?> TerritoryIds { get; set; }
    public List<int> BookIds { get; set; }
    public List<int> CampaignIds { get; set; }
}

And now our query:

public async Task<IQueryable<Contact>> GetContactsWithCustomQuery(
    ContactsQueryModel contactsQueryModel, DbContext context, long tenantId)
{
    Expression <Func<Contact, bool>> statesExpression = m => 
        m.Locations.Any(l => contactsQueryModel.States.Contains(l.State));
    Expression <Func<Contact, bool>> territoryExpression = m => 
        contactsQueryModel.TerritoryIds.Contains(m.TerritoryId);
    Expression <Func<Contact, bool>> booksExpression = m => 
        m.BookContacts.Any(b => contactsQueryModel.BookIds.Contains(b.BookId));
    Expression <Func<Contact, bool>> campaignsExpression = m => 
        m.CampaignContacts.Any(c => contactsQueryModel.CampaignIds.Contains(c.CampaignId));

    Expression <Func<Contact, dynamic>> selectStatement = m => new
    {
        m.Id,
        m.UpdatedBy,
        m.UpdatedByName,
        m.CreatedBy,
        m.CreatedByName,
        m.DateUpdated,
        m.DateCreated,
        m.IsDeleted,
        m.ContactStatusId,
        m.PictureId,
        m.ReferralSourceId,
        m.TerritoryId,
        m.TimeZoneReferenceId,
        m.UserId
    };
    IQueryable <Contact> query;

    if (contactsQueryModel.StatusIds != null && contactsQueryModel.StatusIds?.Count != 0)
        query = context.Contact.Where(m => contactsQueryModel.StatusIds
            .Contains(m.ContactStatusId) && m.TenantId == tenantId);
    else
        query = context.Contact.Where(m => m.ContactStatusId != 0 && m.TenantId == tenantId);

    if (contactsQueryModel.States != null && contactsQueryModel.States?.Count > 0)
        query = query.Where(statesExpression);

    if (contactsQueryModel.FilterByUserId != Guid.Empty)
        query = query.Where(m => m.UserId == contactsQueryModel.FilterByUserId);

    if (contactsQueryModel.TerritoryIds != null && contactsQueryModel.TerritoryIds?.Count != 0)
        query = query.Where(territoryExpression);

    if (contactsQueryModel.BookIds != null && contactsQueryModel.BookIds?.Count > 0)
        query = query.Where(booksExpression);

    if (contactsQueryModel.CampaignIds != null && contactsQueryModel.CampaignIds?.Count > 0)
        query = query.Where(campaignsExpression);

    if (!string.IsNullOrEmpty(contactsQueryModel.Company))
        query = query.Where(companyExpression);

    return query.Select(selectStatement);
}

I removed a lot from this query since it was over five times the size of the code above, but the basic idea is that you pass in your parameters and create expressions. From there you append the expressions to construct the query and then return the IQueryable and then pass it to the PaginatedList’s CreateAsync.

If you do a simple single text field filter then it’s much easier. In the code before CreateAsync do the same thing as above by creating an expression, then split the text by space, and then construct your Where with LINQ like below:

var queryTerms = queryString.Split(' ');
query = query.Where(m => queryTerms.Contains(m.FirstName) 
    || queryTerms.Contains(m.LastName));

A query built with expressions for each query parameter is typically going to be the better route to go, but if simplicity is your goal then a simple query string will suffice.

Client Side Paging

Client side paging, like server side paging, has pros and cons. If you do client side paging you are going to be returning a larger dataset which will typically result in a larger initial page load. Also, it can be more inefficient since often many of the records will not be accessed or used resulting in unneeded usage of bandwidth. That being said, client side paging results in a much smoother experience giving your application a desktop feel. You can roll your own client side filtering, sorting, and paging with Javascript, but most libraries these days are going to have that functionality out of box. Typically you want to use client side paging when you expect your users to spend a lot of time with a single data set and when the amount of features and functionality associated with each record is significant. Your overall design of your system will also determine your choice.

As an example, I wrote a CRM. I decided to create two modes, a table mode which was the thirty thousand foot view of the data and allowed batch actions as well as visibility at a higher level of the data. The second mode is a detail mode that allows you to click on a record anywhere in the table to have more granular control over the contact. It takes roughly two seconds to load 1500 partially hydrated contact records. Given the amount of records and how long a user could spend working with that data set, two seconds isn’t bad. Below is a picture of the table:

When a user clicks into detail mode I go and grab the rest of the record which takes roughly 50ms. In other words, we have a two second hit but then we have near instant requests after that for all user interaction. Here’s what detail mode looks like:

When in this mode you can hit next and previous as well as jump where ever you want within the data set of contacts. You can also click back to table mode, click on a new page, then click on a new contact without incurring the query again. In addition you can also work on a contact, move to another contact, and then come back and click undo. This kind of functionality is not possible with server based paging, sorting, and filtering.

In the end, pick which one works best for you based on your design and your performance concerns.