Pagination using Entity Framework through Web API

asp.net-mvc-3 asp.net-mvc-4 c# c#-4.0 entity-framework-6

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 ?

1
2
9/30/2015 11:34:12 AM

Accepted Answer

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; }
}
0
9/30/2015 11:58:53 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow