Pagination using Entity Framework through Web API

Question

I have a class like this:

public class BE_CategoryBase
{
    public Int32 CategoryID { get; set; }
    public String CategoryName
    {
        get;
        set;
    }
    public String CategorySanitized { get; set; }
    public Boolean IsActive { get; set; }
    public DateTime? ModificationDate { get; protected set; }
    public Int64? ModifiedBy { get; set; }
}

In the Web API I have Action Method like this.

So far I am fetching all records from the server in single request. The above API call is Post because there are many filters being used. As the Get has limited length in query string...so I used Post.

[Route("api/v1/CategoryList"), HttpPost]
public async Task<IHttpActionResult> CategoryList([FromBody]BE_Category obj)
{
    var result = await _category.CategoryList(obj);

    if (!string.IsNullOrEmpty(result.Key))
        await _log.CreateLog(new BE_Logs { Message = result.Key });

    return Ok(new { ErrorMessage = result.Key, result = result.Value });
}

Now, In order to use Paging, I was thinking of adding startPage and PageSize columns in the BE_CategoryBase class. As I am using Entity Framework Code First, adding the column in the class will add these columns in Database Table also.

Is there any elegant way to implement paging with Post Action Method ?

Harsh · Accepted Answer · 2017-04-27 11:58:28Z

A clean way to handle the situation is to encapsulate all the result set and the related metadata (page size, current page number, etc.) in one class. EntityFrameworkPaginate is a nuget package which provides an elegant solution to pagination. So, the way it works is you set up your dynamic filtering and sorting and you will get a Page object as result which will have the result along with the metadata. (CurrentPage, PageCount, PageSize, RecordCount and Results).

Pagination will probably come with dynamic filtering and sorting as well. A clean way to handle the situation is to encapsulate all the result set and the related metadata (page size, current page number, etc.) in one class. EntityFrameworkPaginate is a nuget package which provides an elegant solution to pagination. So, the way it works is you set up your dynamic filtering and sorting and you will get a Page object as result which will have the result along with the metadata. (CurrentPage, PageCount, PageSize, RecordCount and Results).

If you have dynamic filters based on separate conditions, then you can create a Filters object. Add the filter expression and the condition the the filter object.The first parameter in your filter.Add is the search condition and the second one is search expression.

 var filters = new Filters<BE_CategoryBase>(); 
 filters.Add(!string.IsNullOrEmpty(searchText), x => x.CategoryName.Contains(searchText));
 filters.Add(!string.IsNullOrEmpty(categoryText), x => x.CategorySanitized.Equals(categoryText));

Similarly set up your dynamic sorting in the Sorts object.

 var sorts = new Sorts<BE_CategoryBase>();
 sorts.Add(sortBy == 1, x => x.CategoryID );
 sorts.Add(sortBy == 2, x => x.ModificationDate );

Now, call the Paginate extension method on your db set to get the paginated results.

 Page<BE_CategoryBase> paginatedResult = context.BE_CategoryBase.Paginate(currentPage, 
                                  pageSize, sorts, filters);

This approach reduces the code you have to write without having any implications on the performance. A detailed example is here in the link.

Pankaj · Accepted Answer · 2015-09-30 11:58:53Z

0

NotMapped is what I was looking for. I hope this will surely help somebody.

public class Paging
{
    [NotMapped]
    public Int16 StartPage { get; set; }

    [NotMapped]
    public Int16 PageSize { get; set; }
}


public class BE_CategoryBase : Paging
{
    public Int32 CategoryID { get; set; }
    public String CategoryName { get; set; }
    public String CategorySanitized { get; set; }
    public Boolean IsActive { get; set; }
    public DateTime? ModificationDate { get; protected set; }
    public Int64? ModifiedBy { get; set; }
}

edited Sep 30, 2015 at 11:58

answered Sep 30, 2015 at 11:45

Pankaj

10.2k40 gold badges152 silver badges298 bronze badges

6 Comments

Steve Greene Over a year ago

I would look at the ViewModel pattern. Paging is a concern of the view, not the entity (as well as sorting, filtering, etc). stevemichelotti.com/aspnet-mvc-view-model-patterns

Pankaj Over a year ago

I have 7 projects in my solution. Let me share my N-Tier architecture and you will find that every thing has their own project: codereview.stackexchange.com/questions/103879/…

Steve Greene Over a year ago

Regarding n-tier, personally I have moved way from it. I have a project for my entities, one for entity framework, maybe a service layer and then the UI. javacodegeeks.com/2014/09/… As to my comment above, the viewmodel pattern is a different issue. Use whatever layers you need to construct a viewmodel, pass that to your view and then process the results. Your entity model shouldn't have fields in it that are only concerns of the view (paging, sorting, etc).

Pankaj Over a year ago

In my case, View Model will not be helpful because the request goes till 7th project starting from 1st.

Steve Greene Over a year ago

We're not on the same page. The purpose of an n-tier architecture is to not have to deal with the bottom tiers from the UI. Simply put, you might have a GET action on your MVC controller where you go get your stuff from the database using your top tier. Then add other stuff the view cares about (like paging). Then on your POST you validate and persist using your top tier.

|

Collectives™ on Stack Overflow

Pagination using Entity Framework through Web API

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related