none
How to incorporate pagination feature into my repository RRS feed

  • Question

  • see the sample code

    This is books model
        -----------------------
        public class Book
        {
            [Key]
            public int Id { get; set; }
            [Required]
            [MaxLength(30)]
            public string Title { get; set; }
            public string Authers { get; set; }
     
            [Column("Year")]
            [Display(Name = "Publish Year")]
            public string publishYear { get; set; }
     
            [Column("Price")]
            [Display(Name = "Price")]
            public decimal BasePrice { get; set; }
        }
        
        Repository interface
        --------------------
        public interface IBookRepository : IDisposable
            {
                IEnumerable<Book> GetBooks();
                Book GetBookByID(int bookId);
                void InsertBook(Book book);
                void DeleteBook(int bookID);
                void UpdateBook(Book book);
                void Save();
        }
    
    
    Book repository
    --------------------
    public class BookRepository : IBookRepository
        {
            private BookContext _context;
     
            public BookRepository(BookContext bookContext)
            {
                this._context = bookContext;
            }
     
            public IEnumerable<book> GetBooks()
            {
                return _context.Books.ToList();
            }
     
            public Book GetBookByID(int id)
            {
                return _context.Books.Find(id);
            }
     
            public void InsertBook(Book book)
            {
                _context.Books.Add(book);
            }
     
            public void DeleteBook(int bookID)
            {
                Book book = _context.Books.Find(bookID);
                _context.Books.Remove(book);
            }
     
            public void UpdateBook(Book book)
            {
                _context.Entry(book).State = EntityState.Modified;
            }
     
            public void Save()
            {
                _context.SaveChanges();
            }
     
            private bool disposed = false;
     
            protected virtual void Dispose(bool disposing)
            {
                if (!this.disposed)
                {
                    if (disposing)
                    {
                        _context.Dispose();
                    }
                }
                this.disposed = true;
            }
     
            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }
        } 
    

    code taken from https://www.codeproject.com/Articles/644605/CRUD-Operations-Using-the-Repository-Pattern-in-MV

    now tell me when i will fetch books then how could i do the pagination...say want to retrieve 20 books or

    i like to mention author name and 20 records when fetching book.

    please help me with sample code. thanks

    Wednesday, December 13, 2017 2:12 PM

Answers

  • Hi Sudip_inn,

    >>now tell me when i will fetch books then how could i do the pagination...say want to retrieve 20 books or i like to mention author name and 20 records when fetching book

    1.  Defined class called PagedResult<T>. PagedResult<T> defines all important properties we need when dealing with paged results. Code is here.

    public class PagedResult<T>
    {
            public IList<T> Results { get; set; }
            public int CurrentPage { get; set; }
            public int PageCount { get; set; }
            public int PageSize { get; set; }
            public int RowCount { get; set; }
    }

    2. Modify your interface like this:

    public interface IBookRepository : IDisposable
        {
            IEnumerable<Book> GetBooks();
            Book GetBookByID(int bookId);
            void InsertBook(Book book);
            void DeleteBook(int bookID);
            void UpdateBook(Book book);
            void Save();
    
    //add this line
            PagedResult<Book> ListPageBooks(string author, int page, int pageSize);
        }


    3.  Modify your repository class like this:

    public class BookRepository : IBookRepository
        {
            private BookContext _context;
     
            public BookRepository(BookContext bookContext)
            {
                this._context = bookContext;
            }
     
            public IEnumerable<book> GetBooks()
            {
                return _context.Books.ToList();
            }
     
            public Book GetBookByID(int id)
            {
                return _context.Books.Find(id);
            }
     
            public void InsertBook(Book book)
            {
                _context.Books.Add(book);
            }
     
            public void DeleteBook(int bookID)
            {
                Book book = _context.Books.Find(bookID);
                _context.Books.Remove(book);
            }
     
            public void UpdateBook(Book book)
            {
                _context.Entry(book).State = EntityState.Modified;
            }
     
            public void Save()
            {
                _context.SaveChanges();
            }
    
    public PagedResult<Book> ListPageBooks(string author, int page, int pageSize)
            {
                IQueryable<Book> results = from o in _context.Books
                              orderby o.Id
                              select o;
                if (!string.IsNullOrEmpty(author))
                {
                    results = results.Where(t => t.Authers.Contains(author));
                }
                var result = GetPagedResultForQuery(results, page, pageSize);
                return result;
            }
    
            private static PagedResult<Book> GetPagedResultForQuery(
                IQueryable<Book> query, int page, int pageSize)
            {
                var result = new PagedResult<Book>();
                result.CurrentPage = page;
                result.PageSize = pageSize;
                result.RowCount = query.Count();
                var pageCount = (double)result.RowCount / pageSize;
                result.PageCount = (int)Math.Ceiling(pageCount);
                var skip = (page - 1) * pageSize;
                result.Results = query.Skip(skip).Take(pageSize).ToList();
    
                return result;
            }
     
            private bool disposed = false;
     
            protected virtual void Dispose(bool disposing)
            {
                if (!this.disposed)
                {
                    if (disposing)
                    {
                        _context.Dispose();
                    }
                }
                this.disposed = true;
            }
     
            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }
        } 

    4. Usage

    private BookContext _context = new BookContext();
            public ActionResult Index()
            {
                BookRepository bRes = new BookRepository(_context);
                var pagebook = bRes.ListPageBooks("authorname",1, 20);
                return View();
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Thursday, December 14, 2017 6:19 PM
    Thursday, December 14, 2017 2:00 AM
    Moderator
  • Hi Sudip_inn,

    >>guide me please should i use the above code in production? would be right choice.

    I think you could use it. you could also refer the follow document, which use repository pattern and unit of work.

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, December 15, 2017 1:24 PM
    Friday, December 15, 2017 5:10 AM
    Moderator
  • Hi Sudip_inn,

    If you want to use extension method named ToPagedList, you need to install PagedList.Mvc package via Nuget. For more information, please refer to(please check the phase "Add Paging Functionality to the Index Method"):

    https://docs.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

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Thursday, December 21, 2017 10:04 AM
    Monday, December 18, 2017 9:30 AM
    Moderator

All replies

  • http://gunnarpeipman.com/2017/01/ef-core-paging/

    Paging now matter what the underlying ORM or database technology is about is based on an algorithm.

     

    Wednesday, December 13, 2017 7:50 PM
  • Hi Sudip_inn,

    >>now tell me when i will fetch books then how could i do the pagination...say want to retrieve 20 books or i like to mention author name and 20 records when fetching book

    1.  Defined class called PagedResult<T>. PagedResult<T> defines all important properties we need when dealing with paged results. Code is here.

    public class PagedResult<T>
    {
            public IList<T> Results { get; set; }
            public int CurrentPage { get; set; }
            public int PageCount { get; set; }
            public int PageSize { get; set; }
            public int RowCount { get; set; }
    }

    2. Modify your interface like this:

    public interface IBookRepository : IDisposable
        {
            IEnumerable<Book> GetBooks();
            Book GetBookByID(int bookId);
            void InsertBook(Book book);
            void DeleteBook(int bookID);
            void UpdateBook(Book book);
            void Save();
    
    //add this line
            PagedResult<Book> ListPageBooks(string author, int page, int pageSize);
        }


    3.  Modify your repository class like this:

    public class BookRepository : IBookRepository
        {
            private BookContext _context;
     
            public BookRepository(BookContext bookContext)
            {
                this._context = bookContext;
            }
     
            public IEnumerable<book> GetBooks()
            {
                return _context.Books.ToList();
            }
     
            public Book GetBookByID(int id)
            {
                return _context.Books.Find(id);
            }
     
            public void InsertBook(Book book)
            {
                _context.Books.Add(book);
            }
     
            public void DeleteBook(int bookID)
            {
                Book book = _context.Books.Find(bookID);
                _context.Books.Remove(book);
            }
     
            public void UpdateBook(Book book)
            {
                _context.Entry(book).State = EntityState.Modified;
            }
     
            public void Save()
            {
                _context.SaveChanges();
            }
    
    public PagedResult<Book> ListPageBooks(string author, int page, int pageSize)
            {
                IQueryable<Book> results = from o in _context.Books
                              orderby o.Id
                              select o;
                if (!string.IsNullOrEmpty(author))
                {
                    results = results.Where(t => t.Authers.Contains(author));
                }
                var result = GetPagedResultForQuery(results, page, pageSize);
                return result;
            }
    
            private static PagedResult<Book> GetPagedResultForQuery(
                IQueryable<Book> query, int page, int pageSize)
            {
                var result = new PagedResult<Book>();
                result.CurrentPage = page;
                result.PageSize = pageSize;
                result.RowCount = query.Count();
                var pageCount = (double)result.RowCount / pageSize;
                result.PageCount = (int)Math.Ceiling(pageCount);
                var skip = (page - 1) * pageSize;
                result.Results = query.Skip(skip).Take(pageSize).ToList();
    
                return result;
            }
     
            private bool disposed = false;
     
            protected virtual void Dispose(bool disposing)
            {
                if (!this.disposed)
                {
                    if (disposing)
                    {
                        _context.Dispose();
                    }
                }
                this.disposed = true;
            }
     
            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }
        } 

    4. Usage

    private BookContext _context = new BookContext();
            public ActionResult Index()
            {
                BookRepository bRes = new BookRepository(_context);
                var pagebook = bRes.ListPageBooks("authorname",1, 20);
                return View();
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Thursday, December 14, 2017 6:19 PM
    Thursday, December 14, 2017 2:00 AM
    Moderator
  • guide me please should i use the above code in production? would be right choice.

    tell me if there any area which i need to refactor then highlight that area. 

    Thursday, December 14, 2017 6:20 PM
  • here you design ListPageBooks function which take author name. suppose can't we remove filters from this function ?

    can we develop the pagination part as extension. if possible then we can write code like

    IQueryable<Book> results = from o in _context.Books
                              orderby o.Id
                              select o;
                if (!string.IsNullOrEmpty(author))
                {
                    results = results.Where(t => t.Authers.Contains(author));
                }
                var result = results.GetPagedResultForQuery(page, pageSize);
                return result;

    public PagedResult<Book> ListPageBooks(string author, int page, int pageSize)
            {
                IQueryable<Book> results = from o in _context.Books
                              orderby o.Id
                              select o;
                if (!string.IsNullOrEmpty(author))
                {
                    results = results.Where(t => t.Authers.Contains(author));
                }
                var result = GetPagedResultForQuery(results, page, pageSize);
                return result;
            }

    looking for your code. how to refactor the pagination as extension method which i can use for customer, product, author etch.

    Thursday, December 14, 2017 6:24 PM
  • Hi Sudip_inn,

    >>here you design ListPageBooks function which take author name. suppose can't we remove filters from this function ?

    You could modify the input argument according to your requirement.

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 15, 2017 5:07 AM
    Moderator
  • Hi Sudip_inn,

    >>guide me please should i use the above code in production? would be right choice.

    I think you could use it. you could also refer the follow document, which use repository pattern and unit of work.

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, December 15, 2017 1:24 PM
    Friday, December 15, 2017 5:10 AM
    Moderator
  • Where they used pagination functionality in code

    i saw their code. they ToPagedList which is 3rd part component which generate pagination link.

    i am not after pagination link rather i need 20 or 50 data.

    int pageSize = 3; int pageNumber = (page ?? 1); return View(students.ToPagedList(pageNumber, pageSize));

    after checking the link you shared i found no code is there for

    fetching data from db with a range. so please looking for your further guidance. thanks

    Friday, December 15, 2017 1:28 PM
  • Hi Sudip_inn,

    If you want to use extension method named ToPagedList, you need to install PagedList.Mvc package via Nuget. For more information, please refer to(please check the phase "Add Paging Functionality to the Index Method"):

    https://docs.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

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Thursday, December 21, 2017 10:04 AM
    Monday, December 18, 2017 9:30 AM
    Moderator
  • what unit of work pattern confirms or does?

    why it should matter when we design repository based on EF.

    thanks

    Thursday, December 21, 2017 10:05 AM