Paging in MVC .NET Framework not working, how to get it to work

Question

I try to convert an old asp.net webforms application to an MVC application using Entity Framework. I generated the model for this application using the scaffold-DbContect command which is part of the EF Core Tools. I was able to generate a list of items. In this case, I'm converting an old issuetracker system. The list is a list of the registered bugs. But this is quite a long list (5K+ records), so I would like to add paging. I came across this item on SO and the third answer looked like it could work for me.

So I added the next code to my controller index-action

  public ActionResult Index(int page = 0)
    {

        int pageSize = 10;

        var qry = db.Bugs
        .Include(e => e.Organization)
        .Include(e => e.AssignedUser)
        .Include(e => e.UpdatedUser)
        .Include(e => e.ReportedUser)
        .Include(e => e.Project)
        .Include(e => e.Category)
        .Include(e => e.Status)
        .Include(e => e.Priority)
        .AsNoTracking()
        .AsQueryable();

        var count = qry.Count();

        var data = qry.Skip(page * pageSize).Take(pageSize).OrderBy(e => e.BgId);

        this.ViewBag.MaxPage = (count / pageSize) - (count % pageSize == 0 ? 1 : 0);

        this.ViewBag.Page = page;

        return View(data.ToList());
    }

The problem is, I get no results. If I debug the data var has a count of 10, but there is no output. If I don't filter and just use qry.ToList() in my return View() I get all the records. No exception is thrown either. What am I missing? Anybody a clue? I think I overlook something.

ViewBag is messy... bind the model. (And use PagedList helper class/function!) You can add it straight from VS dependency manager. You'll end up with items in your model (.cs file) that store things like page#, results per page, sort, etc... anything relating to saved view state... — pcalkins, Oct 01 '20 at 20:42
Model Binding: https://learn.microsoft.com/en-us/aspnet/core/mvc/models/model-binding?view=aspnetcore-3.1 — pcalkins, Oct 01 '20 at 20:49
for your question though, I think it's because you've got a var there (compiler decides the type)... try with var data = qry.Skip(page * pageSize).Take(pageSize).OrderBy(e => e.BgId).ToList(); I think "ToList()" will make the query actually run instead of being deferred. — pcalkins, Oct 01 '20 at 20:57
`.AsQueryable()` is not needed. `OrderBy` should be placed before `Skip`. Also update question with actual generated query. If problem still exists, try to comment out Includes one by one. — Svyatoslav Danyliv, Oct 02 '20 at 09:57
@SvyatoslavDanyliv thank you for the reply but based on the previous comment I choose to use the PageListed helper which is working now. — Stephan, Oct 02 '20 at 10:15

Stephan · Accepted Answer · 2020-10-03T11:47:23.587

Thanks to the help of @pcalkins I could solve my problem using the PageList.mvc helper.

I ended up with the next code in my Action:

  // GET: Bugs
    public ActionResult Index(int? page)
    {
        var qry = db.Bugs
        .Include(e => e.Organization)
        .Include(e => e.AssignedUser)
        .Include(e => e.UpdatedUser)
        .Include(e => e.ReportedUser)
        .Include(e => e.Project)
        .Include(e => e.Category)
        .Include(e => e.Status)
        .Include(e => e.Priority)
        .OrderBy(e => e.BgId).ToList();

        int pageSize = 20;
        int pageNumber = page ?? 1;

        return View(qry.ToPagedList(pageNumber, pageSize));
    }

I had to change the view to get it to work with this PageList helper in:

@model PagedList.IPagedList<IssueTracker.Models.Bugs>
@using PagedList.Mvc;
<link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />

@{
    ViewBag.Title = "Issuelist";
}
<div class="container pt-5">
    <div class="pb-2">
        <form action="" class="form-inline">
            @Html.ActionLink("Add", "Create", "Bugs", null, new { @class = "btn btn-success mr-sm-2" })
            <div class="form-group mr-sm-2">
                <select class="custom-select">
                    <option selected="">Open this select menu</option>
                    <option value="1">One</option>
                    <option value="2">Two</option>
                    <option value="3">Three</option>
                </select>
            </div>
            <button type="button" class="btn btn-primary mr-sm-2">
                <i class="fa fa-print"></i>
            </button>
            <button type="button" class="btn btn-primary mr-sm-2">
                <i class="fa fa-file-excel-o"></i>
            </button>
        </form>
    </div>
    <div class="table-responsive">
        <table class="table table-hover tauw-table table-sm" id="tauw-table">
            <thead>
                <tr>
                    <th scope="col">
                        ID
                    </th>
                    <th scope="col">
                        Description
                    </th>
                    <th scope="col">
                        Reported by
                    </th>
                    <th scope="col" style="width:120px">
                        Reported on
                    </th>
                    <th scope="col">
                        Status
                    </th>
                    <th scope="col">
                        Priority
                    </th>
                    <th scope="col">
                        Organisation
                    </th>
                    <th scope="col">
                        Category
                    </th>
                    <th scope="col">
                        Project
                    </th>
                    <th scope="col"></th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model)
                {
                    <tr>
                        <th scope="row">
                            @Html.DisplayFor(modelItem => item.BgId)
                        </th>
                        <td>
                            @Html.DisplayFor(modelItem => item.BgShortDesc)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ReportedUser.UsUsername)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.BgReportedDate)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Status.StName)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Priority.PrName)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Organization.OgName)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Category.CtName)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Project.PjName)
                        </td>
                        <td style="width:50px;">
                            <a href="@Url.Action("Edit", "Bugs",new { id = item.BgId })" class="fa fa-edit"></a>
                            <a href="@Url.Action("Delete", "Bugs",new { id = item.BgId })" class="fa fa-trash"></a>
                        </td>
                    </tr>
                }
            </tbody>
        </table>
        <br />
        Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
    @Html.PagedListPager(Model, page => Url.Action("Index",
        new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))
</div>

I couldn't get it to work right away. It only showed me sometimes one record sometimes 4, very unpredictable. When I tried debugging I saw an error like: "There is already an open DataReader associated with this Connection which must be closed first." When I searched for an answer I came across this site which suggested using the ToList() function with the include() options. This solved my problem for the paging. Hope this can help others as well.

Paging in MVC .NET Framework not working, how to get it to work

1 Answers1