Tag: Paging

MVC Index Partial View for Paging

This entry is part 7 of 7 in the series Ultimate MVC Paging

This post was revised and updated on 10/24/2020.

When paging, you usually want to have a way to allow your users to search and filter the data. When they click on search, or hit enter, or click on a column heding, you want to retain that search criteria after the page displays the results. The easiest way to do that is to keep the results in a partial view that is refreshed separately from the primary view. This way, you don’t have to fight the battle of keeping track of it.

One issue I’ve run into is that the user may search for a keyword, then they modify the keyword, think better of it, and just click a sort column. Your page posts back and the search is now for the modified keyword, which wasn’t their intention. Using the partial view method described here WILL NOT have this unwanted functionality.

When writing a partial view for the search results, I’ve added in the ability to utilize my business objects for paging, shown on post: Business Object for Paging, so that paging is implemented generically. I then use the AJAX JQuery call to refresh of the page on an column click for sorting.

Like the main view, this partial view is specifically written for the LuCategory model, there is no need to reference the model as an interface. You can specify the exact model implementation you’re expecting on the page.

Don’t make the mistake of adding the Script reference to
“jquery.unobtrusive-ajax” to this partial view as well, as that will cause some wonky results.

@using YTG.MVC.Lookups.Models
@using YTG.Models
@model SearchModel<LuCategory>

<script type="text/javascript">

    function GetCategories(sortColumn) {

        // Set the global values for sorting post back
        var searchModel = {};
        searchModel.SortColumn = sortColumn;
        searchModel.PrevSortColumn = '@Model.SortColumn';
        searchModel.CurrentPage = @Model.CurrentPage;
        searchModel.PageSize = @Model.PageSize;
        searchModel.SearchTerm = '@Model.SearchTerm';
        searchModel.SortDescending = '@Model.SortDescending';
        searchModel.ActiveOnly = '@Model.ActiveOnly';

        $.ajax({
            type: "POST",
            url: "/Lookups/CatDisplay",
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify(searchModel),
            dataType: "html",
            async: true,
            success: function (result, status, xhr) {
                $("#gridPartial").html(result)
            },
            error: function (xhr, status, error) {
                alert("Result: " + status + " " + error + " " + xhr.status + " " + xhr.statusText)
            }
        });

    }

</script>

<div id="CatDisplay">
    <section id="main-content" class="animated fadeInUp">

        <div class="text-center">
            <!-- Pager -->
            @if (Model.EndPage > 1)
            {
                await Html.RenderPartialAsync("_PagerPartial", Model);
            }
        </div>

        <div class="panel-default">
            <table width="100%" class="table table-bordered table-hover">
                <thead>
                    <tr>
                        <th>Edit</th>
                        <th onclick="GetCategories('Name')" title="Click to Sort">Name</th>
                        <th onclick="GetCategories('ShortName')" title="Click to Sort">Short Name</th>
                        <th onclick="GetCategories('Description')" title="Click to Sort">Description</th>
                        <th class="text-center" title="Quantity of Items">Item Count</th>
                        <th class="text-center">@Html.DisplayName("Active")</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model.SortedResults)
                    {
                    <tr>
                        <td class="text-center">
                            @Html.ActionLink(" ", "Edit", "Lookups", new { id = item.Id.ToString() }, new { @class = "fas fa-edit", @title = "Id: " + item.Id })
                        </td>
                        <td>@Html.DisplayFor(modelItem => item.Name)</td>
                        <td>@Html.DisplayFor(modelItem => item.ShortName)</td>
                        <td>@Html.DisplayFor(modelItem => item.Description)</td>
                        <td class="text-center">@item.Items.Count().ToString()</td>
                        <td class="text-center">@Html.DisplayFor(modelItem => item.IsActive)</td>
                    </tr>
                    }
                </tbody>
            </table>

            @if (Model.RecordCount == 0)
            {
                <div class="col-md-12">
                    <p class="text-center text-warning">
                        There are no Categories defined
                    </p>
                    <br /><br />
                </div>
            }

            <div class="text-center">
                <!-- Pager -->
                @if (Model.EndPage > 1)
                {
                    await Html.RenderPartialAsync("_PagerPartial", Model);
                    await Html.RenderPartialAsync("_PageCountPartial", Model);
                }
            </div>

        </div>
    </section>
</div>

I chose to not display the paging partial views when there is only one page of data using “@if (Model.EndPage > 1)”. You may prefer for them to remain, or at the very least, display the total count of records somewhere if paging is not enabled.

As I’m sure you now probably surmised, this post series took about 20 hours of effort. Please let me know if you’ve found it useful, or if you see any areas where improvements can be made (be nice). Thanks, and happy coding.

MVC Paging Index View

This entry is part 5 of 7 in the series Ultimate MVC Paging

This post was revised and updated on 10/24/2020.

Below is a sample of an Index View utilizing the Generic paging partial views and another partial view that displays my data in a grid.

If you’re falling onto this page from a search link, then you may want to start at the beginning: Ultimate Guide to MVC Paging.

It contains a search box and an “Active Only” check box, if you have a requirement to knock out inactive records. Make special note of the required “jquery.unobtrusive-ajax”.

Since this view is specifically written for the LUCategory, there is no need to reference the model as an interface. You can specify the exact model implementation you’re expecting on the page.

The next important note is that you must store any values from the search model that you’re not displaying on the page in Hidden fields, or else they won’t be passed back on a click, say if the user clicks “Clear”.

@using YTG.MVC.Lookups.Models
@using YTG.Models
@model SearchModel<LuCategory>

@{
    ViewData["Title"] = "Index";
}

<h1>Lookups Category Index</h1>

<section id="main-content" class="animated fadeInUp">
    <div class="row">
        <div class="w-100">
            @using (Html.BeginForm("Index", "Lookups", FormMethod.Get, new { id = "frmCategorySearch" }))
            {
                @Html.HiddenFor(m => m.SortColumn, new { id = "SortColumn" })
                @Html.HiddenFor(m => m.SortDescending, new { id = "SortDescending" })
                @Html.HiddenFor(m => m.PageSize, new { id = "PageSize" })
                @Html.HiddenFor(m => m.CurrentPage, new { id = "CurrentPage" })

                <table class="table-responsive" cellspacing="0" width="100%" style="border-color: red !important;">
                    <tr>
                        <td>
                            @Html.TextBoxFor(m => m.SearchTerm, new
                               {
                                   id = "searchTerm",
                                   placeholder = "Category Name",
                                   CurrentPage = Model.CurrentPage,
                                   PageSize = Model.PageSize,
                                   SortExpression = Model.SortColumn,
                                   SortOrder = Model.SortDescending
                               })
                            <input type="submit" value="Search" class="btn btn-sm btn-primary" name="submitButton" />
                            <input type="submit" value="Clear" class="btn btn-sm btn-primary" name="submitButton" />
                            <a asp-action="Edit" asp-controller="Lookups" asp-route-id="-1" class="btn btn-sm btn-primary">Add New Category</a>
                            <div>
                                @Html.CheckBoxFor(m => m.ActiveOnly, new { id = "ActiveOnly", name = "ActiveOnly" })
                                @Html.LabelFor(m => m.ActiveOnly)
                            </div>
                        </td>
                    </tr>
                </table>

                <div id="gridPartial" style=" border-color: blue !important;">
                    @{ await Html.RenderPartialAsync("_CatsDisplay", Model); }
                </div>

            }
        </div>
    </div>
</section>

This view implements searching, filtering by active, paging, and length of page selection, yet it’s only 51 lines of code!

Before we can finish up, I want to explain the Paging and PageCount partial views.

Heart of MVC Paging

This entry is part 4 of 7 in the series Ultimate MVC Paging

This post was revised and updated on 10/24/2020.

If you fell on this post first, you’ll notice that the implementation of paging is a little more involved than the average example on the web. That’s because most of the other examples are either, limited, or outright “The wrong way to do it”. To start from the begining, click here: The Ultimate Guide to MVC Paging!.

Once you have a PagedResult object, or any object with the collection and total count of records that match the filters, back from your web service, you’ll need to convert it to a SearchModel that can be used throughout your MVC views and partial views to enable the paging.

In the implementation demo, we’re going to call a function to return the SearchModel, both from the original call to the Index controller, and then later from a Ajax when the user clicks on a page number or sort column. So I create a function that both controllers can share:

/// <summary>
/// Category search with paged results
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="searchTerm"></param>
/// <param name="sortFilter"></param>
/// <param name="sortColumn"></param>
/// <param name="sortDescending"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<SearchModel<LuCategory>> GetCategoriesPagedAsync(int page = 1, 
            int pageSize = 25, 
            string searchTerm = "",
            string searchFilter = "", 
            string sortColumn = "ShortName",
            bool sortDescending = false, 
            bool ActiveOnly = true)
{
    SearchModel<LuCategory> _smlus = new SearchModel<LuCategory>();
    try
    {
        PagedResult<LuCategory> _cats = await LookupsSvc.GetCategoriesPagedAsync(page, pageSize, searchTerm, searchFilter, sortColumn, sortDescending, ActiveOnly);
        _smlus.TotalItems = _cats.TotalCount;
        _smlus.CurrentPage = page;
        _smlus.PageSize = pageSize;
        _smlus.RecordCount = _cats.Items.Count();
        _smlus.SortColumn = sortColumn;
        _smlus.SearchTerm = searchTerm;
        _smlus.SortDescending = sortDescending;
        _smlus.ActiveOnly = ActiveOnly;
        _smlus.SortedResults = _cats.Items.ToList();

        _smlus.actionName = "CatDisplay";
        _smlus.controllerName = "Lookups";

        return _smlus;

    }
    catch (Exception)
    {
        throw;
    }
}

While the code might be self explanatory, I’ll explain what’s happening here. Notice the two properties, actionName and contollerName. These are used by the partial views to know how to get to the controller method that’s going to serve up the partial view, so you can set it to go anywhere you like. The reason these properties are part of the object, is because this allows the paging partial views to be generic and reusable.

We would normally return the PagedResults object from our web service call. Then I transfer the results into the SearchModel while appending all the search, paging and filtering criteria passed into the method. The reason for this is that we want to retain this data in order to use the SearchModel in the Index view, as well as the partial views used for paging.

So lets go over the controllers first. The Index GET has all the parameters needed for the SearchModel with defaults, so that it can be called the first time with no arguments. 

/// <summary>
/// Index of categories paged
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="searchTerm"></param>
/// <param name="sortFilter"></param>
/// <param name="sortColumn"></param>
/// <param name="sortDescending"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
[HttpGet]
public async Task<IActionResult> Index(int page = 1, int pageSize = 25, string searchTerm = "", string sortFilter = "", string sortColumn = "ShortName", bool sortDescending = false, bool ActiveOnly = true)
{
    try
    {
        var _smlus = await GetCategoriesPagedAsync(page, pageSize, searchTerm, sortFilter, sortColumn, sortDescending, ActiveOnly);

         return View(_smlus);

    }
    catch (Exception)
    {
        throw;
    }
}

Notice how having all the code in a sub method keeps our controller method clean and uncluttered.

Next, we need to have the controller method that returns the PartialView that will service the Ajax calls:

/// <summary>
/// Edit Category, with display of items
/// </summary>
/// <param name="id"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
[HttpGet]
public async Task<ActionResult> Edit(long id, int pageSize = 25)
{
    LuCategoryModel model = new LuCategoryModel();
    try
    {
         if (id >= 0)
         {
             // Get the base LuCategory
             LuCategory _return = await LookupsSvc.GetLuCategoryByIdAsync(id);

            model = Helpers.MappingUtils.LuCatToLuCatModel(_return, false);

             // Get a paged result of some items because we want a paged list of child items as well
            model.Items = await GetItemsPagedAsync(id, 1, pageSize);
        }

        return View(model);

    }
    catch (Exception)
    {
        throw;
    }
}

This is all that’s needed to service the Index view, grid partial view, page count partial view and the pager partial view.

Business Objects for Paging

This entry is part 3 of 7 in the series Ultimate MVC Paging

The .NET framework contains lots of ways to pass around data generically now. Tuples, Dynamic Type, Anonymous types, ExpandoObject, etc. But my favorite is a standard style DTO using Generic collections that I can use for business objects for paging.

For paging in an N-Tier, Repository Pattern environment, I use two different Data Transfer Objects (DTO). The first, is an object used to transfer the basic data from the data layer that contains the collection of return objects or DataRows. The second is a more detailed and flexible object that contains all the properties to satisfy 90% of my paging needs.

DataLayer PageResults

Since my service layer already knows the intimate details of my paging request, when I receive back the response from the data layer, I really only need two things:

  1. The collection I asked for
  2. The total number of items that fit my filters

I’ve always felt that if the API was made public, that less is more.

Yes, I could use the same object that I use in the service layer that has more properties, but I’ve always felt that if the API was made public, that less is more. The data layer return object is one that I call PagedResults, it’s structured like:

using System;
using System.Collections.Generic;

namespace YTG.Models
{

    /// <summary>
    /// Yasgar Technology Group, Inc.
    /// http://www.ytgi.com
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public class PagedResult<TEntity> : IPagedResult<TEntity>
    {

        /// <summary>
        /// Primary constructor
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalCount"></param>
        public PagedResult(List<TEntity> items, int totalCount)
        {
            Items = items;
            TotalCount = totalCount;
        }

        private Lazy<List<TEntity>> m_items = new Lazy<List<TEntity>>(() => new List<TEntity>(), true);

        /// <summary>
        /// IEnumerable list of items returned
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public List<TEntity> Items
        {
            get { return m_items.Value; }
            set
            {
                m_items = new Lazy<List<TEntity>>(() => value, true);
            }
    }

        /// <summary>
        /// Total count of records from query
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalCount { get; set; } = 0;

    }
}

You can read a little more about this object here:

Business Object used for Search and Paging

Your data layer will pull back a dataset or IQueryable resultset. You can stuff that into the PagedResult with the following code:

return new PagedResult<Models.LuCategory>(_entityrows.ToList(), _totalCount);

I usually do this in a service layer before my web service front end, so something like:

  1. Pull back a DataSet or Entity IQueryable collection
  2. Return that to the service layer
  3. Loop through and map to a DTO and stuff into the Paging DTO

A ha! I know what you’re thinking, I would just stuff the DataSet or Entity collection in there and send it back, save some time. Right? I don’t recommend that for one main reason; you’re tying your UI to the structure of your database or ORM. I don’t want to digress in this post as to why this is not desirable, but I promise to discuss it in another post soon.

Hate writing redundant mapping code? Read this post: Stuffing DataSets into your objects.

I return this object from my system REST API or WCF web service. In my service layer in the web site, I combine the results into my UI based DTO that has the rest of the properties needed for paging, called SearchModel. It is IMPERATIVE that this DTO be based on the Interface in the project. The SearchModel object looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Text.Json.Serialization;

namespace YTG.MVC.Lookups.Models
{

    /// <summary>
    /// View Model for Searching and holding results
    /// </summary>
    public class SearchModel<TEntity> : ISearchModel
    {

        /// <summary>
        /// Default constructor
        /// </summary>
        public SearchModel() { }


        public SearchModel(int totalItems, int? page, int pageSize = 10)
        {
            TotalItems = totalItems;
            CurrentPage = page ?? 1;
            PageSize = pageSize;
        }

        private int m_TotalItems = 0;
        private int m_CurrentPage = 1;
        private int m_PageSize = 0;
        private Lazy<List<TEntity>> m_SortedResults = new Lazy<List<TEntity>>(() => new List<TEntity>(), true);

        /// <summary>
        /// Gets or set the total number of items to be paged
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalItems
        {
            get
            { return m_TotalItems; }
            set
            {
                m_TotalItems = value;
                if (m_PageSize > 0)
                { TotalPages = (m_TotalItems + (m_PageSize - 1)) / m_PageSize; }
            }
        }

        /// <summary>
        /// Gets or sets the current page being displayed
        /// Automatically populates the values of other properties
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int CurrentPage
        {
            get
            { return m_CurrentPage; }
            set
            {
                m_CurrentPage = value < 1 ? 1 : value;
                StartPage = m_CurrentPage - 5;
                EndPage = m_CurrentPage + 4;

                if (StartPage <= 0)
                {
                    EndPage -= (StartPage - 1);
                    StartPage = 1;
                }

                if (EndPage > TotalPages)
                {
                    EndPage = TotalPages;
                    if (EndPage > 10)
                    {
                        StartPage = EndPage - 9;
                    }
                }
            }
        }

        /// <summary>
        /// Gets or sets the page size currently set
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int PageSize
        {
            get
            { return m_PageSize; }
            set
            {
                m_PageSize = value;
                if (m_TotalItems > 0)
                { TotalPages = (m_TotalItems + (m_PageSize - 1)) / m_PageSize; }
                // To get EndPage set correctly if this property is set last
                CurrentPage = m_CurrentPage;
            }
        }

        /// <summary>
        /// Gets or set the total number of pages based on the TotalItems and PageSize
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalPages { get; set; } = 0;

        /// <summary>
        /// Gets or sets the start page for the pager display
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int StartPage { get; set; } = 0;

        /// <summary>
        /// Gets or sets the end page for the pager display
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int EndPage { get; set; } = 0;

        /// <summary>
        /// Gets or sets the search filter for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SearchFilter { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the search term for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SearchTerm { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the sort expression for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SortColumn { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the previous sort expression for the paged items when posting back to controller
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string PrevSortColumn { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the count of records in the current page
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int RecordCount { get; set; } = 0;

        /// <summary>
        /// Gets or sets the sort order, default is asc
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public bool SortDescending { get; set; } = false;

        /// <summary>
        /// Gets or sets whether the paged items only contain active records
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        [Display(Name = "Active Only")]
        public bool ActiveOnly { get; set; } = true;

        /// <summary>
        /// Gets or sets the Reference Id, to be used for a parent object
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public long RefId { get; set; } = 0;

        /// <summary>
        /// Gets or sets the Reference Unique Id, to be used for a parent object
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public Guid RefUniqueId { get; set; } = Guid.Empty;

        /// <summary>
        /// Gets or sets the List of pre-sorted results
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public List<TEntity> SortedResults
        {
            get { return m_SortedResults.Value; }
            set
            {
                m_SortedResults = new Lazy<List<TEntity>>(() => value, true);
            }
        }


        /// <summary>
        /// Gets or sets the controller name for this Model
        /// Needed for Pagination partial views
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string controllerName { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the action name for this model
        /// Needed for Pagination partial views
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string actionName { get; set; } = string.Empty;

    }
}

I use the simpler PagedResults to return the data collection and the total count from my repository. Then push this data, combined with whatever filter options were sent in back to the View.

You can take a look at how these objects are used in my sample app on GitHub:

https://github.com/YTGI/YTG-MVC-Lookups

Stored Procedure for Paging in SQL

Implementing paging correctly is critically important. If you’re shop still uses, or insists on stored procedures, that’s fine. It’s just as easy to write a stored procedure for paging in SQL.

This example was developed on Microsoft SQL server 2016, although I believe it will work fine back to SQL 2012, not sure about SQL 2008.

As with any solution, the idea is to keep the data at rest and only pull out what you need to return. This example uses the AdventureWorks database.

This example has all of the typical parameters that I use on many paging implementations. You can trim out the ones not needed for your implementation.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Created by Jack Yasgar, www.jackyasgar.net
CREATE PROCEDURE [dbo].[uspGetOrderDetailsYTGI]
	@PageNumber			INT,
	@PageSize			INT,
	@SearchTerm			VARCHAR(100) = NULL,
	@SortDirection		VARCHAR(4) = 'ASC',
	@SortColumn			VARCHAR(100) = 'lastname',
	@Filter				VARCHAR(50) = '',
	@ActiveOnly			BIT
AS
BEGIN
    SET NOCOUNT ON;

 	IF ((@PageNumber = 0) OR (@PageNumber IS NULL))
	BEGIN
		SET @PageNumber = 1;
	END

	IF ((@PageSize = 0) OR (@PageSize IS NULL))
	BEGIN
		SET @PageSize = 25;
	END

	DECLARE @FirstRec INT, @LastRec INT;
	DECLARE @OnlineOrder BIT;
	SET @OnlineOrder = (SELECT CONVERT(BIT, @Filter));

	SELECT @FirstRec = (@PageNumber - 1) * @PageSize
	SELECT @LastRec = (@PageNumber * @PageSize + 1);
	

	SELECT 
		sod.OrderQty,
		sod.ProductID,
		p.[Name] AS ProductName,
		p.ProductNumber,
		soh.ShipDate,
		sod.UnitPrice,
		soh.OnlineOrderFlag
	FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
		LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
		LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
	WHERE 
		(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
		AND
		(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
		AND
		(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
	ORDER BY 
		CASE WHEN @SortDirection = 'ASC' THEN
			CASE @SortColumn 
				WHEN 'ProductName'		THEN p.[Name]
				WHEN 'ProductNumber'	THEN p.ProductNumber
			END
		END,
		CASE WHEN @SortDirection = 'DESC' THEN
			CASE @SortColumn 
				WHEN 'ProductName'		THEN p.[Name]
				WHEN 'ProductNumber'	THEN p.ProductNumber
			END
		END DESC
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

		-- Return the full count for pager
	SELECT COUNT(*) AS TotalCount FROM (
			SELECT DISTINCT sod.SalesOrderDetailID
			FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
				LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
				LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
			WHERE 
				(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
				AND
				(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
				AND
				(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
		) AS Counting

END

This query uses the SQL OFFSET command to start where we need to based upon the PageNumber and PageSize values passed in as arguments. It then uses the FETCH command to retrieve a specific row. You can take a look at the documentation on FETCH here:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/fetch-transact-sql?view=sql-server-ver16

Once you receive back the DataSet in your Repository, you’ll need to stuff the data into a collection of DTO’s in your ServiceLayer for transfer through your web service: Stuffing DataSets into your objects.

DataLayer Level Paging

This entry is part 2 of 7 in the series Ultimate MVC Paging

This post was revised and updated on 10/24/2020.

If we’re going to implement paging in our UI, the first thing we’ll need is code that will return a paged set of data from the database. The ultimate goal is to only retrieve the exact quantity of filtered, sorted records from the database.

I’m going to be using the AdventureWorks database for some of these examples. The code in the GitHub project DOES NOT use AdventureWorks, it uses the YTGI Lookups database tables. You can get the AdventureWorks database from Microsoft’s site here: AdventureWorks Download

The complete solution that I show in this Series can cloned here: Paging Demo Download. This solution was created in Visual Studio 2019 Professional using MVC in .NET Core 3.1.

Here are three posts showing retrieving data in a paged format. Once you look at these, you can return to this post to continue to the next step.

Paging with a Stored Procedure

Paging with Entity Framework – Simple

Paging with Entity Framework – Advanced

Ultimate Guide to MVC Paging!

This entry is part 1 of 7 in the series Ultimate MVC Paging
MVC Paging Partial Views

After searching the web for MVC paging techniques, I realized that 90% or more of the examples I found on the web would not solve my problem. Some of them are so bad that they could get me fired if I followed their example. (copy -> paste). They so oversimplify the need, that they render their solutions useless. The intentions are good, but the implementation is lacking.

Have you:

  • Wanted to add paging to your MVC site, but think it’s too complicated?
  • Implemented it on one page and hope you never have to do it again?
  • After implementing paging thought, “There’s got to be a better way?”

The most popular problem with most examples that I found is that they show pulling back the entire collection from the database and paging the results in the UI (in the controller). This is fine if you’re absolutely positive, sure beyond belief, willing to bet your job, that the results will never exceed a hundred records or so…

If you want to become an expert on how to implement fast, extremely flexible paging in all your MVC applications, then figure that this is going to take an hour or two. If you’re looking to “turn on paging”, then feel free to click on the “Back” button, I won’t be offended.

Not implementing paging, or implementing it incorrectly can cause serious problems with the performance of your web site. Below are some symptoms that I’ve seen that leads me to believe there could be a problem with the paging implementation on a web site:

  1. Complaints that the web site slows down, but nobody can see high utilization on the web server or database server (and no expensive queries)
  2. Complaints that the web site sometimes stops responding, occasionally for minutes at a time
  3. Complaints that the web site sometimes suddenly throws users back to the login screen while they’re working
  4. Complaints that the slow downs are intermittent, and can’t be reproduced in the test environment

I know you may be thinking, what does this have to do with paging? Well, it’s tangentially related, as it has to do with writing bad code without thinking about what the consequences are. The reason these issues don’t tax the servers is that doing a SELECT * FROM Clients doesn’t really tax the CPU that much. Now, if you were watching the NIC card traffic, that would be a different story.

The biggest issue that I see with code examples on the web:

  1. They show bringing back every row in the table and then page in the controller (No, no, please don’t do this!)
  2. They show using a LINQ query in the controller to Skip and Take
  3. They assume that I’m pulling back my data directly from the database in my Web Application
// Example of code that is ridiculous outside of a POC app
return View(MyRepository.GetClients().Skip((page - 1 ?? 0) * (rows ?? 3)).Take(rows ?? 3));

This LINQ query will work well if:

  1. The MyRepository is an EntityFramework repository
  2. AND you’re implementing your Repository in your web site
  3. AND it doesn’t dispose of the Entity context inside the method
  4. AND the GetClients() method doesn’t call ToList() inside of it
  5. AND GetClients() is not executing a stored procedure

If any of the above is not true, then this method is no better than the first method that pulls the entire table back.

Another issue with this example is that it assumes that the Repository is part of your web project AND you’re using the objects that are in your Entity Model. So no DTO’s, no business objects, and absolutely no Web Service in the middle.

The reason I’m showing this, is that I’ve never seen these issues discussed in the posts. A developer could easily implement this on a GetClients() that again, returns the entire table contents and then pages on the UI side.

Just in case you forgot, the web is stateless, so if IIS or most other Web Servers feel that they’ve had about enough with the application memory usage, they just reset themselves. Shouldn’t cause a problem right? Except if you’re depending on session to keep people logged in, then they get kicked back to the login screen. (BTW, this only happens if you have your own custom code looking for a session object to verify the user is logged in. If you use the proper MVC standard Authorization, they shouldn’t be sent to the login screen.) But not to minimize the impact, because all users get kicked out, not just the one user.

Almost every web, and even windows project I’ve ever worked on followed the Repository pattern in that there were separations of concerns, i.e.:

Database → DataLayer → ServiceLayer → WebService → ProxyClass → ServiceLayer → UI

I don’t want to digress into a discussion of patterns. But the ServiceLayer behind the WebService will convert the Entity Framework object or DataSet to a DTO collection. Then the ServiceLayer behind the UI will likely convert it to a ViewModel. We don’t want to do all that work on 5000 records so that we can ultimately display 25 to the user…

This search on Google returned 466,000 records. Do you think that they were all returned and paged in the controller? I think not…

The reason I’m discussing this is to get your imagination started. If you didn’t want to transfer tons of data over the wire from your WebService, then where is the best place to implement Paging? You guessed it, in the DataLayer (also referred to as Data Access Layer (DAL)). As close to the data as you can possibly get.

Alright, let’s get to the nuts and bolts. I’m going to show you a full implementation, so you can trim it down if your particular need is less complicated.

Anatomy of a Paging Request

The following are the properties that I’ve been able to accomplish almost any paging task I’ve ever needed on the client side:

Property Type Notes
SortedResults List<T> The list of results, without it, we don’t need paging!
TotalItems int The total number of items that would be returned, if we returned them all at once
CurrentPage int The current page the user is on
PageSize int The size of the page, usually an option for the user to select
TotalPages int The total number of pages (TotalItems/PageSize rounded up)
StartPage int The start page, for when you have more pages than will fit on one widget
EndPage int The end page, for when you have more pages than will fit on one widget
SearchFilter string A filter to use in addition to any search criteria, usually selected from a dropdown, i.e. year, division, salesman, etc.
SearchTerm string A search term, usually typed in a search box by the user
SortExpression string The column name to sort the results by
RecordCount int The quantity of records in this particular result set
SortOrder string The order that you want the result set, i.e.: “ASC”, “DESC”
ActiveOnly bool Another filter flag, if you have an IsActive flag in your table, or any other criteria to knock out inactive rows
RefId int Typically used when there is a Parent/Child relationship. i.e. Your SortedList is a list of Project tasks, then you’ll need to know the parent project PK Id

Paging with Entity Framework (Advanced)

This example is only called “Advanced” because it addresses multiple tables for paging. This will often be the case, but you can view a simple example that addresses one table here: Paging with Entity Framework (Simple)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

        /// <summary>
        /// Complicated Paged results from multiple tables in EF Query
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        /// <param name="PageNumber"></param>
        /// <param name="PageSize"></param>
        /// <param name="SearchTerm"></param>
        /// <param name="SearchFilter"></param>
        /// <param name="SortExpression"></param>
        /// <param name="SortOrder"></param>
        /// <param name="ActiveOnly"></param>
        /// <returns></returns>
        public PagedResult<Models.SalesProduct> GetSalesDetailsByEF(int PageNumber,
                                                                    int PageSize,
                                                                    string SearchTerm,
                                                                    string SearchFilter,
                                                                    string SortExpression,
                                                                    string SortOrder,
                                                                    bool ActiveOnly)
        {
            try
            {
                int _skipRows = (PageNumber - 1) * PageSize; // if this is not the first call, need move forward
                int _totalCount = 0; // placeholder for the total amount of records

                using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
                {
                    // Using var because this is returning an anonymous type
                    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                                       join sdetail in _efEntities.SalesOrderDetails on sheader.SalesOrderID equals sdetail.SalesOrderID
                                       join product in _efEntities.Products on sdetail.ProductID equals product.ProductID
                                       select new Models.SalesProduct()
                                       {
                                           OrderQuantity = sdetail.OrderQty,
                                           ProductId = sdetail.ProductID,
                                           ProductName = product.Name,
                                           ProductNumber = product.ProductNumber,
                                           ShipDate = sheader.ShipDate ?? DateTime.MinValue,
                                           UnitPrice = sdetail.UnitPrice,
                                           OnlineOrderFlag = sheader.OnlineOrderFlag
                                       });

                    if (ActiveOnly)
                    {
                        // Showing how to use ActiveOnly without a boolean flag as an example
                        _entityrows = _entityrows.Where(er => er.ShipDate.Year > 2013);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchFilter))
                    {
                        // This can be customized for each implementation
                        bool _onlineOrder;
                        bool.TryParse(SearchFilter, out _onlineOrder);
                        _entityrows = _entityrows.Where(f => f.OnlineOrderFlag == _onlineOrder);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchTerm))
                    {
                        // This can be customized for each implementation
                        _entityrows = _entityrows.Where(f => f.ProductName.Contains(SearchTerm.Trim()));
                    }

                    // Getting count will execute a SELECT COUNT(*)
                    // Like to do this before adding sort criteria
                    _totalCount = _entityrows.Count();

                    if (!string.IsNullOrWhiteSpace(SortExpression))
                    {
                        bool IsSortDESC = false;
                        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }

                        _entityrows = _entityrows.OrderBy<Models.SalesProduct>(SortExpression, IsSortDESC);
                    }

                    _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

                    return new PagedResult<Models.SalesProduct>(_entityrows.ToList(), _totalCount);


                }
            }
            catch (Exception)
            {
                throw;
            }
        }

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Business Object used for Search and Paging

When I’m implementing a paged query, I wanted a simple object that I could return a strongly typed list of values along with a total count from my query. The business object used for search and paging also has the return of a List<T> of objects along with a TotalCount property from my Repository. You’ll see this as the return object in many of my sample paging posts.

This is basically a DTO, it doesn’t have any specific functionality.

    /// <summary>
    /// Yasgar Technology Group, Inc.
    /// http://www.ytgi.com
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    [DataContract(Name = "PagedResult", Namespace = "", IsReference = true)]
    public class PagedResult<TEntity> : IPagedResult<TEntity>
    {

        #region Constructors

        /// <summary>
        /// Primary constructor
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalCount"></param>
        public PagedResult(IEnumerable<TEntity> items, int totalCount)
        {
            m_items = items;
            m_totalCount = totalCount;
        }

        #endregion // Constructors

        #region Fields

        private IEnumerable<TEntity> m_items;
        private int m_totalCount = 0;

        #endregion // Fields

        #region Properties

        /// <summary>
        /// IEnumerable list of items returned
        /// </summary>
        [DataMember]
        public IEnumerable<TEntity> Items
        {
            get { return m_items; }
            protected set { m_items = value; }
        }

        /// <summary>
        /// Total count of records from query
        /// </summary>
        [DataMember]
        public int TotalCount
        {
            get { return m_totalCount; }
            protected set { m_totalCount = value; }
        }

        #endregion // Properties

    }

This data transfer object (DTO) is used to house the results from a paging query that has the minimum amount of information needed to return from the database. It does not need to have search criteria etc, as it is assumed that the calling method already has all that data. This will help you have a standard return object that not only has the collection of objects, but the total count that would have been returned had there not been any filters.

Here is an example of this object in use:

Paging with Entity Framework (Simple)

Paging with Entity Framework (Simple)

Whenever you have a need to show a paged list of your items, it is a must that this is done in your repository in Entity Framework so that you’re not passing around more data than is necessary. There are too many examples of paging on the web which show paging at the UI level, especially terrible ones are showing doing a LINQ to SQL query with ToList() after it. Horrible!

DON’T EVER DO THIS!

var _result = dbContext.Customers.ToList().Skip(page).Take(pageSize);

The following example is only called “Simple” because it addresses one table for paging. This will often be the case, but you can view a more complicated query here: Paging with Entity Framework (Advanced)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

This LINQ code was pulled directly from the LookupsRep class in the YTG MVC Lookups and Paging Demo Download.

/// <summary>
/// Simple Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="Page"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortColumn"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<PagedResult<LuCategories>> GetLuCategoriesByEFAsync(int Page,
            int PageSize,
            string SearchTerm,
            string SearchFilter,
            string SortColumn,
            string SortOrder,
            bool ActiveOnly)
{
    try
    {
        int _skipRows = (Page - 1) * PageSize; // if this is not the first call, need move forward
        int _totalCount = 0; // placeholder for the total amount of records

        // Using var because this is returning an anonymous type
        var _entityrows = (from item in LuContext.LuCategories.Include(a => a.LuItems)
                           select item);

        if (ActiveOnly)
        {
            // Showing how to use ActiveOnly without a boolean flag as an example
            _entityrows = _entityrows.Where(er => er.IsActive == true);
        }

        if (!string.IsNullOrWhiteSpace(SearchFilter))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.ShortName == SearchFilter);
        }

        if (!string.IsNullOrWhiteSpace(SearchTerm))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.Name.Contains(SearchTerm.Trim()));
        }

        // Getting count will execute a SELECT COUNT(*)
        // Like to do this before adding sort criteria
        _totalCount = _entityrows.Count();

        if (!string.IsNullOrWhiteSpace(SortColumn))
        {
            bool IsSortDESC = false;
            if (SortOrder.ToLower() == "desc") { IsSortDESC = true; }

            _entityrows = _entityrows.OrderBy(SortColumn, IsSortDESC);
        }

        _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

        return new PagedResult<Models.LuCategories>(await _entityrows.ToListAsync(), _totalCount);

    }
    catch (Exception)
    {
        throw;
    }
}

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging