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.
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.