DotNetCore Entity Framework | Generic Pagination

Question

I was looking for a way to do generic pagination with Entity Framework in Dotnet Core 1.1.

I found this guide on MSDN: https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application

But this was not generic and did not allow me to reuse code.

Included is the answer I used if anyone is looking into this, I thought it would be nice to share.

It uses custom Attributes on models, and returns a pagination model.

L.querter · Answer 1 · 2017-08-15T13:33:12.093

EDIT:

The answer below is not correct due to the orderBy not translating into L2E correctly. All the records will be retrieved and sorted in memory what results into poor performance. Check comments for more information and posisble solution.

ORIGNAL:

My solution:

Model.cs:

public class User
{

    // Sorting is not allowed on Id
    public string Id { get; set; }

    [Sortable(OrderBy = "FirstName")]
    public string FirstName { get; set; }

}

SortableAttribute.cs:

public class SortableAttribute : Attribute
{
    public string OrderBy { get; set; }
}

PaginationService.cs:

public static class PaginationService
{

    public static async Task<Pagination<T>> GetPagination<T>(IQueryable<T> query, int page, string orderBy, bool orderByDesc, int pageSize) where T : class
    {
        Pagination<T> pagination = new Pagination<T>
        {
            TotalItems = query.Count(),
            PageSize = pageSize,
            CurrentPage = page,
            OrderBy = orderBy,
            OrderByDesc = orderByDesc
        };

        int skip = (page - 1) * pageSize;
        var props = typeof(T).GetProperties();
        var orderByProperty = props.FirstOrDefault(n => n.GetCustomAttribute<SortableAttribute>()?.OrderBy == orderBy);


         if (orderByProperty == null)
        {
            throw new Exception($"Field: '{orderBy}' is not sortable");
        }

        if (orderByDesc)
        {
            pagination.Result = await query
                .OrderByDescending(x => orderByProperty.GetValue(x))
                .Skip(skip)
                .Take(pageSize)
                .ToListAsync();

            return pagination;
        }
        pagination.Result = await query
            .OrderBy(x => orderByProperty.GetValue(x))
            .Skip(skip)
            .Take(pageSize)
            .ToListAsync();

        return pagination;
    }
}

Pagination.cs (model):

public class Pagination<T>
{
    public int CurrentPage { get; set; }

    public int PageSize { get; set; }

    public int TotalPages { get; set; }

    public int TotalItems { get; set; }

    public string OrderBy { get; set; }

    public bool OrderByDesc { get; set; }

    public List<T> Result { get; set; }
}

UserController.cs (inside controller), context is EntityFramework context:

 [HttpGet]
    public async Task<IActionResult> GetUsers([FromQuery] string orderBy, [FromQuery] bool orderByDesc, [FromQuery] int page, [FromQuery] int size)
    {
        var query = _context.User.AsQueryable();
        try
        {
            var list = await PaginationService.GetPagination(query, page, orderBy, orderByDesc, size);
            return new JsonResult(list);
        }
        catch (Exception e)
        {
            return new BadRequestObjectResult(e.Message);
        }
    }

I hope this helps someone in the future !

Your OrderBy needs to be *before* the skip/take. Also, there's no need to use an attribute, that's just added fluff in your model. — DavidG, Aug 15 '17 at 11:17
@DavidG, the attribute is a restriction, i dont want a user to sort on (example) Id, how can I restrict it then? And this is working in my setup to work with EF. I can use this pagination system on (eg) different Contexts (like users, companies, ....) by only chaging the initial query. — L.querter, Aug 15 '17 at 11:56
Why would you restrict them? That's not a useful thing. If this code works in your environment then your skip and take are running off a the entire data set in memory. EF will not understand what to do with the reflection part of your code. Unless .Net Core introduced something funky,but I doubt it. — DavidG, Aug 15 '17 at 11:59
You could be right about the orderBy before the Skip/Take, i will change it. But this code works, no doubt about it. — L.querter, Aug 15 '17 at 12:02
I'm pretty sure constructs like `.OrderBy(x => orderByProperty.GetValue(x))` does **not** work in L2E query. At least in EF6. In EF Core it might work, but will use client evaluation (in memory), hence will have terrible performance. — Ivan Stoev, Aug 15 '17 at 12:08
@IvanStoev I've just tested this to be sure, and as we both suspected, EF Core doesn't magically figure out the reflection code and sorts everything in memory. — DavidG, Aug 15 '17 at 12:45
@IvanStoev, you are right, the query executed does not have the order by statement so everything is handled in memory, what will result in poor performance. Do you by any chance have a better solution to this problem? i don't want to write it like they do in de msdn tutorial. — L.querter, Aug 15 '17 at 12:47
Instead of passing in `string orderBy`, just pass in the expression directly (`Expression> orderBy`) and do `OrderBy(orderBy)` — DavidG, Aug 15 '17 at 12:55
@L.querter Sure I do :) For instance, take a look at my answer to [How to use a string to create a EF order by expression?](https://stackoverflow.com/questions/39908403/how-to-use-a-string-to-create-a-ef-order-by-expression/39916384#39916384) or similar SO posts to get an idea. — Ivan Stoev, Aug 15 '17 at 13:10

DotNetCore Entity Framework | Generic Pagination

1 Answers1