Implement CRUD Operations With Sorting, Searching And Paging Using EF Core In ASP.NET Core

Debasis Saha
Nov 01, 2019

336.3k
0
22
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

EF_Crud_Samples.rar|EF_Crud_Samples_ExcelDownload.rar

Entity Framework Core (EF Core) is one of the latest data access technologies for any type of application in the .NET Core framework. Entity Framework Core is based on Object-Relational Mapper or ORM model. Basically, the object-relational model is a technique that helps the developer to establish a relation between the entity model class of the application and the data objects of the relational database. In this technique, developers can create a relation between the entity model class and database objects, i.e., tables in the relational database in an object-oriented way to perform different types of operations like fetch data, save data, etc. Entity Framework Core is a lightweight extensible framework which can support cross-platform development.

Entity Framework Core provides a lot of benefits to the concept of rapid developments. The main advantages of Entity Framework Core are:

Entity framework core provides auto generated code.
It helps us to reduce development time.
It also helps us to reduce development cost.
Using this framework, developers can visually design entity models and can map the database objects.
It provides a multiple conceptual model to map with a single schema object.

PREREQUISITES REQUIRED

In this article, we will discuss how to develop an application using Entity Framework Core. In this application, we can perform the following operations:

Basic CRUD Operations like Insert, Update, Delete & Read
Sort Record
Search Record
Paging Data for Listing
Download Index List Data as Excel File

For starting this development, we need to have the below prerequisites.

Microsoft Visual Studio 2017
.Net Core 2.1 or Above SDK
Relation Database like SQL Server

CREATE DATABASE USING ENTITY FRAMEWORK CORE CODE FIRST APPROACH

Step 1

Now, open Microsoft Visual Studio 2017 and click on File --> New --> Projects.

Implement CRUD Operations With Sorting, Searching And Paging Using EF Core In ASP.NET Core

Step 2

Select the Web Application Project template and click the OK button.

Step 3

In the Project Template Box, select Web Application (Model-View-Controller) options and click on the OK button.

Step 4

Now, a blank project solution has been ready.

Step 5

Now, select the solution file and right-click and select Add --> New Projects.

Step 6

Now, select Class Library (.NET Core) Project Template

Step 7

Provide a Project Name as DataContext and click OK.

Step 8

Now, install the below Nuget Packages related to the Entity Framework Core.

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Step 9

Now, create a folder called Models in this project and add the below models.

Department.cs --> Department class which contains Department name list

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;
namespace DataContext.Models
{
[Table("Department", Schema = "dbo")]
public class Department
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Display(Name = "Department Id")]
public int DepartmentId { get; set; }
[Required]
[Column(TypeName = "NVarchar(20)")]
[Display(Name = "Department Code")]
public string Alias { get; set; }
[Required]
[Column(TypeName = "Nvarchar(100)")]
[Display(Name = "Department Description")]
public string DepartmentName { get; set; }
}
}

Designation.cs --> Designation class which contains Designation Name List

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;
namespace DataContext.Models
{
[Table("Designation", Schema = "dbo")]
public class Designation
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int DesignationId { get; set; }
[Required]
[Column(TypeName = "NVarchar(20)")]
[Display(Name = "Designation Code")]
public string DesignationCode { get; set; }
[Required]
[Column(TypeName = "varchar(100)")]
[Display(Name = "Designation Name")]
public string DesignationName { get; set; }
}
}

Employee.cs --> Employee class which contains Employee details List. In this entity, DepartmentId and DesignationId is acting as a Foreign Key related to the Department and Designation Entity.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;
namespace DataContext.Models
{
[Table("Employee", Schema = "dbo")]
public class Employee
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Display(Name = "Employee Id")]
public int Id { get; set; }
[Required]
[Column(TypeName = "NVarchar(20)")]
[Display(Name = "Code")]
public string EmployeeCode { get; set; }
[Required]
[Column(TypeName = "NVarchar(100)")]
[Display(Name = "Employee Name")]
public string EmployeeName { get; set; }
[Required]
[Column(TypeName = "DateTime2")]
[Display(Name = "Date of Birth")]
[DataType(DataType.Date)]
public DateTime DateOfBirth { get; set; }
[Required]
[Column(TypeName = "DateTime2")]
[Display(Name = "Join Date")]
[DataType(DataType.Date)]
public DateTime JoinDate { get; set; }
[Required]
[Column(TypeName = "Decimal(18,2)")]
public decimal Salary { get; set; }
[Column(TypeName = "NVarchar(100)")]
public string Address { get; set; }
[Column(TypeName = "NVarchar(100)")]
public string State { get; set; }
[Column(TypeName = "NVarchar(100)")]
public string City { get; set; }
[Column(TypeName = "NVarchar(20)")]
public string ZipCode { get; set; }
[ForeignKey("DepartmentInfo")]
[Required]
public int DepartmentId { get; set; }
[Display(Name = "Department")]
[NotMapped]
public string DepartmentName { get; set; }
public virtual Department DepartmentInfo { get; set; }
[ForeignKey("DesignationInfo")]
[Required]
public int DesignationId { get; set; }
[Display(Name = "Designation")]
[NotMapped]
public string DesignationName { get; set; }
public virtual Designation DesignationInfo { get; set; }
}
}

Step 10

Now, create another folder called DataContext.

Step 11

Within this folder, add the class file name EFDataContext.cs which will contains mapping code related to the entity and database tables. Now, add the below code in this file.

using DataContext.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace DataContext.DataContext
{
public class EFDataContext : DbContext
{
public DbSet<Department> Departments { get; set; }
public DbSet<Designation> Designations { get; set; }
public DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"data source=xxx; initial catalog=EFCrudDemo;persist security info=True;user id=sa;password=ssss;");
}
}

Step 12

In the above file, we overrode the method OnConfiguring() of the DBContext to specify the database and sql server credentials so that Entity Framework Core can connect with the database server for the CRUD operations in the applications.

Step 13

Now, open the Package Manager Console and run the below command – add migration migration1 (here migration1 is the alias name which can be changed).

Step 14

Now, run the command update database.

Step 15

Now, open the SQL Server and check the mentioned database is already created.

Step 16

Now, in this application, we will create a CRUD operation for Employee data objects. So, we need to insert sample data using a database in the Department and Designation tables so that when we will try to create employee from the application we can generate the department and designation lists.

CREATE EMPLOYEE INDEX UI

Step 1

Now select the DemoApplication Projects (MVC Application) and provide the reference of the DataContext Library which we already add.

Step 2

Now Select the Controller Folder and right click and select Add --> Controller.

Step 3

In the Controller Template, Select MVC Controller – Empty and click on Add Button

Step 4

Provide the Controller Name as EmployeesController and Click on Ok Button.

Step 5

Now in the controller file, create an object for EFDataContext class as below -

EFDataContext _dbContext = new EFDataContext();

Step 6

Now in the controller file, add a method named Index and write down the below code -

public class EmployeesController : Controller
{
EFDataContext _dbContext = new EFDataContext();
public IActionResult Index()
{
List<Employee> employees = this._dbContext.Employees.ToList();
return View(employees);
}
}

Step 7

In the View Folder, create a new folder called Employee and then add an empty view named index.cshtml.

Step 8

Now open the Index.cshtml view and the the below code.

@model IEnumerable<DataContext.Models.Employee>
@{
ViewData["Title"] = "Employee List";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.EmployeeCode)
</th>
<th>
@Html.DisplayNameFor(model => model.EmployeeName)
</th>
<th>
@Html.DisplayNameFor(model => model.DateOfBirth)
</th>
<th>
@Html.DisplayNameFor(model => model.JoinDate)
</th>
<th>
@Html.DisplayNameFor(model => model.DepartmentName)
</th>
<th>
@Html.DisplayNameFor(model => model.DesignationName)
</th>
<th>
@Html.DisplayNameFor(model => model.Salary)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmployeeCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.EmployeeName)
</td>
<td>
@Html.DisplayFor(modelItem => item.DateOfBirth)
</td>
<td>
@Html.DisplayFor(modelItem => item.JoinDate)
</td>
<td>
@Html.DisplayFor(modelItem => item.DepartmentName)
</td>
<td>
@Html.DisplayFor(modelItem => item.DesignationName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Salary)
</td>
<td>
@Html.DisplayFor(modelItem => item.State)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.Id }) |
@Html.ActionLink("Delete", "Delete", new { id = item.Id })
</td>
</tr>
}
</tbody>
</table>

Step 9

Now open the _Layout.cshtml view in shared folder and the below line of code in that file:

<header>
<nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
<div class="container">
<a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">EF_Crud_Samples</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">
<ul class="navbar-nav flex-grow-1">
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
</li>
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Employees" asp-action="Index">Employee</a>
</li>
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
</li>
</ul>
</div>
</div>
</nav>
</header>

Step 10

Now run the application and click on Employee Link

CREATE NEW EMPLOYEE UI

Step 1

Now, open the EmployeesController.cs file again and add a new method as below:

public IActionResult Create()
{
this.GetModelData();
return View();
}
private void GetModelData()
{
ViewBag.Departments = this._dbContext.Departments.ToList();
ViewBag.Designations = this._dbContext.Designations.ToList();
}

Step 2

In the above method, we create two ViewBag inststances for Department and Designation Lists.

Step 3

Now Select the Employee folder under views and add another empty view. Create.cshtml and add the below code in that file.

@model DataContext.Models.Employee
@{
ViewData["Title"] = Model != null ? "Edit" : "Create";
}
<h2>@ViewData["Title"] Employee</h2>
<hr />
<div class="row">
<div class="col-lg-12">
<form asp-action="ViewData[" Title"]">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<div class="form-row">
<div class="col-lg-2">
<label asp-for="EmployeeCode" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="EmployeeCode" class="form-control" />
<span asp-validation-for="EmployeeCode" class="text-danger"></span>
</div>
<div class="col-lg-2">
<label asp-for="EmployeeName" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="EmployeeName" class="form-control" />
<span asp-validation-for="EmployeeName" class="text-danger"></span>
</div>
</div>
<div class="form-row">
<div class="col-lg-2">
<label asp-for="DateOfBirth" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="DateOfBirth" class="form-control" />
<span asp-validation-for="DateOfBirth" class="text-danger"></span>
</div>
<div class="col-lg-2">
<label asp-for="JoinDate" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="JoinDate" class="form-control" />
<span asp-validation-for="JoinDate" class="text-danger"></span>
</div>
</div>
<div class="form-row">
<div class="col-lg-2">
<label asp-for="DepartmentId" class="control-label"></label>
</div>
<div class="col-lg-4">
<select asp-for="DepartmentId" asp-items="@(new SelectList(ViewBag.Departments,"DepartmentId","DepartmentName"))" class="form-control">
<option value="">--Select--</option>
</select>
<span asp-validation-for="DepartmentId" class="text-danger"></span>
</div>
<div class="col-lg-2">
<label asp-for="DesignationId" class="control-label"></label>
</div>
<div class="col-lg-4">
<select asp-for="DesignationId" asp-items="@(new SelectList(ViewBag.Designations,"DesignationId","DesignationName"))" class="form-control">
<option value="">--Select--</option>
</select>
<span asp-validation-for="DesignationId" class="text-danger"></span>
</div>
</div>
<div class="form-row">
<div class="col-lg-2">
<label asp-for="Salary" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="Salary" class="form-control" />
<span asp-validation-for="Salary" class="text-danger"></span>
</div>
<div class="col-lg-2">
</div>
<div class="col-lg-4">
</div>
</div>
<div class="form-row">
<div class="col-lg-2">
<label asp-for="Address" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="Address" class="form-control" />
<span asp-validation-for="Address" class="text-danger"></span>
</div>
<div class="col-lg-2">
<label asp-for="State" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="State" class="form-control" />
<span asp-validation-for="State" class="text-danger"></span>
</div>
</div>
<div class="form-row">
<div class="col-lg-2">
<label asp-for="City" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="City" class="form-control" />
<span asp-validation-for="City" class="text-danger"></span>
</div>
<div class="col-lg-2">
<label asp-for="ZipCode" class="control-label"></label>
</div>
<div class="col-lg-4">
<input asp-for="ZipCode" class="form-control" />
<span asp-validation-for="ZipCode" class="text-danger"></span>
</div>
</div>
<div class="form-row">
<hr />
</div>
<div class="form-row">
<div class="col-lg-3">
</div>
<div class="col-lg-7">
<input type="submit" value="Save" class="btn btn-primary" />
<a asp-action="Index" class="btn btn-secondary">Cancel</a>
</div>
</div>
</div>
</form>
</div>
</div>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Step 4

Now, return back to the EmployeesController.cs file and write down the method to save the data in the database. Note that, in this method we need to use HttpPost verb so that it can be fired on Form submit event from UI and it takes Employee data as argument. After successfully saving the data, we will automatically redirect to the Index UI.

[HttpPost]
public IActionResult Create(Employee model)
{
if (ModelState.IsValid)
{
_dbContext.Employees.Add(model);
_dbContext.SaveChanges();
return RedirectToAction("Index");
}
this.GetModelData();
return View();
}

Step 5

Now run the application and then click on Create New Button in the Employee Index UI.

Step 6

After providing proper data in the form, click on the Submit button.

Step 7

Now, the Employee Index Page starts to display the information.

Step 8

In the Employee List, department and designation information is missing. But when we save the data, we provide the information for Department and Designation. This is because the Employee entity only contains DepartmentId and DesignationId. But we need to display a description related to those ids in the UI. For that purpose, we will need to make changes in the retrieval of Employee List in the Index() as below:

public IActionResult Index()
{
var employees = (from employee in this._dbContext.Employees
join desig in this._dbContext.Designations on employee.DepartmentId equals desig.DesignationId
join dept in this._dbContext.Departments on employee.DepartmentId equals dept.DepartmentId
select new Employee
{
Id = employee.Id,
EmployeeCode = employee.EmployeeCode,
EmployeeName = employee.EmployeeName,
DateOfBirth = employee.DateOfBirth,
JoinDate = employee.JoinDate,
Salary = employee.Salary,
Address = employee.Address,
State = employee.State,
City = employee.City,
ZipCode = employee.ZipCode,
DepartmentId = employee.DepartmentId,
DepartmentName = dept.DepartmentName,
DesignationId = employee.DesignationId,
DesignationName = desig.DesignationName
}).ToList();
return View(employees);
}

Step 9

Now, again run the application and check the Index UI of the Employee.

EDIT & DELETE EMPLOYEE DATA

Step 1

Now, we want to edit the existing employee data. For that, we have already created the "Edit" button in the Index UI. On click of the Edit, it will redirect to the Edit() of the controller. This method returns the same create.cshtml view.

public IActionResult Edit(int id)
{
Employee data = this._dbContext.Employees.Where(p => p.Id == id).FirstOrDefault();
this.GetModelData();
return View("Create", data);
}

Step 2

After making the modification on the existing data, we need to submit that data. For this, we need to called another HttpPost Method called Edit() in the controller.

[HttpPost]
public IActionResult Edit(Employee model)
{
if (ModelState.IsValid)
{
_dbContext.Employees.Update(model);
_dbContext.SaveChanges();
return RedirectToAction("Index");
}
this.GetModelData();
return View("Create", model);
}

Step 3

After submitting the change, related changes have been shown in the Index Page.

Step 4

Now, add another method called Delete() for deleting the existing data in the EmployeesController File.

public IActionResult Delete(int id)
{
Employee data = this._dbContext.Employees.Where(p => p.Id == id).FirstOrDefault();
if (data != null)
{
_dbContext.Employees.Remove(data);
_dbContext.SaveChanges();
}
return RedirectToAction("Index");
}

Step 5

Now, on clicking on the Delete button for any employee, that data will be deleted from the index list as well as the database.

Step 6

Now, we can use a stored procedure to fetch any data from the database. For that purpose, first, we need to create a store procedure which accepts employee id and returns all the records of that employee.

Create procedure uspGetEmployee
(
@p_EmployeeId INT = 0
)
As
BEGIN
IF @p_EmployeeId > 0
BEGIN
Select * from DBO.Employee WHERE Id= @p_EmployeeId
END
END
GO

Step 7

Now, open the EFDataContex.cs file and add the below method to execute the stored procedure using Entity Framework.

public Employee GetEmployeeById(int employeeId)
{
IQueryable<Employee> data = this.Employees.FromSql<Employee>(
"Exec [dbo].uspGetEmployee " +
"@p_EmployeeId", new SqlParameter("p_EmployeeId", employeeId));
if (data != null)
return data.FirstOrDefault();
else
return new Employee();
}

Step 8

Now, change the code of Edit() method as below.

public IActionResult Edit(int id)
{
Employee data = this._dbContext.GetEmployeeById(id);
this.GetModelData();
return View("Create", data);
}

Step 9

Now, run the application and check all CRUD operations.

ADD SORTING IN EMPLOYEE LIST

Step 1

Now, to add sorting, we first need to make changes as below in the Index.cshtml pages.

<thead>
<tr>
<th style="width:10%;"> @Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:20%;"> @Html.ActionLink("Employee Name", "Index", new { sortField = "EmployeeName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:10%;"> @Html.ActionLink("Join Date", "Index", new { sortField = "JoinDate", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:15%;"> @Html.ActionLink("Department", "Index", new { sortField = "DepartmentName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:15%;"> @Html.ActionLink("Designation", "Index", new { sortField = "DesignationName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:10%;"> @Html.ActionLink("Salary", "Index", new { sortField = "Salary", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder }) </th>
<th style="width:10%;"> @Html.ActionLink("City", "Index", new { sortField = "City", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
</th>
<th style="width:10%;"></th>
</tr>
</thead>

Step 2

In the above code, we pass SortField, CurrentSortField and CurrentSortOrder to the Index Page so that we can sort the data in the controller and return to the view.

Step 3

So, we need to accept 3 arguments in the index method as below:

public IActionResult Index(string sortField, string currentSortField, string currentSortOrder)
{
var employees = this.GetEmployeeList();
return View(this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder));
}
private List<Employee> SortEmployeeData(List<Employee> employees, string sortField, string currentSortField, string currentSortOrder)
{
if (string.IsNullOrEmpty(sortField))
{
ViewBag.SortField = "EmployeeCode";
ViewBag.SortOrder = "Asc";
}
else
{
if (currentSortField == sortField)
{
ViewBag.SortOrder = currentSortOrder == "Asc" ? "Desc" : "Asc";
}
else
{
ViewBag.SortOrder = "Asc";
}
ViewBag.SortField = sortField;
}
var propertyInfo = typeof(Employee).GetProperty(ViewBag.SortField);
if (ViewBag.SortOrder == "Asc")
{
employees = employees.OrderBy(s => propertyInfo.GetValue(s, null)).ToList();
}
else
{
employees = employees.OrderByDescending(s => propertyInfo.GetValue(s, null)).ToList();
}
return employees;
}

Step 4

Now, in the above code, we create another method called SortEmployeeData() which stored the Current Sort Field and SortOrder in the View Bag. Also, this method is used to find the property name and then sort that data accordingly.

Step 5

Now, run the application and check the index page of employee.

ADD SEARCH BOX IN THE INDEX PAGE

Step 1

Now, for adding the search functionality, we need to add the below code in the Index.cshtml page just above the Index table.

<p>
<a asp-action="Create">Create New</a>
</p>
@using (Html.BeginForm())
{
<p>
Find by Employee Name: @Html.TextBox("SearchString")
<input type="submit" value="Search" />
</p>
}
<table class="table">
<thead>
<tr>
<th style="width:10%;">
@Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })
@*@Html.DisplayNameFor(model => model.EmployeeCode)*@
</th>

Step 2

Now make the below changes in the Index() of the EmployeesController.cs

public IActionResult Index(string sortField, string currentSortField, string currentSortOrder, string SearchString)
{
var employees = this.GetEmployeeList();
if (!String.IsNullOrEmpty(SearchString))
{
employees = employees.Where(s => s.EmployeeName.Contains(SearchString)).ToList();
}
return View(this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder));
}

Step 3

Now run the application and check

ADD PAGING IN THE INDEX PAGE

Step 1

First Select the DataContext project and add a folder called Paging.

Step 2

In that folder, add a file called PagingList.cs file and add the below code

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataContext.Paging
{
public class PagingList<T> : List<T>
{
public int PageIndex { get; private set; }
public int TotalPages { get; private set; }
public PagingList(List<T> items, int count, int pageIndex, int pageSize)
{
PageIndex = pageIndex;
TotalPages = (int)Math.Ceiling(count / (double)pageSize);
this.AddRange(items);
}
public bool HasPreviousPage
{
get
{
return (PageIndex > 1);
}
}
public bool HasNextPage
{
get
{
return (PageIndex < TotalPages);
}
}
public int TotalPageNo
{
get
{
return TotalPages;
}
}
public static PagingList<T> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
{
var count = source.Count();
var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return new PagingList<T>(items, count, pageIndex, pageSize);
}
}
}

Step 3

Now change the below code in the declaration section of the index.cshtml page

@model DataContext.Paging.PagingList<DataContext.Models.Employee>
<link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />
@{
ViewData["Title"] = "Employee List";
}

Step 4

Now change the below code in the header part of the index.cshtml page

<thead>
<tr>
<th style="width:10%;">
@Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:20%;">
@Html.ActionLink("Employee Name", "Index", new { sortField = "EmployeeName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:10%;">
@Html.ActionLink("Join Date", "Index", new { sortField = "JoinDate", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:15%;">
@Html.ActionLink("Department", "Index", new { sortField = "DepartmentName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:15%;">
@Html.ActionLink("Designation", "Index", new { sortField = "DesignationName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:10%;">
@Html.ActionLink("Salary", "Index", new { sortField = "Salary", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:10%;">
@Html.ActionLink("City", "Index", new { sortField = "City", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })
</th>
<th style="width:10%;"></th>
</tr>
</thead>

Step 5

Now add the below code at the end of the Index.cshtml page.

<br />
@{
var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
var nextDisabled = !Model.HasNextPage ? "disabled" : "";
var totalPageNo = Model.TotalPageNo;
var currentPageNo = Model.PageIndex;
}
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-pageNo="@(Model.PageIndex - 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @prevDisabled">
Previous
</a>
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-pageNo="@(Model.PageIndex + 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @nextDisabled">
Next
</a>
<span>
Page No : @currentPageNo of @totalPageNo
</span>

Step 4

Now, make the below changes in the Index() in the EmployeesController.cs method

public IActionResult Index(string sortField, string currentSortField, string currentSortOrder, string currentFilter, string SearchString,
int ? pageNo) {
var employees = this.GetEmployeeList();
if (SearchString != null) {
pageNo = 1;
} else {
SearchString = currentFilter;
}
ViewData["CurrentSort"] = sortField;
ViewBag.CurrentFilter = SearchString;
if (!String.IsNullOrEmpty(SearchString)) {
employees = employees.Where(s => s.EmployeeName.Contains(SearchString)).ToList();
}
employees = this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder);
int pageSize = 10;
return View(PagingList < Employee > .CreateAsync(employees.AsQueryable < Employee > (), pageNo ? ? 1, pageSize));
}

Step 5

Now run the application.

Download Index List Data as Excel File

Now we need to download the Index List data as excel file. For that purpose, we first need to install a package called EPPlus 4.5.3 version from Nuget Package. Now insert the below code into Index.cshtml file -

@*@model IEnumerable<DataContext.Models.Employee>*@
@model DataContext.Paging.PagingList<DataContext.Models.Employee>
@{
ViewData["Title"] = "Employee List";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<div class="text-right" style="overflow:hidden;position:absolute;padding:1px 0px 0px 0px;right: 350px;">
<button type="button" class="btn btn-info" onclick="location.href='@Url.Action("ExportToExcel", "Employees")'">Export To Excel</button>
</div>
@using (Html.BeginForm("Index", "Employees", FormMethod.Get))
{
<p>
Find by Employee Name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)
<input type="submit" value="Search" />
</p>
}

Now open the EmployeesController.cs file and add the below code for download the excel file -

public IActionResult ExportToExcel()
{
var employees = this.GetEmployeeList();
byte[] fileContents;
ExcelPackage Ep = new ExcelPackage();
ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("EmployeeInfo");
Sheet.Cells["A1"].Value = "Employee Code";
Sheet.Cells["B1"].Value = "Employee Name";
Sheet.Cells["C1"].Value = "Join Date";
Sheet.Cells["D1"].Value = "Department";
Sheet.Cells["E1"].Value = "Designation";
Sheet.Cells["F1"].Value = "Salary";
Sheet.Cells["G1"].Value = "City";
int row = 2;
foreach (var item in employees)
{
Sheet.Cells[string.Format("A{0}", row)].Value = item.EmployeeCode;
Sheet.Cells[string.Format("B{0}", row)].Value = item.EmployeeName;
Sheet.Cells[string.Format("C{0}", row)].Value = item.JoinDate;
Sheet.Cells[string.Format("D{0}", row)].Value = item.DepartmentName;
Sheet.Cells[string.Format("E{0}", row)].Value = item.DesignationName;
Sheet.Cells[string.Format("F{0}", row)].Value = item.Salary;
Sheet.Cells[string.Format("G{0}", row)].Value = item.City;
row++;
}
Sheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileContents = Ep.GetAsByteArray();
if (fileContents == null || fileContents.Length == 0)
{
return NotFound();
}
return File(
fileContents: fileContents,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "Employee.xlsx"
);
}

Now, run the result and check the output -

Conclusion

In this article, we discussed how to implement basic CRUD operations including sorting data and paging in the index page. I hope this will help the reader to understand how to use entity framework core in real life applications. Suggestions and feedback related to this article or any subject are most welcome. For any further query or clarification, ping me.

Recommended Free Ebook

API Development Using ASP.NET Core Web API

Download Now!