So after some long time I had to get in to some old problem. But this time I figured it out. So lets go through this “MVC WebGrid pagination and sorting with Entity Framework”
With this implementation, I’m able to do the pagination in database level. That means this doesn’t load all the records at once , instead of loading whole result set it will load batch of records. You can check this with Sql server profiler.
For this solution I have used following technologies
I use visual studio as IDE and download these frameworks / libs by using nuget packages. For this example I used Northwind database .
Download the project
Data Layer
Add Northwind.edmx file to data layer
Northwind.edmx
and here I add another view-model called “FilterModel” which I’m going to use it in my Pager controller’s Index.cshtml
public class FilterModel
{
public int Page { get; set; }
public int PageSize { get; set; }
public int TotalCount { get; set; }
public string SearchText { get; set; }
public string Sort { get; set; }
public string Sortdir{ get; set; }
public List<Order> DataModel { get; set; }
}
Ok… That’s it Now we have done with our Data. So time to move on to service layer…
Service Layer [ Business Layer ]
Service Layer
public class OrderService
{
public FilterModel GetFilterModel(FilterModel model)
{
model.Page = model.Page == 0 ? 1 : model.Page;
model.PageSize = model.PageSize == 0 ? 5 : model.PageSize;
model.SearchText = string.IsNullOrEmpty(model.SearchText) ? model.SearchText : model.SearchText.Trim();
model.Sort = string.IsNullOrEmpty(model.Sort) ? "CustomerID" : model.Sort;
model.Sortdir = string.IsNullOrEmpty(model.Sortdir) ? "ASC" : model.Sortdir;
int skipValue = (model.Page - 1) * model.PageSize;
Func<Order, bool> prediction = x => string.IsNullOrEmpty(model.SearchText) ||
model.SearchText == x.CustomerID ||
model.SearchText == x.ShipAddress ||
model.SearchText == x.ShipName ||
model.SearchText == x.ShipCity;
if (model.Sortdir.ToLower() == "asc")
{
model.DataModel = new NorthwindEntities().Orders
.Where(prediction)
.OrderBy(GenericEvaluateOrderBy<Order>(model.Sort)).Skip(skipValue).Take(model.PageSize).ToList();
}
else
{
model.DataModel = new NorthwindEntities().Orders
.Where(prediction)
.OrderByDescending(GenericEvaluateOrderBy<Order>(model.Sort)).Skip(skipValue).Take(model.PageSize).ToList();
}
model.TotalCount = new NorthwindEntities().Orders
.Where(prediction)
.Count();
return model;
}
public Func<TSource, object> GenericEvaluateOrderBy<TSource>(string propertyName)
{
//this will return p=>p.<< your proeprty name >>
var type = typeof(TSource);
var parameter = Expression.Parameter(type, "p");
var propertyReference = Expression.Property(parameter, propertyName);
return Expression.Lambda<Func<TSource, object>>(propertyReference, new[] { parameter }).Compile();
}
}
GetFilterModel – I call this method from my action method in controller and pass FilterModel. You can see in first couple of lines I’m checking its properties and manually assigning values. just because of when my Index.cshtml loads first time I have to have some default values.
To implement pagination and sorting we have to use some kind of Linq query like this
var result = context.<< table name >>.where( << your search text >> ).OrderBy or OrderDescendingBy ( << sort column name >> ).Skip( << (Current page number-1) * rows per page >> ).Take( << rows per page >> ).ToList<< table name >>()
by looking at this code you can see when user tries to search something or when he changes the sort column we need to change this query.
So I used this code block to get Where condition
Func<Order, bool> prediction = x => string.IsNullOrEmpty(model.SearchText) ||
model.SearchText == x.CustomerID ||
model.SearchText == x.ShipAddress ||
model.SearchText == x.ShipName ||
model.SearchText == x.ShipCity;
ok lets move to order by clause. I had following problems
- How can I sort when my sort column gets change
- how can I handle Ascending and Descending
GenericEvaluateOrderBy<TSource>(string propertyName) – Suppose what will happen If I don’t use this method… Then I’m running to pretty much if else || switch case code block. So instead of implementing column name with conditions , I passed column name as a variable in to my Linq query then I’m able to reduce my code block.
( actually I got some help from this web article so much of credit goes to the author of article https://www.simple-talk.com/dotnet/.net-framework/dynamic-linq-queries-with-expression-trees/ )
Controller
Add a controller to your mvc project. I named this as a Pager you can give what ever your prefer
PageController
public class PagerController : Controller
{
public ActionResult Index(FilterModel model)
{
OrderService service = new OrderService();
return View(service.GetFilterModel(model));
}
}
Views
Next thing is a adding a view to the project.
Index.cshtml
Here I need you to tell you some couple of things, as I told you earlier I used bootstrap ui , font-awsome and jquery .Actually you can get these stuffs through Nuget package manager in visual studio or else download from their respective sites.
once you get these things make sure you bundle them in BundleConfig.cs you can find this class in App_start folder
BundleConfig.cs
and here is the content of class for further clarification…
public class BundleConfig
{
// For more information on bundling, visit http://go.microsoft.com/fwlink/?LinkId=301862
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
"~/Scripts/jquery-{version}.js"));
// Use the development version of Modernizr to develop with and learn from. Then, when you're
// ready for production, use the build tool at http://modernizr.com to pick only the tests you need.
bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
"~/Scripts/modernizr-*"));
bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
"~/Scripts/bootstrap.js",
"~/Scripts/respond.js"));
bundles.Add(new StyleBundle("~/Content/css").Include(
"~/Content/bootstrap.css",
"~/Content/site.css"));
bundles.Add(new StyleBundle("~/Content/font-awesome").Include("~/Content/font-awesome.css"));
}
}
In my _Layout.cshtml
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Styles.Render("~/Content/font-awesome")
@Scripts.Render("~/bundles/modernizr")
@RenderSection("styles", required: false)
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Application name", "Index", "Home", null, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li class="navbar-text">Hello, @User.Identity.Name!</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - My ASP.NET Application</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
Views –> Pager –> Index.cshtml
@using PaginationTest.Models
@model FilterModel
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}
@section scripts{
<script type="text/javascript">
var gridSort = '@Model.Sort';
var gridSortDir = '@Model.Sortdir';
</script>
<script src="~/Scripts/AppScripts/PagerScript.js"></script>
}
@section styles
{
<style type="text/css">
.space-box {
margin: 10px;
}
</style>
}
@{
WebGrid grid = new WebGrid(null, rowsPerPage: Model.PageSize, canPage: true, defaultSort: "CustomerID");
grid.Bind(Model.DataModel, rowCount: Model.TotalCount, autoSortAndPage: false);
}
@using (@Html.BeginForm("Index", "Pager", FormMethod.Post, new { @id = "frmDetails" }))
{
<div class="container">
<div class="panel">
<div class="panel-heading">
<h3 class="panel-title">Order Details</h3>
</div>
<div class="panel-body">
<div class="row">
<div class="col-md-4">
<div class="form-inline">
@Html.DropDownListFor(m => m.PageSize,
new List<SelectListItem>
{
new SelectListItem() { Text = "5", Value = "5" },
new SelectListItem() { Text = "10", Value = "10" },
new SelectListItem() { Text = "20", Value = "20" }
},
new { @class = "form-control" }
)
<span>Rows per page</span>
</div>
</div>
<div class="col-md-2"><p>Records :@{ var x = (Model.Page * Model.PageSize) >= Model.TotalCount ? Model.TotalCount : (Model.Page * Model.PageSize); } @x out of @Model.TotalCount</p></div>
<div class="col-md-2"></div>
<div class="col-md-4">
<div class="input-group">
<div class="input-group-addon"><span class="glyphicon glyphicon-search"></span></div>
<input class="form-control" type="text" name="SearchText" id="SearchText" value="@Model.SearchText" />
</div>
</div>
</div>
<div class="row space-box"></div>
<div class="row">
@if (Model.DataModel != null && Model.DataModel.Count > 0)
{
@grid.GetHtml(tableStyle: "table table-bordered", columns:
grid.Columns(
grid.Column("CustomerID", "Customer ID"),
grid.Column("ShipName", "Ship Name"),
grid.Column("ShipAddress", "Ship Address"),
grid.Column("ShipCity", "Ship City")
)
)
}
else
{
<div class="alert alert-info" role="alert">No Data</div>
}
</div>
</div>
<div class="panel-footer">
<div class="row">
<div id="gridPager" class="space-box">
@if (Model.DataModel != null && Model.DataModel.Count > 0)
{
@grid.PagerList(WebGridPagerModes.All)
}
</div>
</div>
</div>
</div>
</div>
}
You need to remember following steps are really important when you need to implement pagination and sorting
@{
WebGrid grid = new WebGrid(null, rowsPerPage: Model.PageSize, canPage: true, defaultSort: "CustomerID");
grid.Bind(Model.DataModel, rowCount: Model.TotalCount, autoSortAndPage: false);
}
- set the default sort column ( CustomerID )
- set the total row count of your search query ( Model.TotalCount )
I hide default pager by using J query and implemented bootstrap pager But you need to have web gird pagination extension method to work this properly. This is the extension class and it is not mine I got it from this article
Webgrid Extension
public static class WebGridExtensions
{
public static HelperResult PagerList(
this WebGrid webGrid,
WebGridPagerModes mode = WebGridPagerModes.NextPrevious | WebGridPagerModes.Numeric,
string firstText = null,
string previousText = null,
string nextText = null,
string lastText = null,
int numericLinksCount = 5)
{
return PagerList(webGrid, mode, firstText, previousText, nextText, lastText, numericLinksCount, explicitlyCalled: true);
}
private static HelperResult PagerList(
WebGrid webGrid,
WebGridPagerModes mode,
string firstText,
string previousText,
string nextText,
string lastText,
int numericLinksCount,
bool explicitlyCalled)
{
int currentPage = webGrid.PageIndex;
int totalPages = webGrid.PageCount;
//int lastPage = totalPages - 1;
int lastPage = (totalPages - 1) < 0 ? 0 : (totalPages - 1);
var ul = new TagBuilder("ul");
var li = new List<TagBuilder>();
if (ModeEnabled(mode, WebGridPagerModes.FirstLast)) {
if (String.IsNullOrEmpty(firstText)) {
firstText = "First";
}
var part = new TagBuilder("li") {
InnerHtml = GridLink(webGrid, webGrid.GetPageUrl(0), firstText)
};
if (currentPage == 0) {
part.MergeAttribute("class", "disabled");
}
li.Add(part);
}
if (ModeEnabled(mode, WebGridPagerModes.NextPrevious)) {
if (String.IsNullOrEmpty(previousText)) {
previousText = "Prev";
}
int page = currentPage == 0 ? 0: currentPage - 1;
var part = new TagBuilder("li") {
InnerHtml = GridLink(webGrid, webGrid.GetPageUrl(page), previousText)
};
if (currentPage == 0) {
part.MergeAttribute("class", "disabled");
}
li.Add(part);
}
if (ModeEnabled(mode, WebGridPagerModes.Numeric) && (totalPages > 1)) {
int last = currentPage + (numericLinksCount / 2);
int first = last - numericLinksCount + 1;
if (last > lastPage) {
first -= last - lastPage;
last = lastPage;
}
if (first < 0) {
last = Math.Min(last + (0 - first), lastPage);
first = 0;
}
for (int i = first; i <= last; i++) {
var pageText = (i + 1).ToString(CultureInfo.InvariantCulture);
var part = new TagBuilder("li") {
InnerHtml = GridLink(webGrid, webGrid.GetPageUrl(i), pageText)
};
if (i == currentPage) {
part.MergeAttribute("class", "active");
}
li.Add(part);
}
}
if (ModeEnabled(mode, WebGridPagerModes.NextPrevious)) {
if (String.IsNullOrEmpty(nextText)) {
nextText = "Next";
}
int page = currentPage == lastPage ? lastPage: currentPage + 1;
var part = new TagBuilder("li") {
InnerHtml = GridLink(webGrid, webGrid.GetPageUrl(page), nextText)
};
if (currentPage == lastPage) {
part.MergeAttribute("class", "disabled");
}
li.Add(part);
}
if (ModeEnabled(mode, WebGridPagerModes.FirstLast)) {
if (String.IsNullOrEmpty(lastText)) {
lastText = "Last";
}
var part = new TagBuilder("li") {
InnerHtml = GridLink(webGrid, webGrid.GetPageUrl(lastPage), lastText)
};
if (currentPage == lastPage) {
part.MergeAttribute("class", "disabled");
}
li.Add(part);
}
ul.InnerHtml = string.Join("", li);
var html = "";
if (explicitlyCalled && webGrid.IsAjaxEnabled) {
var span = new TagBuilder("span");
span.MergeAttribute("data-swhgajax", "true");
span.MergeAttribute("data-swhgcontainer", webGrid.AjaxUpdateContainerId);
span.MergeAttribute("data-swhgcallback", webGrid.AjaxUpdateCallback);
span.InnerHtml = ul.ToString();
html = span.ToString();
} else {
html = ul.ToString();
}
return new HelperResult(writer => {
writer.Write(html);
});
}
private static String GridLink(WebGrid webGrid, string url, string text)
{
TagBuilder builder = new TagBuilder("a");
builder.SetInnerText(text);
builder.MergeAttribute("href", url);
if (webGrid.IsAjaxEnabled) {
builder.MergeAttribute("data-swhglnk", "true");
}
return builder.ToString(TagRenderMode.Normal);
}
private static bool ModeEnabled(WebGridPagerModes mode, WebGridPagerModes modeCheck)
{
return (mode & modeCheck) == modeCheck;
}
}
Scripts
So we have made so far. This is the last section. Create a script file name PagerScript.js and put that in this folder path.
Pager Script
function getParameterByName(url, name) {
name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
results = regex.exec(url);
return results == null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));
}
function setArrowImages() {
$('table th').each(function () {
//get direction value
var direction = getParameterByName($(this).find('a').attr('href'), 'sortdir');
var header = $(this).find('a[href*="sortdir"]');
header.html(header.html() + ' <i class="fa fa-sort pull-right" />');
if (gridSort == getParameterByName($(this).find('a').attr('href'), 'sort')) {
switch (gridSort) {
case 'CustomerID': if (gridSortDir.toLowerCase() == "asc") { $(this).find('i').removeAttr('class').addClass('fa fa-sort-asc pull-right') } else { $(this).find('i').removeAttr('class').addClass('fa fa-sort-desc pull-right') }; break;
case 'ShipName': if (gridSortDir.toLowerCase() == "asc") { $(this).find('i').removeAttr('class').addClass('fa fa-sort-asc pull-right') } else { $(this).find('i').removeAttr('class').addClass('fa fa-sort-desc pull-right') }; break;
case 'ShipAddress': if (gridSortDir.toLowerCase() == "asc") { $(this).find('i').removeAttr('class').addClass('fa fa-sort-asc pull-right') } else { $(this).find('i').removeAttr('class').addClass('fa fa-sort-desc pull-right') }; break;
case 'ShipCity': if (gridSortDir.toLowerCase() == "asc") { $(this).find('i').removeAttr('class').addClass('fa fa-sort-asc pull-right') } else { $(this).find('i').removeAttr('class').addClass('fa fa-sort-desc pull-right') }; break;
default:
}
}
});
};
$(function () {
$('#gridPager ul').addClass('pagination');
$('tfoot').hide();
$('#PageSize').change(function () {
$('#frmDetails').submit()
return false;
});
$('#SearchText').change(function () {
$('#frmDetails').submit()
return false;
});
$('#gridPager a').click(function (e) {
var form = $('#frmDetails');
form.attr("action", this.href);
$(this).attr("href", "javascript:");
form.submit();
});
$('th a').click(function () {
var form = $('#frmDetails');
form.attr("action", this.href);
$(this).attr("href", "javascript:");
form.submit();
});
setArrowImages();
});
you may be wondering how I stopped default “GET” request… let me explain
$('th a').click(function () {
var form = $('#frmDetails');
form.attr("action", this.href);
$(this).attr("href", "javascript:");
form.submit();
});
from this part I’m handling pagination. When user clicks pager we need to do a form submit (not a AJAX form submit It must be a fully form postback to server ). So I changed current form action. To do that I changed anchor tag href value. It is some thing like http://localhost:56896/?page=1 but at the same time I need to stop that html link click, you can modify href value like this $(this).attr(“href”,”javascript:”) Thereby it doesn’t do anything when user clicks this link and it will not send this “GET” request but it will send “POST” request to the server in our case it will invoke our “Index” action method. If you feel some strange about this $(this).attr(“href”,”javascript:”) code then you can change it to $(this).attr(“href”,”#”). Because both of them do the same thing.
So I hope this post will help you too and this is the final result
If you want to know anything and share your knowledge with me please do comment. Thank you all have a nice day with nice web grid.
WebGrid pagination with bootstrap UI and EF
Download the project
Writing Wall 