Add, edit, delete list elements

Contents:

  1. Introduction
  2. Add CRUD operations to the generic repository
  3. Define the user model
  4. Add a view for adding and modifying an user
  5. Implement the delete feature
  6. Summary

1. Introduction Go top

We will continue the solution built in the previous post Paging, sorting and filtering a list in ASP.NET MVC 4 and implement new features for adding, modifying and removing a list element. You can download the source code for the previous solution from Codeplex and use it as the starting point for implementing the features discussed here. The final result will look like this:
List-edit result
The main steps are:

  • Extend the generic repository to support CRUD (Create Read Update Delete) operations.
  • Create a view model starting from the User class that was generated by EF that will define the validation rules and will serve as the model for our user edit view.
  • Create controller action methods to support adding and modifying a user and the associated view that knows how to render a User view model.
  • Implement the delete action inside the users list.

2. Add CRUD operations to the generic repository Go top

First we will enhance the generic repository we created in the previous post to support adding, modifying and deleting an entity. We will also add a method for retrieving an entity based on its primary key supposing that the primary key is not composite (is based only on one table column).

Note: None of these methods will call save changes on the context because it will be the invoked from the unit of work.

/// <summary>
/// Retrieve an entity from the repository based on the unique identifier.
/// </summary>
/// <param name="id">The unique identifier of the entity.</param>
/// <returns></returns>
public T GetById(object id)
{
	var entityKey = new EntityKey(EntitySetName, PrimaryKeyName, id);
	object entity;
	if (Context.TryGetObjectByKey(entityKey, out entity))
		return (T)entity;
	
	return null;
}

/// <summary>
/// Add a new item to the repository.
/// </summary>
/// <param name="entity">The element to add to the repository.</param>
/// <returns></returns>
public void Insert(T entity)
{      
	ObjectSet.AddObject(entity);
}

/// <summary>
/// Updates an item in the repository.
/// </summary>
/// <param name="entity">The element to update.</param>
/// <returns></returns>
public void Update(T entity)
{
	if (!IsAttached(entity))
		Context.AttachTo(EntitySetName, entity);

	Context.ObjectStateManager.ChangeObjectState(entity, EntityState.Modified);
}

/// <summary>
/// Deletes an item from the repository.
/// </summary>
/// <param name="entity">The item to be deleted.</param>
/// <returns></returns>
public void Delete(T entity)
{
	if (!IsAttached(entity))
		Context.AttachTo(EntitySetName, entity);

	Context.ObjectStateManager.ChangeObjectState(entity, EntityState.Deleted);
}

/// <summary>
/// Deletes an item from the repository.
/// </summary>
/// <param name="id">The unique identifier of the entity to be deleted.</param>
/// <returns></returns>
public void Delete(object id)
{
	var entity = GetById(id);
	if(entity == null)
		throw new Exception("Entity not found.");
	Delete(entity);
}

/// <summary>
/// Returns true if the entity is attached to the current context.
/// </summary>
/// <param name="entity"></param>
protected bool IsAttached( T entity)
{
	ObjectStateEntry entry;
	Context.ObjectStateManager.TryGetObjectStateEntry(Context.CreateEntityKey(EntitySetName, entity), out entry);

	return !(entry == null || entry.State == EntityState.Detached);
}		

3. Define the user model Go top

Next, we will create the model by adding a partial class for the EF User class. Inside this partial class we define the model validation rules:

  • using attributes for basic field validation rules and
  • implementing the IValidatableObject interface to check if the username is unique among all users.

You can find more details in a previous post that was exclusively dedicated to data validation.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using HelloWorld.Code.Util;

namespace HelloWorld.Code.DataAccess
{
    [MetadataType(typeof(UserMetaData))]
    public partial class User : IValidatableObject
    {
        /// <summary>
        /// Helper property used to store the URL referrer of the current page. Needed for the 'Go back to list' functionality.
        /// </summary>
        public string UrlReferrer { get; set; }

        /// <summary>
        /// Get a list that contains the available user roles.
        /// </summary>
        public IEnumerable<SelectListItem> UserRolesList
        {
            get
            {
                return (new[]
                            {
                                new SelectListItem
                                    {
                                        Selected = (UserRoleId == 0),
                                        Text = "Select...",
                                        Value = string.Empty
                                    }
                            }).Union(CacheManager.GetUserRoles(HttpContext.Current.Request.HttpMethod == "POST").
                                         Select(c => new SelectListItem
                                         {
                                             Selected = (c.UserRoleId == UserRoleId),
                                             Text = c.UserRoleName,
                                             Value = c.UserRoleId.ToString(CultureInfo.InvariantCulture)
                                         }));
            }
        }

        /// <summary>
        /// Saves the model using the repository and unit of work.
        /// </summary>
        public void Save()
        {
            using (var unitOfWork = new UnitOfWork())
            {
                var repository = unitOfWork.GetUserRepository;

                if (UserId == 0)
                    repository.Insert(this);
                else
                    repository.Update(this);

                unitOfWork.Save();                
            }
        }

        /// <summary>
        /// Validates that the username is unique among all users.
        /// </summary>
        /// <param name="validationContext"></param>
        /// <returns></returns>
        public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
        {
            using (var unitOfWork = new UnitOfWork())
            {
                var repository = unitOfWork.GetUserRepository;
                var existingUser = repository.Search(u => u.UserId != UserId &&
                        string.Compare(u.Username, Username, StringComparison.CurrentCultureIgnoreCase) == 0, null, null);
                
                if(existingUser.Count > 0)
                    yield return new ValidationResult("Username already taken. Please choose another username.", new[] { "Username" });
            }
        }
    }

    public class UserMetaData
    {
        [Required(ErrorMessage = "Required")]
        [StringLength(100, MinimumLength = 3, ErrorMessage = "The first name must be a string of at least 3 characters and at most 100 characters")]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }

        [Required(ErrorMessage = "Required")]
        [StringLength(100, MinimumLength = 3, ErrorMessage = "The last name must be a string of at least 3 characters and at most 100 characters")]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }

        [Required(ErrorMessage = "Required")]
        [StringLength(100, ErrorMessage = "The e-mail can't have more than 100 characters")]
        [RegularExpression(@"^\w+([-+.]*[\w-]+)*@(\w+([-.]?\w+)){1,}\.\w{2,4}$", ErrorMessage = "Invalid e-mail")]
        [Display(Name = "E-mail")]
        public string Email { get; set; }

        [Required(ErrorMessage = "Required")]
        [StringLength(50, MinimumLength = 3, ErrorMessage = "The username must be a string of at least 3 characters and at most 50 characters")]
        public string Username { get; set; }

        [Required(ErrorMessage = "Required")]
        [StringLength(50, MinimumLength = 3, ErrorMessage = "The password must be a string of at least 3 characters and at most 50 characters")]
        public string Password { get; set; }

        [Required(ErrorMessage = "Required")]
        [Range(1, 2, ErrorMessage = "Required")]
        [Display(Name = "User role")]
        public int UserRoleId { get; set; }
    }
}

4. Add a view for adding and modifying an user Go top

For adding and editing an user we will add action methods inside the UsersController and a view for defining the user interface.

[HttpGet]
public ActionResult Add()
{
	ViewBag.IsNewUser = true;
	return View("User", new User {UrlReferrer = Request.UrlReferrer == null ? string.Empty : Request.UrlReferrer.AbsoluteUri});
}

[HttpPost]
public ActionResult Add(User model)
{
	ViewBag.IsNewUser = true;
	return Save(model);
}

[HttpGet]
public ActionResult Edit(int id = 0)
{
	ViewBag.IsNewUser = false;
	var user = new UnitOfWork().GetUserRepository.GetById(id);
	if (user == null)
	{
		ViewBag.Error = "User not found";
		user = new User();
	}
	user.UrlReferrer = Request.UrlReferrer == null ? string.Empty : Request.UrlReferrer.AbsoluteUri;
	
	return View("User", user);
}

[HttpPost]
public ActionResult Edit(User model)
{
	ViewBag.IsNewUser = false;
	return Save(model);
}

protected ActionResult Save(User model)
{
	if(ModelState.IsValid)
	{
		try
		{
			model.Save();
			ViewBag.Message = "The user was saved successfully";
		}
		catch (Exception exp)
		{                    
			ViewBag.Error = "There was an unexpected error while saving the user.";
		}
	}
	return View("User", model);
}

The last thing needed is a view that supports both cases: add new and edit user.

@model HelloWorld.Code.DataAccess.User
@{
    ViewBag.Title = ViewBag.IsNewUser ? "Add a new user" : "Edit user";
}

<h2>@ViewBag.Title</h2>
<hr/>
@if (ViewBag.Error != null && !string.IsNullOrEmpty(ViewBag.Error))
{
    <p style="color: red">@ViewBag.Error</p>
}
else if (ViewBag.Message != null && !string.IsNullOrEmpty(ViewBag.Message))
{
    <p style="color: green">@ViewBag.Message</p>
}
else
{            
    using (Html.BeginForm(ViewBag.IsNewUser ? "Add" : "Edit", "Users", FormMethod.Post))
    {       
        @Html.HiddenFor(model => model.UserId)
        @Html.HiddenFor(model => model.UrlReferrer)        
    
        @Html.LabelFor(model => model.FirstName, new { @class = "control-label" })
        @Html.TextBoxFor(model => model.FirstName)
        @Html.ValidationMessageFor(model => model.FirstName)
        <br/>
        @Html.LabelFor(model => model.LastName, new { @class = "control-label" })
        @Html.TextBoxFor(model => model.LastName)
        @Html.ValidationMessageFor(model => model.LastName)
        <br/>
        @Html.LabelFor(model => model.Email, new { @class = "control-label" })
        @Html.TextBoxFor(model => model.Email)
        @Html.ValidationMessageFor(model => model.Email)
        <br/>
        @Html.LabelFor(model => model.Username, new { @class = "control-label" })
        @Html.TextBoxFor(model => model.Username)
        @Html.ValidationMessageFor(model => model.Username)
        <br/>
        @Html.LabelFor(model => model.Password, new { @class = "control-label" })
        @Html.TextBoxFor(model => model.Password)
        @Html.ValidationMessageFor(model => model.Password)
        <br/>
        @Html.LabelFor(model => model.UserRoleId, new { @class = "control-label" })
        @Html.DropDownListFor(model => model.UserRoleId, Model.UserRolesList)
        @Html.ValidationMessageFor(model => model.UserRoleId)
        <br/>
        <input type="submit" name="Save" value="Save" />
    }
}
<br/>
@if (!string.IsNullOrEmpty(Model.UrlReferrer))
{
    <a href="@Model.UrlReferrer">Go back</a>
}

5. Implement the delete feature Go top

For deleting the user we will modify the index action method associated to the post HTTP method so that it accepts an optional parameter “hfDeleteId ” that, when provided, will contain the id of the user to delete.

[HttpGet]
public ActionResult Index(UsersList model, int page = 1, string sort = "", string direction = "")
{
	model.SetParameters(page, sort, direction, Request.Params);
	return View(model);
}

[HttpPost]
public ActionResult Index(UsersList model, int page = 1, string sort = "", string direction = "", int hfDeleteId = 0)
{           
	if (hfDeleteId > 0)
	{
		#region Delete

		using (var unitOfWork = new UnitOfWork())
		{
			var repository = unitOfWork.GetUserRepository;
			var user = repository.GetById(hfDeleteId);
			if (user != null)
			{
				try
				{
					unitOfWork.GetUserRepository.Delete(user);
					unitOfWork.Save();
					ViewBag.DeleteMessage = "The user was deleted";
					ViewBag.Deleted = true;
				}
				catch
				{
					ViewBag.DeleteMessage = "An unexpected error occured";
					ViewBag.Deleted = false;
				}
			}
			else
			{
				ViewBag.DeleteMessage = "The user to delete wasn't found";
				ViewBag.Deleted = false;
			}
		}

		#endregion

		model.SetParameters(page, sort, direction, Request.Params);
		return View(model);
	}
	
	//We do this redirect in order to preserve the search parameters in the URL after the user presses the search button.
	model.SetParameters(page, sort, direction, Request.Params);
	return RedirectToAction("Index", (new UrlHelper(ControllerContext.RequestContext)).GetRouteValueDictionaryForList(model));
}

The view for the users list was also changed to render two links for each row allowing us to edit and/or delete that user.

@using System.ComponentModel
@using HelloWorld.Code.Util
@model HelloWorld.Models.UsersList
@{
    ViewBag.Title = "Users list";
}
@using (Html.BeginForm("Index", "Users", FormMethod.Post, new {id = "frmSearch"}))
{            
    @Html.LabelFor(model => model.FirstName) 
    @Html.TextBoxFor(model => model.FirstName)
    <br/> 
    @Html.LabelFor(model => model.LastName) 
    @Html.TextBoxFor(model => model.LastName)   
    <input type="submit" name="Search" value="Search"/>   
}
@using (Html.BeginForm("Index", "Users", FormMethod.Post, new {id = "frmDelete"}))
{            
    <input type="hidden" id="hfDeleteId" name="hfDeleteId"/>
    @Html.HiddenFor(model => model.FirstName)
    @Html.HiddenFor(model => model.LastName)                                             
}
@if (!string.IsNullOrEmpty(ViewBag.DeleteMessage))
{
    <p style="color: @(ViewBag.Deleted ? "green" : "red")">@ViewBag.DeleteMessage</p>
}
@if (!Model.PagedList.CurrentPage.Any())
{
    <p>No users were found</p>
}
else
{
    <table>
        <thead>
            <tr>
                <th>@Html.ActionLink("First name", "Index", "Users",
                                    Url.GetRouteValueDictionaryForList(
                                        Model,
                                        sortColumn: "FirstName",
                                        direction: string.Compare(Model.SortColumn, "FirstName",
                                        StringComparison.CurrentCultureIgnoreCase) != 0 ? "asc" : Model.SortDirection == ListSortDirection.Ascending ? "desc" : "asc"), null)</th>
                <th>@Html.ActionLink("Last name", "Index", "Users",
                                    Url.GetRouteValueDictionaryForList(
                                        Model,
                                        sortColumn: "LastName",
                                        direction: string.Compare(Model.SortColumn, "LastName", StringComparison.CurrentCultureIgnoreCase) != 0 ? "asc" : Model.SortDirection == ListSortDirection.Ascending ? "desc" : "asc"), null)</th>
                <th>E-mail</th>
                <th>Role</th>
                <th>Actions</th>
            </tr>
        </thead>
        @foreach (var user in Model.PagedList.CurrentPage)
        {
            <tr>
                <td>@user.FirstName</td>
                <td>@user.LastName</td>
                <td>@user.Email</td>
                <td>@user.UserRole.UserRoleName</td>
                <td>
                    @Html.ActionLink("Edit", "Edit", "Users", new RouteValueDictionary { { "id", @user.UserId } }, null) | 
                    <a href="javascript:;" class="delete" data-id="@user.UserId">Delete</a>                   
                </td>
            </tr>
        } 
    </table>
@Html.PagedListPager(Url, new PagerHtmlRenderer(
                                                currentPageNumber: Model.PagedList.CurrentPageNumber,
                                                pageSize: Model.PagedList.PageSize,
                                                totalNumberOfItems: Model.PagedList.TotalNumberOfItems,
                                                actionName: "Index",
                                                controllerName: "Users",
                                                routeValues: Url.GetRouteValueDictionaryForList(Model),
                                                pageRouteValueName: "page"))
    
}
<br />
<br/>
@Html.ActionLink("Add a new user", "Add", "Users")

@section scripts
{
    <script type="text/javascript">
        $().ready(function () {
            $('.delete').click(function (event) {
                event.preventDefault();
                $("#hfDeleteId").val($(this).attr("data-id"));
                document.forms["frmDelete"].submit();
            });
        });
    </script>    
}

Note that we restricted the deletion only for the HTML POST method, using the [HttpPost] attribute in the controller. Although it is possible to delete an item using a GET method this is not recommended since it might generate a security hole. For example let’s suppose our delete link would not execute any JavaScript to post the form but would only point to an URL that contains the id of the user to delete. Something like this:

<a href="http://localhost:12317/Users/Index?delete=10">Delete</a>   

And let’s also suppose that the controller action method that handles the HTTP GET checks if a delete parameter is passed in the query string and, if so, it deletes the user with that id. Now, when spiders/bots/crawlers will parse our page and try to follow the links they will trigger a delete. So when the site will be online and the Googlebot will try to index our pages it will follow the links it finds and automatically delete our data.

Even if you restrict that link only for authenticated users you will still have a security hole. For example, a user that is logged in could receive an malicious e-mail that contains an image that has the src attribute set to this URL:

<img src="http://localhost:12317/Users/Index?delete=10" />

If the user will allow images from his e-mail client then a delete will happen without the user being aware of that.

As a general rule, remember to use:

  • GET – for safe operations that only retrieve data without changing the state of the resource on the server,
  • POST – for operations that change the state of the resource,

Here is an funny post about Why learning HTTP does matter that summaries very nicely the unwanted behavior we might create using the wrong HTTP methods.

6. Summary Go top

This post is part of a series of demos about starting to work with ASP.NET MVC 4. Until now we have created a simple list that supports paging, sorting and filtering, and we implemented the add, modify and delete list item features as described in this post. You can download the complete source code that includes both the list and the edit from Codeplex.

Paging, sorting and filtering a list in ASP.NET MVC 4

Contents:

  1. Introduction
  2. Creating a list
  3. The repository pattern
  4. The unit of work pattern
  5. Adding a pager to the list
  6. Allow sorting on the list
  7. Add list filters
  8. Create a base view model for lists that support sorting, filtering and paging
  9. Summary

1. Introduction Go top

Starting from the solution created in the previous post Data validation, we will build from scratch a new page that displays a list of users and gives the possibility to filter, page and sort the list. The final result will look like this:

MVC Course 3 - Final result

We will be building the examples in this post over this solution, so we will have some predefined CSS styles already in place but you can start as well from a completely new solution.

2. Creating a list Go top

DB structure
Let’s start with the necessary DB tables: [dbo].[Users] and [dbo].[UserRoles]. Below is the SQL script for creating and populating these two tables with some default data.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserRoles](
	[UserRoleId] [int] NOT NULL,
	[UserRoleName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
	[UserRoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[UserRoles]([UserRoleId],[UserRoleName]) VALUES (1,'Administrator')
INSERT INTO [dbo].[UserRoles]([UserRoleId],[UserRoleName]) VALUES (2,'User')
GO

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](100) NOT NULL,
	[LastName] [varchar](100) NOT NULL,
	[Email] [varchar](100) NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[UserRoleId] [int] NOT NULL,
	[Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IDX_Username] ON [dbo].[Users]
(
	[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_UserRoles] FOREIGN KEY([UserRoleId])
REFERENCES [dbo].[UserRoles] ([UserRoleId])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_UserRoles]
GO

INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('John','Dow','administrator@mailinator.com','admin','password',1,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Bela','Lugosi','bela.lugosi@mailinator.com','bela.lugosi','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('David','Copperfield','david.copperfield@mailinator.com','david.copperfield','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Jennifer','Jones','jennifer.jones@mailinator.com','jennifer.jones','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Sophia Loren','Loren','sophia.loren@mailinator.com','sophia.loren','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Woody','Allen','woody.allen@mailinator.com','woody.allen','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Stanley','Donwood','stanley.donwood@mailinator.com','stanley.donwood','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Marilyn','Monroe','marilyn.monroe@mailinator.com','marilyn.monroe','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Jamie','Foxx','jamie.foxx@mailinator.com','jamie.foxx','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('John','Ford','john.ford@mailinator.com','john.ford','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Diane','Keaton','diane.keaton@mailinator.com','diane.keaton','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Angelina','Jolie','angelina.jolie@mailinator.com','angelina.jolie','password',2,0)
GO
INSERT INTO [dbo].[Users] ([FirstName],[LastName],[Email],[Username],[Password],[UserRoleId],[Deleted])
VALUES ('Brad','Pitt','brad.pitt@mailinator.com','brad.pitt','password',2,0)
GO

Next we will update the edmx model to include users and user roles and then we shall add a new controller called UsersController. By default our controller will have an action method called Index. Let’s add a view for the Index method and display the list of users that aren’t deleted. First we will fetch the list from the DB inside the controller and pass it to the view.

Note: For the moment, I will concentrate mainly on the user interface and I will put all the server side logic intro the controller but lately we will refactor our application’s architecture using a view model and applying the repository and unit of work patterns.

public ActionResult Index()
{
	using (var context = new MvcDemoEntities())
	{
		var list = context.Users.Include(u => u.UserRole).Where(u => !u.Deleted).ToList();
		return View(list);
	}
}

In the view we will iterate through the collection of users and render one row for each user:

@model IEnumerable
@{
    ViewBag.Title = "Users list";
}
@if (!Model.Any())
{
    <div>No users were found.</div>
}
else
{
    <table>
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>E-mail</th>
                <th>Role</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var user in Model) {
                <tr>
                    <td>@user.FirstName</td>
                    <td>@user.LastName</td>
                    <td>@user.Email</td>
                    <td>@user.UserRole.UserRoleName</td>
                </tr>
            }
        </tbody>
    </table>
}

If you run now the site, you will see a table with all the users in the DB.

3. The repository pattern Go top

As defined by Martin Fowler:

A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer. Repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers.

So, the repository pattern is used to create an abstraction between the business layer and the data access layer so that the business layer doesn’t have to know anything about the underlying data access techniques be it static files, external services, ADO.NET, Entity Framework or NHibernate. The main advantages that come from this abstraction are:

  • Helps to separate the application layers providing a simplified model for obtaining/persisting data.
  • Makes it easier to implement unit testing because it provides an easy way of defining test repositories that create in-memory dummy collections and use them for testing instead of accessing the real data source.

You can find a complete explanation of the Repository Pattern on MSDN.

Below is an interesting recommendation taken from the book “Domain-Driven Design: Tackling Complexity in the Heart of Software” that I added here because I think it’s a good recommendation:

Leave transaction control to the client. Although the REPOSITORY will insert into and delete from the database, it will ordinarily not commit anything. It is tempting to commit after saving, for example, but the client presumably has the context to correctly initiate and commit units of work. Transaction management will be simpler if the REPOSITORY keeps its hands off.

After this brief introduction, let’s use the repository pattern in our example. For the moment, in order to keep things simple, we will define only a data retrieval method and later, in the next post, we will add data persistence methods as we will define the add/edit/delete user functionality.

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace HelloWorld.Code.DataAccess
{
    /// <summary>
    /// Base repository class
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IRepository where T : class
    {
        /// <summary>
        /// Return a list.
        /// </summary>
        /// <param name="filters">A lambda expression used to filter the result.</param>
        /// <param name="sorting">The sort expression, example: ColumnName desc</param>
        /// <param name="includeList">The list of related entities to load.</param>
        /// <returns></returns>
        IList Search(Expression<Func<T, bool>> filters, string sorting, List includeList);
    }
}

Before building the basic repository class, we will add the System.Linq.Dynamic library using NuGet. We need it because we will create dynamic Linq queries inside our repositiries starting from the input parameters. Below is the code for the default repository class:

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Linq.Dynamic;

namespace HelloWorld.Code.DataAccess
{
    public class Repository<T> : IRepository<T> where T : class
    {
        internal MvcDemoEntities Context;
        public Repository(MvcDemoEntities context)
        {
            Context = context;
        }

        public IList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList)
        {
            using (var context = new MvcDemoEntities())
            {
                return GetQuery(context, filters, sorting, includeList).ToList();
            }
        }

        /// <summary>
        /// Method used to build the query that will reflect the filter conditions and the sort expression.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="filters"></param>
        /// <param name="sorting"></param>
        /// <param name="includeList"></param>
        /// <returns></returns>
        protected IQueryable<T> GetQuery(ObjectContext context, Expression<Func<T, bool>> filters, string sorting, List<string> includeList)
        {
            var objectSet = context.CreateObjectSet<T>();
            if (string.IsNullOrEmpty(sorting))
                sorting = objectSet.EntitySet.ElementType.KeyMembers.First().Name; //consider the PK as the default sort column

            var objectQuery = (ObjectQuery<T>)objectSet;
            if (includeList != null)
            {
                foreach (var include in includeList)
                {
                    objectQuery = objectQuery.Include(include);
                }
            }

            var query = objectQuery.Where(filters == null ? t => 1 == 1 : filters);
            query = query.OrderBy(sorting);
            return query;
        }
    }
}

Now, we will change also the controller method to use the repository.

public ActionResult Index()
{
	using (var context = new MvcDemoEntities())
	{
		var repository = new Repository<User>(context);
		var list = repository.Search(u => u.Deleted == false, null, new List{ "UserRole"});
		return View(list);
	}
}

The down-side of this implementation is that the controller has to provide a context, meaning that it’s still tightly coupled with the DAL. You can say that we could move the creation of the context inside the repository. This is true but contradicts with the recommendation mentioned just a little before: “Leave transaction control to the client”. It’s true that for the moment we are only retrieving data, so we are not concerned about transactions, but soon we will need to add in our repository Add,Update and Delete methods. In order to remove this problem we will use the Unit of Work pattern.

4. The unit of work pattern Go top

As defined by Martin Fowler:

Maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems. […] A Unit of Work keeps track of everything you do during a business transaction that can affect the database. When you’re done, it figures out everything that needs to be done to alter the database as a result of your work.

And, as usual, some more references to MSDN for further reading:

Let’s modify our code to include also this pattern. The code for the UnitOfWork class is:

using System;

namespace HelloWorld.Code.DataAccess
{
    public class UnitOfWork : IDisposable
    {
        #region Repositories

        protected IRepository<User> UserRepository;
        public IRepository<User> GetUserRepository
        {
            get { return UserRepository ?? (UserRepository = new Repository<User>(Context)); }
        }

        #endregion

        #region UnitOfWork

        protected MvcDemoEntities Context;
        private bool _disposed;

        public UnitOfWork()
        {
            Context = new MvcDemoEntities();
            _disposed = false;
        }

        public void Save()
        {
            Context.SaveChanges();
        }

        public void Dispose()
        {
            if(!_disposed)
            {
                Context.Dispose();
                _disposed = true;
            }

            GC.SuppressFinalize(this);
        }

        #endregion
    }
}

The controller Index method will use the UnitOfWork class instead of crating a context and passing it to the repository constructor:

public ActionResult Index()
{
	var unitOfWork = new UnitOfWork();
	var repository = unitOfWork.GetUserRepository;
	var list = repository.Search(u => u.Deleted == false, null, new List { "UserRole" });
	return View(list);
}

5. Adding a pager to the list Go top

List with paging
In order to do this we have to:

  1. Add a new method to the repository for returning data in a paged manner.
  2. Add a pager in the View so the user can browse between the pages.

Because each of these topics it’s complex enough to be treated independently, I wrote two other posts to serve as a helping guide. First, let’s apply the instructions described in: Retrieve paged lists from DAL. Then we will integrate in our solution the pager described in: ASP.NET MVC – Pager HtmlHelper extention. In the end our controller Index method looks like this:

public ActionResult Index(int page = 1)
{
	var unitOfWork = new UnitOfWork();
	var repository = unitOfWork.GetUserRepository;
	var pagedList = repository.Search(u => !u.Deleted, null, new List { "UserRole" }, page, 5);

	return View(pagedList);
}

The view was changed to include the pager:

@using HelloWorld.Code.Util
@model HelloWorld.Code.DataAccess.Paging.IPagedList
@{
    ViewBag.Title = "Users list";
}
@if (!Model.CurrentPage.Any())
{
    <div>No users were found.</div>
}
else
{
    <table>
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>E-mail</th>
                <th>Role</th>
            </tr>
        </thead>
        <tbody>
        @foreach (var user in Model.CurrentPage) {
            <tr>
                <td>@user.FirstName</td>
                <td>@user.LastName</td>
                <td>@user.Email</td>
                <td>@user.UserRole.UserRoleName</td>
            </tr>
        }
        </tbody>
    </table>
    @Html.PagedListPager(Url, new PagerHtmlRenderer(
                currentPageNumber: Model.CurrentPageNumber,
                pageSize: Model.PageSize,
                totalNumberOfItems: Model.TotalNumberOfItems,
                actionName: "Index",
                controllerName: "Users",
                routeValues: new RouteValueDictionary() { { "page", "" } },
                pageRouteValueName: "page"))
}

6. Allow sorting on the list Go top

We want to allow the user to sort the list by first name and last name. To enable this we first modify the controller action to receive two new parameters, the sort column name and the sort direction.

public ActionResult Index(int page = 1, string sort = "", string direction = "asc")
{
	var unitOfWork = new UnitOfWork();
	var repository = unitOfWork.GetUserRepository;
	var pagedList = repository.Search(u => !u.Deleted, string.IsNullOrEmpty(sort) ? null : string.Format("{0} {1}", sort, direction), new List { "UserRole" }, page, 5);

	ViewBag.Sort = sort;
	ViewBag.Direction = direction;

	return View(pagedList);
}

We pass the sorting information to the repository search method and also to the view: ViewBag.Sort, ViewBag.Direction. For the columns that allow sorting, the view will display links in the header. Each link will indicate the column name and the sort direction to apply (ascending for the first click and descending for the second). So, in the view, we will replace the table headers for ‘First Name’ and ‘Last Name’ with:

@Html.ActionLink("First Name", "Index", "Users",
	new RouteValueDictionary
		 {
				{ "page", Model.CurrentPageNumber },
				{ "sort", "FirstName" },
				{ "direction", ViewBag.Sort == "FirstName" ? (ViewBag.Direction == "asc" ? "desc" : "asc") : "asc" }
		 }, null)@Html.ActionLink("Last Name", "Index", "Users",
	new RouteValueDictionary
		{
			{ "page", Model.CurrentPageNumber },
			{ "sort", "LastName" },
			{ "direction", ViewBag.Sort == "LastName" ? (ViewBag.Direction == "asc" ? "desc" : "asc") : "asc" }
		}, null)

7. Add list filters Go top

In this step we will add a text box and a search button to allow the user to filter the list by name and/or surname. These controls will be placed in the view inside a form so we are able to post the data to the server when the user click the submit button.

@using (Html.BeginForm("Index", "Users", FormMethod.Post))
{
    @:Filter by name: <input type="text" name="filter" />
    <input type="submit" name="search" value="Search" />
}

In the controller we will get the value and apply the filter:

public ActionResult Index(int page = 1, string sort = "", string direction = "asc", string filter = "")
{
	var unitOfWork = new UnitOfWork();
	var repository = unitOfWork.GetUserRepository;
	var pagedList =
		repository.Search(
			u =>
			!u.Deleted &&
			(string.IsNullOrEmpty(filter) || u.FirstName.Contains(filter) || u.LastName.Contains(filter)),
			string.IsNullOrEmpty(sort) ? null : string.Format("{0} {1}", sort, direction),
			new List {"UserRole"}, page, 5);

	ViewBag.Sort = sort;
	ViewBag.Direction = direction;
	ViewBag.Filter = filter;

	return View(pagedList);
}

Now, we are able to filter the page but, if we filter the page and then we sort or change the page number the filter will be lost. To solve this we need to remember the filter value in our page number and sort column links so we will pass the filter value in the RouteValueDictionary for both sorting and paging.

8. Create a base view model for lists that support sorting, filtering and paging Go top

Displaying lists that allow sorting, filtering and paging is a common and repetitive task inside applications so, the next thing to do, is to create a basic view model that will allow us to reuse as much code as possible. The generic model should allow us to set:

  • the repository
  • the page size and the current page number
  • the sort expression

Then we will use this view model as the base class for our lists and we will define, inside the custom list classes, the filters to apply. We want the filters to be easy to define, so we will create an attribute that, when applied to a property will add filtering functionality to it. The main classes and their roles will be:

  • ListViewModel – provides basic paging, sorting and filtering,
  • ListViewFilterAttribute – the ListViewModel will get all the properties marked with the ListViewFilterAttribute and create ListViewFilter objects,
  • ListViewFilter – will be used to understand:
    • what’s the filter query string parameter name in the URL (in our example we will keep the filter values in the URL),
    • what’s the filter property name,
    • the value of the filter.

I will explain the code starting from how the base class will be used. Here is how the user list model will look like:

public class UsersList : ListViewModel<User>
{
	#region Filters

	[DisplayName("First name")]
	[ListViewFilter("fn")]
	public string FirstName { get; set; }

	[DisplayName("Last name")]
	[ListViewFilter("ln")]
	public string LastName { get; set; }

	public override Expression<Func<User, bool>> FilterCondition
	{
		get
		{
			return
				(u =>
				 (string.IsNullOrEmpty(FirstName) || u.FirstName.Contains(FirstName.Trim())) &&
				 (string.IsNullOrEmpty(LastName) || u.LastName.Contains(LastName.Trim())));
		}
		set { }
	}

	#endregion

	public UsersList()
	{       
		var unitOfWork = new UnitOfWork();
		Repository = unitOfWork.GetUserRepository;            
		IncludeList.Add("UserRole");
		PageSize = 5;
	}
}

In the constructor we set a default repository, the related entityto load (UserRole) and the default page size. Of course this values could be set from outside the class but the constructor it’s a convenient place for default values. Apart from this, we have two filters defined in the form of properties decorated with the attribute [ListViewFilter(“FilterUrlParameterName”)]. The filter sets the name of the query string parameter where to store the filter value between post-backs. Just to give a well known example, this is more or less the same idea as in a Google search. Below is the code for the attribute:

[System.AttributeUsage(System.AttributeTargets.Property)]
public class ListViewFilterAttribute : System.Attribute
{
	public string UlrParameterName { get; set; }

	public ListViewFilterAttribute(string ulrParameterName)
	{
		UlrParameterName = ulrParameterName;
	}
}

Using this attribute we will be able to populate (using reflection), a collection of ListViewFilter objects:

public class ListViewFilter
{
	public string PropertyName { get; set; }

	public string UrlFilterName { get; set; }

	public object Value { get; set; }
}

And the main class that actually supports paging, sorting and filtering is:

public class ListViewModel<T> : IListViewModel where T : class
{
	#region Properties

	/// <summary>
	/// The number of elements to display in the list. Default is 10.
	/// </summary>
	public int PageSize
	{
		get { return _pageSize < 1 ? 10 : _pageSize; }
		set { _pageSize = value; }
	}
	private int _pageSize;

	/// <summary>
	/// The current page to be displayed. Default is 1.
	/// </summary>
	public int CurrentPageNumber
	{
		get { return _currentPageNumber < 1 ? 1 : _currentPageNumber; }
		set { _currentPageNumber = value; }
	}
	private int _currentPageNumber;

	/// <summary>
	/// The column on which the sorting is applied.
	/// </summary>
	public string SortColumn { get; set; }

	/// <summary>
	/// The direction of the sorting. Default is ascending.
	/// </summary>
	public ListSortDirection SortDirection
	{
		get { return _sortingDirection; }
		set { _sortingDirection = value; }
	}
	private ListSortDirection _sortingDirection = ListSortDirection.Ascending;

	/// <summary>
	/// Get the current sort expression.
	/// </summary>
	public string SortExpression
	{
		get
		{
			return string.IsNullOrEmpty(SortColumn) ? string.Empty
					   : string.Format("{0} {1}", SortColumn, SortDirection == ListSortDirection.Ascending ? "asc" : "desc");
		}
	}

	/// <summary>
	/// Used to specify the related objects to include in the result.
	/// </summary>
	public List<string> IncludeList { get; private set; }

	/// <summary>
	/// A lambda expression used to filter the list
	/// </summary>
	/// <returns></returns>
	public virtual Expression<Func<T, bool>> FilterCondition { get; set; }

	/// <summary>
	/// The repository used to obtain the data
	/// </summary>
	public IRepository<T> Repository { get; set; } 

	#endregion

	#region Constructor

	public ListViewModel()
	{
		IncludeList = new List<string>();
	}

	#endregion

	#region Fill list & Paged list

	/// <summary>
	/// Returns the elements for the current page. The list is filtered and sorted.
	/// </summary>
	/// <param name="refresh">The refresh parameter is used to force the data retrieval in care the list was already filled.</param>
	/// <returns></returns>
	protected IPagedList<T> GetPagedList(bool refresh)
	{
		if (refresh || _pagedList == null)                           
			 _pagedList = Repository.Search(FilterCondition, SortExpression, IncludeList, CurrentPageNumber, PageSize);                
		
		return _pagedList;
	}

	public IPagedList<T> PagedList
	{
		get { return GetPagedList(false); }
		set { _pagedList = value; }
	}
	private IPagedList<T> _pagedList;

	/// <summary>
	/// Returns all the elements that match the filter condition. The list is sorted.
	/// </summary>
	/// <param name="refresh">The refresh parameter is used to force the data retrieval in care the list was already filled.</param>
	/// <returns></returns>
	protected IList<T> GetList(bool refresh)
	{
		if (refresh || _list == null)            
			_list = Repository.Search(FilterCondition, SortExpression, IncludeList);            

		return List;
	}

	public IList<T> List
	{
		get { return GetList(false); }
		set { _list = value; }
	}
	private IList<T> _list;

	#endregion

	#region Public methods

	/// <summary>
	/// Helper methods used for setting the current page number, the column to sort on and the sorting direction.
	/// </summary>
	/// <param name="currentPageNumber">The current page to be displayed.</param>
	/// <param name="sortColumn">The column on which the sorting is applied.</param>
	/// <param name="sortDirection">The direction of the sorting. Default is ascending.</param>
	/// <param name="requestParameters">Used to populate the filters.</param>
	public void SetParameters(int currentPageNumber, string sortColumn, string sortDirection, NameValueCollection requestParameters)
	{
		CurrentPageNumber = currentPageNumber;
		if (!string.IsNullOrEmpty(sortColumn))
		{
			SortColumn = sortColumn;
			if (string.Compare(sortDirection, "desc", StringComparison.InvariantCultureIgnoreCase) == 0 || string.Compare(sortDirection, "descending", StringComparison.InvariantCultureIgnoreCase) == 0)
				SortDirection = ListSortDirection.Descending;
		}

		//Populate the filter values
		if(requestParameters != null)
		{
			var filters = GetFilters();
			foreach (var filter in filters)
			{
				filter.Value = requestParameters[filter.UrlFilterName] ?? requestParameters[filter.PropertyName];
			}
			SetFilters(filters);
		}
	}

	/// <summary>
	/// Get a list of name and value for all the properties inside this class that are marked with the ListViewFilterAttribute.
	/// </summary>
	/// <returns></returns>
	public List<ListViewFilter> GetFilters()
	{
		return _filters ?? (_filters = (from p in GetType().GetProperties()
										let attr = p.GetCustomAttributes(typeof (ListViewFilterAttribute), true)
										where attr.Length == 1
										select new ListViewFilter
												   {
													   PropertyName = p.Name,
													   UrlFilterName =
														   ((ListViewFilterAttribute) (attr.First())).
														   UlrParameterName,
													   Value = p.GetValue(this, null)
												   }).ToList());
	}
	private List<ListViewFilter> _filters;
	
	/// <summary>
	/// Populate the model filter properties using the filter values.
	/// </summary>
	/// <param name="filters"></param>
	public void SetFilters(List<ListViewFilter> filters)
	{
		var type = GetType();
		foreach (var filter in filters)
		{
			var property = type.GetProperty(filter.PropertyName);
			if(property != null)
				property.SetValue(this, filter.Value, null);
		}
	}

	#endregion
}

Now we will use the UsersList in our controller:

public ActionResult Index(UsersList model, int page = 1, string sort = "", string direction = "")
{
	model.SetParameters(page, sort, direction, Request.Params);
	return View(model);
}

The view is a little modified too:

@using System.ComponentModel
@using HelloWorld.Code.Util
@model HelloWorld.Models.UsersList
@{
    ViewBag.Title = "Users list";
}
@using (Html.BeginForm("Index", "Users", FormMethod.Post))
{            
    @Html.LabelFor(model => model.FirstName) 
    @Html.TextBoxFor(model => model.FirstName)
    <br/> 
    @Html.LabelFor(model => model.LastName) 
    @Html.TextBoxFor(model => model.LastName)   
    <input type="submit" name="Search" value="Search"/>
}
@if (!Model.PagedList.CurrentPage.Any())
{
    <div>No users were found</div>
}
else
{
    <table>
        <thead>
            <tr>
                <th>@Html.ActionLink("First name", "Index", "Users", 
                                    Url.GetRouteValueDictionaryForList(
                                        Model, 
                                        sortColumn: "FirstName", 
                                        direction: string.Compare(Model.SortColumn, "FirstName", 
                                        StringComparison.CurrentCultureIgnoreCase) != 0 ? "asc" : Model.SortDirection == ListSortDirection.Ascending ? "desc" : "asc"), null)</th>
                <th>@Html.ActionLink("Last name", "Index", "Users", 
                                    Url.GetRouteValueDictionaryForList(
                                        Model, 
                                        sortColumn: "LastName", 
                                        direction: string.Compare(Model.SortColumn, "LastName", StringComparison.CurrentCultureIgnoreCase) != 0 ? "asc" : Model.SortDirection == ListSortDirection.Ascending ? "desc" : "asc"), null)</th>
                <th>E-mail</th>
                <th>Role</th>
            </tr>
        </thead>
        @foreach (var user in Model.PagedList.CurrentPage)
        {
            <tr>
                <td>@user.FirstName</td>
                <td>@user.LastName</td>
                <td>@user.Email</td>
                <td>@user.UserRole.UserRoleName</td>
            </tr>
        } 
    </table>
@Html.PagedListPager(Url, new PagerHtmlRenderer(
                                                currentPageNumber: Model.PagedList.CurrentPageNumber,
                                                pageSize: Model.PagedList.PageSize,
                                                totalNumberOfItems: Model.PagedList.TotalNumberOfItems,
                                                actionName: "Index",
                                                controllerName: "Users",
                                                routeValues: Url.GetRouteValueDictionaryForList(Model),
                                                pageRouteValueName: "page"))
    
}

Here you can notice an extension for the UrlHelper class Url.GetRouteValueDictionaryForList that was created to easily build a RouteValueDictionary that is used to create the URLs for paging and sorting so that the filters, the sort criteria and the paging is not lost when the page is refreshed.

public static RouteValueDictionary GetRouteValueDictionaryForList(this UrlHelper urlHelper, IListViewModel model, string sortColumn = "", string direction = "")
{
	var dictionary = new RouteValueDictionary
						 {
							 {"page", model.CurrentPageNumber},
							 {"sort", string.IsNullOrEmpty(sortColumn) ? model.SortColumn : sortColumn},
							 {"direction", string.IsNullOrEmpty(direction) ? (model.SortDirection == ListSortDirection.Ascending ? "asc" : "desc") : direction}                                    
						 };
	foreach (var filter in model.GetFilters())
	{
		dictionary.Add(filter.UrlFilterName, filter.Value);
	}

	return dictionary;
}

9. Summary Go top

In this post we created a list that supports paging, sorting and filtering. In the end we extracted the reusable parts in a generic class that we can use as the base for our lists.

You can download the complete source code from Codeplex.

Retrieve paged lists from DAL

Objective:

Create an easy way to retrieve paged lists from the data access layer when using Entity Framework.

Steps:
  1. Create an interface that defines a paged list: IPagedList<T>.
  2. Create a class PagedList<T> that implements IPagedList<T>.
  3. Create a Linq extension method that can be applied to an Entity Framework query and returns a paged list.
  4. Optional: If you are using the repository pattern you can use IPagedList<T> to return paged lists.
Code 🙂

1. Define an interface for the paged list.

using System.Collections.Generic;

namespace Demo.Paging
{
    public interface IPagedList<T>
    {
        int PageSize { get; }
        int TotalNumberOfItems { get; }
        IList<T> CurrentPage { get; }
        int CurrentPageNumber { get; }
        int TotalNumberOfPages { get; }        
    }
}

2. Implement the class for the paged list.

using System.Collections.Generic;

namespace Demo.Paging
{
    public class PagedList<T> : IPagedList<T>
    {
        public int PageSize { get; private set; }
        public int TotalNumberOfItems { get; private set; }
        public IList<T> CurrentPage { get; private set; }
        public int CurrentPageNumber { get; private set; }

        public int TotalNumberOfPages
        {
            get
            {
                return TotalNumberOfItems == 0
                           ? 1
                           : (TotalNumberOfItems/PageSize + (TotalNumberOfItems%PageSize > 0 ? 1 : 0));
            }
        }

        public PagedList(IList<T> currentPage, int currentPageNumber, int pageSize, int totalNumberOfItems)
        {
            PageSize = pageSize;
            TotalNumberOfItems = totalNumberOfItems;
            CurrentPage = currentPage;
            CurrentPageNumber = currentPageNumber;
        }
    }
}

3. Create a Linq extension method that is able to return paged lists from regular queries.

using System.Linq;

namespace Demo.Paging
{
    public static class PagedListLinqExtentions
    {
        public static PagedList<T> ToPagedList<T>(this IQueryable<T> allItems, int currentPageNumber, int pageSize)
        {
            if (currentPageNumber < 1)
                currentPageNumber = 1;

            var totalNumberOfItems = allItems.Count();
            var totalNumberOfPages = totalNumberOfItems == 0 ? 1 : (totalNumberOfItems / pageSize + (totalNumberOfItems % pageSize > 0 ? 1 : 0));
            if (currentPageNumber > totalNumberOfPages)
                currentPageNumber = totalNumberOfPages;

            var itemsToSkip = (currentPageNumber - 1) * pageSize;
            var pagedItems = allItems.Skip(itemsToSkip).Take(pageSize).ToList();

            return new PagedList<T>(pagedItems, currentPageNumber, pageSize, totalNumberOfItems);
        }
    }
}

4. Example of usage:

protected IPagedList GetPagedList()
{
	using (var context = new DemoEntities())
	{
		return context.TestTable.Where(c =&gt; condition).OrderBy(t =&gt; t.TableColumn).ToPagedList(1, 10); 
	}
}

Note:
The extension method can be applied only on queries that are sorted, because of an Entity Framework restriction. Otherwise we will receive the error:

“The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.”

5. If you are using the repository pattern and you need to return a paged list, below is a handy example that will provide a default column to sort by in case no ordering is supplied. In this way you no longer have to worry about the error: The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.

Note: We are using System.Linq.Dynamic to create dynamic Linq queries. You can use NuGet to download it.

Base repository interface:

using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using Demo.Paging;

namespace Demo.DataAccess
{
    /// <summary>
    /// Base repository class
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IRepository<T> where T:class
    {
        /// <summary>
        /// Return a paged list.
        /// </summary>
        /// <param name="filters">A lambda expression used to filter the result.</param>
        /// <param name="sorting">The sort expression, example: ColumnName desc</param>
        /// <param name="includeList">The list of related entities to load.</param>
        /// <param name="currentPageNumber">The page number to return.</param>
        /// <param name="pageSize">The page size (the maximum number of elements to return in the list).</param>
        /// <returns></returns>
        IPagedList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList, int currentPageNumber, int pageSize);

        /// <summary>
        /// Return a list.
        /// </summary>
        /// <param name="filters">A lambda expression used to filter the result.</param>
        /// <param name="sorting">The sort expression, example: ColumnName desc</param>
        /// <param name="includeList">The list of related entities to load.</param>
        /// <returns></returns>
        IList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList);
    }
}

Base repository class:

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Linq.Dynamic;
using using Demo.Paging;

namespace Demo.DataAccess
{
    /// <summary>
    /// Base repository class
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Repository<T> : IRepository<T> where T : class
    {
        /// <summary>
        /// Return a paged list.
        /// </summary>
        /// <param name="filters">A lambda expression used to filter the result.</param>
        /// <param name="sorting">The sort expression, example: ColumnName desc</param>
        /// <param name="includeList">The list of related entities to load.</param>
        /// <param name="currentPageNumber">The page number to return.</param>
        /// <param name="pageSize">The page size (the maximum number of elements to return in the list).</param>
        /// <returns></returns>
        public IPagedList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList, int currentPageNumber, int pageSize)
        {
            using (var context = new MvcDemoEntities())
            {
                return GetQuery(context, filters, sorting, includeList).ToPagedList(currentPageNumber, pageSize);
            }
        }

        /// <summary>
        /// Return a list.
        /// </summary>
        /// <param name="filters">A lambda expression used to filter the result.</param>
        /// <param name="sorting">The sort expression, example: ColumnName desc</param>
        /// <param name="includeList">The list of related entities to load.</param>
        /// <returns></returns>
        public IList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList)
        {
            using (var context = new MvcDemoEntities())
            {
                return GetQuery(context, filters, sorting, includeList).ToList();
            }
        }

        /// <summary>
        /// Method used to build the query that will reflect the filter conditions and the sort expression.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="filters"></param>
        /// <param name="sorting"></param>
        /// <param name="includeList"></param>
        /// <returns></returns>
        protected IQueryable<T> GetQuery(ObjectContext context, Expression<Func<T, bool>> filters, string sorting, List<string> includeList)
        {
            var objectSet = context.CreateObjectSet<T>();
            if (string.IsNullOrEmpty(sorting))
                sorting =  objectSet.EntitySet.ElementType.KeyMembers.First().Name; //consider the PK as the default sort column

            var objectQuery = (ObjectQuery<T>) objectSet;
            if (includeList != null)
            {
                foreach (var include in includeList)
                {
                    objectQuery = objectQuery.Include(include);
                }
            }

            var query = objectQuery.Where(filters == null ? t => 1 == 1 : filters);
            query = query.OrderBy(sorting);
            return query;
        }
    }
}

Repository usage example:

var userRepository = new Repository<User>();
var pagedList = userRepository.Search(u => u.FirstName.Contains("a"), "FirstName asc", new List<string>{"UserRole"}, 1, 5);

ASP.NET MVC – Pager HtmlHelper extention

For my ASP.NET MVC 4 projects I created a HtmlHelper extension that renders a Google-like pager based on:

  • the current page number,
  • the page size and
  • the total number of elements in the list.

HtmlHelper - PagerThe pages will be generated as links and you can set the action to take when the user clicks on a page in two ways:

  1. define a custom JavaScript function to handle the click, this function will receive as an input parameter the requested page number, or
  2. define the URL of the link by specifying:
    • the name of the action method,
    • the name of the controller,
    • a RouteValueDictionary and
    • the key in the RouteValueDictionary where to put the requested page number.

The code for the HtmlHelper extention is:

public static class HtmlHelperExtentions
{
	/// <summary>
	/// Generate the HTML for a pager.
	/// </summary>
	/// <param name="helper"></param>
	/// <param name="urlHelper">The current Url helper associated to the View. Used for generating the URLs in the page links.</param>
	/// <param name="pagerOptions">An object that defines the pager properties.</param>
	/// <returns></returns>
	public static MvcHtmlString PagedListPager(this HtmlHelper helper, UrlHelper urlHelper, PagerHtmlRenderer pagerOptions)
	{
		return new MvcHtmlString(pagerOptions.GeneratePagerHtml(urlHelper));
	}
}

The class that defines the pager properties and renders the links is PagerHtmlRenderer.

using System.Globalization;
using System.Text;
using System.Web.Mvc;
using System.Web.Routing;

namespace HelloWorld.Code.Util.Paging
{
    /// <summary>
    /// Helper class used for rendering the pager for a list in HTML.
    /// </summary>
    public class PagerHtmlRenderer
    {
        #region Properties

        /// <summary>
        /// The name of the action method.
        /// </summary>
        public string ActionName { get; set; }

        /// <summary>
        /// The name of the controller.
        /// </summary>
        public string ControllerName { get; set; }

        /// <summary>
        /// An object that contains the parameters for a route.
        /// </summary>
        public RouteValueDictionary RouteValues { get; set; }

        /// <summary>
        /// The key in the RouteValues dictionary where to put the page number.
        /// </summary>
        public string PageRouteValueName { get; set; }

        /// <summary>
        /// The current page number. By default it is 1 (the first page).
        /// </summary>
        public int CurrentPageNumber { get; set; }

        /// <summary>
        /// The maximum number of elements in a page. If less then 1 we will consider 10 by default.
        /// </summary>
        public int PageSize
        {
            get { return _pageSize < 1 ? 10 : _pageSize; }
            set { _pageSize = value; }
        }
        private int _pageSize;

        /// <summary>
        /// The total number of elements in the list. 
        /// </summary>
        public int TotalNumberOfItems { get; set; }

        /// <summary>
        /// This will create a google-like pager that will display a limited number of page links. Deafult it is 10.
        /// </summary>
        public int MaximumNumberOfPageLinksToRender
        {
            get { return _maximumNumberOfPageLinksToRender < 1 ? 10 : _maximumNumberOfPageLinksToRender; }
            set { _maximumNumberOfPageLinksToRender = value; }
        }
        private int _maximumNumberOfPageLinksToRender;
        
        /// <summary>
        /// The HTML string to use as a container for all the pages. Example: <ul>{0}</ul> where {0} will be replaced with the page elements. Default is "{0}".
        /// </summary>
        public string ContainerHtmlFormatString
        {
            get { return string.IsNullOrEmpty(_containerHtmlFormatString) ? "{0}" : _containerHtmlFormatString; }
            set { _containerHtmlFormatString = value; }
        }
        private string _containerHtmlFormatString;
        
        /// <summary>
        /// The HTML string to use as a container for a page link. Example: <li>{0}</li> where {0} will be replaced with the page link. Default is "{0}".
        /// </summary>
        public string PageNumberHtmlFormatString
        {
            get { return string.IsNullOrEmpty(_pageNumberHtmlFormatString) ? "{0}" : _pageNumberHtmlFormatString; }
            set { _pageNumberHtmlFormatString = value; }
        }
        private string _pageNumberHtmlFormatString;
        
        /// <summary>
        /// The HTML string to use as a container for the current page number. Example: <strong><li>{0}</li></strong> where {0} will be replaced with the current page number. Default is <strong>{0}</strong>.
        /// </summary>
        public string CurrentPageNumberHtmlFormatString
        {
            get { return string.IsNullOrEmpty(_currentPageNumberHtmlFormatString) ? "<strong>{0}</strong>" : _currentPageNumberHtmlFormatString; }
            set { _currentPageNumberHtmlFormatString = value; }
        }
        private string _currentPageNumberHtmlFormatString;

        /// <summary>
        /// The HTML string used for separating two page numbers. For example you can use , as a separator. Default is ' '.
        /// </summary>
        public string SeparatorHtmlString { get; set; }

        /// <summary>
        /// True to show a go to previous page link. Default is true.
        /// </summary>
        public bool ShowPreviousLink { get; set; }

        /// <summary>
        /// The HTML/text to show inside the previous link element. Default is '&lt;'.
        /// </summary>
        public string PreviousLinkInnerHtml { get; set; }

        /// <summary>
        /// True to show a go to next page link. Default is true.
        /// </summary>
        public bool ShowNextLink { get; set; }

        /// <summary>
        /// The HTML/text to show inside the next link element. Default is '&gt;'.
        /// </summary>
        public string NextLinkInnerHtml { get; set; }

        /// <summary>
        /// True to show a go to first page link. Default is true.
        /// </summary>
        public bool ShowFirstLink { get; set; }

        /// <summary>
        /// The HTML/text to show inside the first link element. Default is '&lt;&lt;'. 
        /// </summary>
        public string FirstLinkInnerHtml { get; set; }

        /// <summary>
        /// True to show a go to last page link. Default is true.
        /// </summary>
        public bool ShowLastLink { get; set; }

        /// <summary>
        /// The HTML/text to show inside the last link element. Default is '&gt;&gt;'. 
        /// </summary>
        public string LastLinkInnerHtml { get; set; }

        /// <summary>
        /// True to hide the pager when there is only one page. Default is false.
        /// </summary>
        public bool AutoHide { get; set; }

        /// <summary>
        /// A custom JavaScript function for the page click that has as an input parameter the page number.
        /// </summary>
        public string PageLinkCustomAction { get; set; }

        #endregion

        /// <summary>
        /// Helper class used for rendering the pager for a list in HTML.
        /// </summary>
        /// <param name="currentPageNumber">The current page number. By default it is 1 (the first page).</param>
        /// <param name="pageSize">The maximum number of elements in a page. If less then 1 we will consider 10 by default.</param>
        /// <param name="totalNumberOfItems">The total number of elements in the list.</param>
        /// <param name="actionName">The name of the action method.</param>
        /// <param name="controllerName">The name of the controller.</param>
        /// <param name="routeValues">An object that contains the parameters for a route.</param>
        /// <param name="pageRouteValueName">The key in the RouteValues dictionary where to put the page number.</param>
        public PagerHtmlRenderer(int currentPageNumber, int pageSize, int totalNumberOfItems, string actionName, string controllerName, RouteValueDictionary routeValues, string pageRouteValueName)
        {
            CurrentPageNumber = currentPageNumber;
            PageSize = pageSize;
            TotalNumberOfItems = totalNumberOfItems;
            ActionName = actionName;
            ControllerName = controllerName;
            RouteValues = routeValues;
            PageRouteValueName = pageRouteValueName;

            //Defaults
            MaximumNumberOfPageLinksToRender = 10;
            CurrentPageNumberHtmlFormatString = "<strong>{0}</strong>";
            SeparatorHtmlString = " ";
            ShowPreviousLink = true;
            PreviousLinkInnerHtml = "&lt;";
            ShowNextLink = true;
            NextLinkInnerHtml = "&gt";
            ShowFirstLink = true;
            FirstLinkInnerHtml = "&lt;&lt;";
            ShowLastLink = true;
            LastLinkInnerHtml = "&gt;&gt;";
            AutoHide = false;
        }

        /// <summary>
        /// Generate the HTML for the pager based on the current object properties.
        /// </summary>
        /// <param name="urlHelper">The current Url helper associated to the View. Used for generating the URLs in the page links.</param>
        /// <returns></returns>
        public string GeneratePagerHtml(UrlHelper urlHelper)
        {
            var totalNumberOfPages = TotalNumberOfItems == 0
                                         ? 1
                                         : (TotalNumberOfItems/PageSize + (TotalNumberOfItems%PageSize > 0 ? 1 : 0));
            var currentPageNumber = CurrentPageNumber < 1
                                        ? 1
                                        : (CurrentPageNumber > totalNumberOfPages
                                               ? totalNumberOfPages
                                               : CurrentPageNumber);

            if (AutoHide && totalNumberOfPages == 1)
                return string.Empty;

            var sb = new StringBuilder();           

            //First, Previous
            if (ShowFirstLink && currentPageNumber > 1)
                AddToStringBuilder(sb, GetLinkHtml(1, FirstLinkInnerHtml, urlHelper));
           
            if (ShowPreviousLink && currentPageNumber > 1)
                AddToStringBuilder(sb, GetLinkHtml(currentPageNumber - 1, PreviousLinkInnerHtml, urlHelper));

            //Pages
            var centerPosition = MaximumNumberOfPageLinksToRender/2;
            var startPageNumber = currentPageNumber <= centerPosition ? 1 : currentPageNumber - centerPosition;
            for (var pageNumber = startPageNumber; pageNumber <= totalNumberOfPages && pageNumber < startPageNumber + MaximumNumberOfPageLinksToRender; pageNumber++)
            {
                AddToStringBuilder(sb,
                                   pageNumber == currentPageNumber
                                       ? string.Format(CurrentPageNumberHtmlFormatString, pageNumber)
                                       : string.Format(PageNumberHtmlFormatString, GetLinkHtml(pageNumber, pageNumber.ToString(CultureInfo.InvariantCulture), urlHelper)));
            }

            //Next, Last
            if (ShowNextLink && currentPageNumber < totalNumberOfPages)
                AddToStringBuilder(sb, GetLinkHtml(currentPageNumber + 1, NextLinkInnerHtml, urlHelper));
            if (ShowLastLink && currentPageNumber < totalNumberOfPages)
                AddToStringBuilder(sb, GetLinkHtml(totalNumberOfPages, LastLinkInnerHtml, urlHelper));

            return string.Format(ContainerHtmlFormatString, sb);
        }
        
        protected string GetLinkHtml(int pageNumber, string innerHtml, UrlHelper urlHelper)
        {
            return string.Format("<a href=\"{0}\">{1}</a>", string.IsNullOrEmpty(PageLinkCustomAction) ? GetPageLink(pageNumber, urlHelper) : GetCustomAction(pageNumber), innerHtml);
        }

        protected string GetCustomAction(int pageNumber)
        {
            return string.Format("javascript:{0}(1);", PageLinkCustomAction);
        }

        protected string GetPageLink(int pageNumber, UrlHelper urlHelper)
        {
            if (RouteValues != null && !string.IsNullOrEmpty(PageRouteValueName) && RouteValues.ContainsKey(PageRouteValueName))
                RouteValues[PageRouteValueName] = pageNumber;

            return string.IsNullOrEmpty(ActionName) || string.IsNullOrEmpty(ControllerName) ? "#" : urlHelper.Action(ActionName, ControllerName, RouteValues);
        }

        /// <summary>
        /// Add the string to the string builder. If the string builder is not emplty add before a separator.
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="textToAdd"></param>
        protected void AddToStringBuilder(StringBuilder sb, string textToAdd)
        {
            if (sb.Length > 0)
                sb.Append(SeparatorHtmlString);
            sb.Append(textToAdd);
        }
    }
}

Below is an example of how to call the HtmlHelper pager extension from a view:

@Html.PagedListPager(Url, new PagerHtmlRenderer(
			//Constructor mandatory values
			currentPageNumber: 3,
			pageSize: 10,
			totalNumberOfItems: 125,
			actionName: "Index",
			controllerName: "Home",
			routeValues: new RouteValueDictionary(){{"page", ""}},
			pageRouteValueName: "page")
			{
				//Properties
				PreviousLinkInnerHtml = "<strong>Previous</strong>",
				NextLinkInnerHtml = "<strong>Next<strong>",
				FirstLinkInnerHtml = "<strong>First</strong>",
				LastLinkInnerHtml = "<strong>Last</strong>"
			})