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

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 which 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 in the concept of rapid developments. The main advantages of Entity Framework Core are:

1. Entity framework core provides auto generated code.
2. It helps us to reduce development time.
3. It also helps us to reduce development cost.
4. Using this framework, developers can visually design entity models and can map the database objects.
5. 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:

1. Basic CRUD Operations like Insert, Update, Delete & Read
2. Sort Record
3. Search Record
4. Paging Data for Listing

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.

 

 

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

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations;  
4. using System.ComponentModel.DataAnnotations.Schema;  
5. using System.Text;  
6.   
7. namespace DataContext.Models  
8. {  
9.     [Table("Department", Schema = "dbo")]  
10.     public class Department  
11.     {  
12.         [Key]  
13.         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
14.         [Display(Name = "Department Id")]  
15.         public int DepartmentId { get; set; }  
16.   
17.         [Required]  
18.         [Column(TypeName = "NVarchar(20)")]  
19.         [Display(Name = "Department Code")]  
20.         public string Alias { get; set; }  
21.   
22.         [Required]  
23.         [Column(TypeName = "Nvarchar(100)")]  
24.         [Display(Name = "Department Description")]  
25.         public string DepartmentName { get; set; }  
26.   
27.     }  
28. }  

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

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations;  
4. using System.ComponentModel.DataAnnotations.Schema;  
5. using System.Text;  
6.   
7. namespace DataContext.Models  
8. {  
9.     [Table("Designation", Schema = "dbo")]  
10.     public class Designation  
11.     {  
12.         [Key]  
13.         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
14.         public int DesignationId { get; set; }  
15.   
16.         [Required]  
17.         [Column(TypeName = "NVarchar(20)")]  
18.         [Display(Name = "Designation Code")]  
19.         public string DesignationCode { get; set; }  
20.   
21.         [Required]  
22.         [Column(TypeName = "varchar(100)")]  
23.         [Display(Name = "Designation Name")]  
24.         public string DesignationName { get; set; }          
25.     }  
26. }  

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.

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations;  
4. using System.ComponentModel.DataAnnotations.Schema;  
5. using System.Text;  
6.   
7. namespace DataContext.Models  
8. {  
9.     [Table("Employee", Schema = "dbo")]  
10.     public class Employee  
11.     {  
12.         [Key]  
13.         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
14.         [Display(Name = "Employee Id")]  
15.         public int Id { get; set; }  
16.   
17.         [Required]  
18.         [Column(TypeName = "NVarchar(20)")]  
19.         [Display(Name = "Code")]  
20.         public string EmployeeCode { get; set; }  
21.   
22.         [Required]  
23.         [Column(TypeName = "NVarchar(100)")]  
24.         [Display(Name = "Employee Name")]  
25.         public string EmployeeName { get; set; }  
26.   
27.         [Required]  
28.         [Column(TypeName = "DateTime2")]  
29.         [Display(Name = "Date of Birth")]  
30.         [DataType(DataType.Date)]  
31.         public DateTime DateOfBirth { get; set; }  
32.   
33.         [Required]  
34.         [Column(TypeName = "DateTime2")]  
35.         [Display(Name = "Join Date")]  
36.         [DataType(DataType.Date)]  
37.         public DateTime JoinDate { get; set; }  
38.   
39.         [Required]  
40.         [Column(TypeName = "Decimal(18,2)")]  
41.         public decimal Salary { get; set; }  
42.           
43.         [Column(TypeName = "NVarchar(100)")]  
44.         public string Address { get; set; }  
45.   
46.         [Column(TypeName = "NVarchar(100)")]  
47.         public string State { get; set; }  
48.   
49.         [Column(TypeName = "NVarchar(100)")]  
50.         public string City { get; set; }  
51.   
52.         [Column(TypeName = "NVarchar(20)")]  
53.         public string ZipCode { get; set; }  
54.   
55.         [ForeignKey("DepartmentInfo")]  
56.         [Required]  
57.         public int DepartmentId { get; set; }  
58.   
59.         [Display(Name = "Department")]  
60.         [NotMapped]  
61.         public string DepartmentName { get; set; }  
62.   
63.         public virtual Department DepartmentInfo { get; set; }  
64.   
65.         [ForeignKey("DesignationInfo")]  
66.         [Required]  
67.         public int DesignationId { get; set; }  
68.   
69.         [Display(Name = "Designation")]  
70.         [NotMapped]  
71.         public string DesignationName { get; set; }  
72.   
73.         public virtual Designation DesignationInfo { get; set; }  
74.     }  
75. }  

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.

1. using DataContext.Models;  
2. using Microsoft.EntityFrameworkCore;  
3. using System;  
4. using System.Collections.Generic;  
5. using System.Data.SqlClient;  
6. using System.Linq;  
7. using System.Text;  
8.   
9. namespace DataContext.DataContext  
10. {  
11.     public class EFDataContext : DbContext  
12.     {  
13.         public DbSet<Department> Departments { get; set; }  
14.   
15.         public DbSet<Designation> Designations { get; set; }  
16.   
17.         public DbSet<Employee> Employees { get; set; }  
18.   
19.         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
20.         {  
21.             optionsBuilder.UseSqlServer(@"data source=xxx; initial catalog=EFCrudDemo;persist security info=True;user id=sa;password=ssss;");  
22.         }  
23.   
24. }   

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 -

1. EFDataContext _dbContext = new EFDataContext();  

Step 6

 

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

1. public class EmployeesController : Controller  
2.     {  
3.         EFDataContext _dbContext = new EFDataContext();  
4.   
5.         public IActionResult Index()  
6.         {  
7.             List<Employee> employees = this._dbContext.Employees.ToList();  
8.             return View(employees);  
9.         }  
10. }  

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.

1. @model IEnumerable<DataContext.Models.Employee>  
2.   
3. @{  
4.     ViewData["Title"] = "Employee List";  
5. }  
6.   
7. <h1>Index</h1>  
8.   
9. <p>  
10.     <a asp-action="Create">Create New</a>  
11. </p>  
12. <table class="table">  
13.     <thead>  
14.         <tr>  
15.             <th>  
16.                 @Html.DisplayNameFor(model => model.EmployeeCode)  
17.             </th>  
18.             <th>  
19.                 @Html.DisplayNameFor(model => model.EmployeeName)  
20.             </th>  
21.             <th>  
22.                 @Html.DisplayNameFor(model => model.DateOfBirth)  
23.             </th>  
24.             <th>  
25.                 @Html.DisplayNameFor(model => model.JoinDate)  
26.             </th>  
27.             <th>  
28.                 @Html.DisplayNameFor(model => model.DepartmentName)  
29.             </th>  
30.             <th>  
31.                 @Html.DisplayNameFor(model => model.DesignationName)  
32.             </th>  
33.             <th>  
34.                 @Html.DisplayNameFor(model => model.Salary)  
35.             </th>  
36.             <th>  
37.                 @Html.DisplayNameFor(model => model.State)  
38.             </th>  
39.             <th>  
40.                 @Html.DisplayNameFor(model => model.City)  
41.             </th>  
42.             <th></th>  
43.         </tr>  
44.     </thead>  
45.     <tbody>  
46. @foreach (var item in Model) {  
47.         <tr>  
48.             <td>  
49.                 @Html.DisplayFor(modelItem => item.EmployeeCode)  
50.             </td>  
51.             <td>  
52.                 @Html.DisplayFor(modelItem => item.EmployeeName)  
53.             </td>  
54.             <td>  
55.                 @Html.DisplayFor(modelItem => item.DateOfBirth)  
56.             </td>  
57.             <td>  
58.                 @Html.DisplayFor(modelItem => item.JoinDate)  
59.             </td>  
60.             <td>  
61.                 @Html.DisplayFor(modelItem => item.DepartmentName)  
62.             </td>  
63.             <td>  
64.                 @Html.DisplayFor(modelItem => item.DesignationName)  
65.             </td>  
66.             <td>  
67.                 @Html.DisplayFor(modelItem => item.Salary)  
68.             </td>  
69.             <td>  
70.                 @Html.DisplayFor(modelItem => item.State)  
71.             </td>  
72.             <td>  
73.                 @Html.DisplayFor(modelItem => item.City)  
74.             </td>  
75.             <td>  
76.                 @Html.ActionLink("Edit", "Edit", new { id = item.Id }) |  
77.                 @Html.ActionLink("Delete", "Delete", new { id = item.Id })  
78.             </td>  
79.         </tr>  
80. }  
81.     </tbody>  
82. </table>  

Step 9 

 

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

1. <header>  
2.         <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">  
3.             <div class="container">  
4.                 <a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">EF_Crud_Samples</a>  
5.                 <button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"  
6.                         aria-expanded="false" aria-label="Toggle navigation">  
7.                     <span class="navbar-toggler-icon"></span>  
8.                 </button>  
9.                 <div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">  
10.                     <ul class="navbar-nav flex-grow-1">  
11.                         <li class="nav-item">  
12.                             <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>  
13.                         </li>  
14.                         <li class="nav-item">  
15.                             <a class="nav-link text-dark" asp-area="" asp-controller="Employees" asp-action="Index">Employee</a>  
16.                         </li>  
17.                         <li class="nav-item">  
18.                             <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>  
19.                         </li>  
20.                     </ul>  
21.                 </div>  
22.             </div>  
23.         </nav>  
24.     </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:

1. public IActionResult Create()  
2.         {  
3.             this.GetModelData();  
4.             return View();  
5.         }  
6.   
7. private void GetModelData()  
8.         {  
9.             ViewBag.Departments = this._dbContext.Departments.ToList();  
10.             ViewBag.Designations = this._dbContext.Designations.ToList();  
11.         }  

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.

1. @model DataContext.Models.Employee  
2.   
3. @{  
4.     ViewData["Title"] = Model != null ? "Edit" : "Create";  
5. }  
6.   
7. <h2>@ViewData["Title"] Employee</h2>  
8.   
9. <hr />  
10. <div class="row">  
11.     <div class="col-lg-12">  
12.         <form asp-action="ViewData[" Title"]">  
13.             <div asp-validation-summary="ModelOnly" class="text-danger"></div>  
14.             <div class="form-group">  
15.                 <div class="form-row">  
16.                     <div class="col-lg-2">  
17.                         <label asp-for="EmployeeCode" class="control-label"></label>  
18.                     </div>  
19.                     <div class="col-lg-4">  
20.                         <input asp-for="EmployeeCode" class="form-control" />  
21.                         <span asp-validation-for="EmployeeCode" class="text-danger"></span>  
22.                     </div>  
23.                     <div class="col-lg-2">  
24.                         <label asp-for="EmployeeName" class="control-label"></label>  
25.                     </div>  
26.                     <div class="col-lg-4">  
27.                         <input asp-for="EmployeeName" class="form-control" />  
28.                         <span asp-validation-for="EmployeeName" class="text-danger"></span>  
29.                     </div>  
30.                 </div>  
31.   
32.                 <div class="form-row">  
33.                     <div class="col-lg-2">  
34.                         <label asp-for="DateOfBirth" class="control-label"></label>  
35.                     </div>  
36.                     <div class="col-lg-4">  
37.                         <input asp-for="DateOfBirth" class="form-control" />  
38.                         <span asp-validation-for="DateOfBirth" class="text-danger"></span>  
39.                     </div>  
40.                     <div class="col-lg-2">  
41.                         <label asp-for="JoinDate" class="control-label"></label>  
42.                     </div>  
43.                     <div class="col-lg-4">  
44.                         <input asp-for="JoinDate" class="form-control" />  
45.                         <span asp-validation-for="JoinDate" class="text-danger"></span>  
46.                     </div>  
47.                 </div>  
48.   
49.                 <div class="form-row">  
50.                     <div class="col-lg-2">  
51.                         <label asp-for="DepartmentId" class="control-label"></label>  
52.                     </div>  
53.                     <div class="col-lg-4">  
54.                         <select asp-for="DepartmentId" asp-items="@(new SelectList(ViewBag.Departments,"DepartmentId","DepartmentName"))" class="form-control">  
55.                             <option value="">--Select--</option>  
56.                         </select>  
57.                         <span asp-validation-for="DepartmentId" class="text-danger"></span>  
58.                     </div>  
59.                     <div class="col-lg-2">  
60.                         <label asp-for="DesignationId" class="control-label"></label>  
61.                     </div>  
62.                     <div class="col-lg-4">  
63.                         <select asp-for="DesignationId" asp-items="@(new SelectList(ViewBag.Designations,"DesignationId","DesignationName"))" class="form-control">  
64.                             <option value="">--Select--</option>  
65.                         </select>  
66.                         <span asp-validation-for="DesignationId" class="text-danger"></span>  
67.                     </div>  
68.                 </div>  
69.   
70.                 <div class="form-row">  
71.                     <div class="col-lg-2">  
72.                         <label asp-for="Salary" class="control-label"></label>  
73.                     </div>  
74.                     <div class="col-lg-4">  
75.                         <input asp-for="Salary" class="form-control" />  
76.                         <span asp-validation-for="Salary" class="text-danger"></span>  
77.                     </div>  
78.                     <div class="col-lg-2">  
79.                     </div>  
80.                     <div class="col-lg-4">  
81.   
82.                     </div>  
83.                 </div>  
84.   
85.                 <div class="form-row">  
86.                     <div class="col-lg-2">  
87.                         <label asp-for="Address" class="control-label"></label>  
88.                     </div>  
89.                     <div class="col-lg-4">  
90.                         <input asp-for="Address" class="form-control" />  
91.                         <span asp-validation-for="Address" class="text-danger"></span>  
92.                     </div>  
93.                     <div class="col-lg-2">  
94.                         <label asp-for="State" class="control-label"></label>  
95.                     </div>  
96.                     <div class="col-lg-4">  
97.                         <input asp-for="State" class="form-control" />  
98.                         <span asp-validation-for="State" class="text-danger"></span>  
99.                     </div>  
100.                 </div>  
101.   
102.                 <div class="form-row">  
103.                     <div class="col-lg-2">  
104.                         <label asp-for="City" class="control-label"></label>  
105.                     </div>  
106.                     <div class="col-lg-4">  
107.                         <input asp-for="City" class="form-control" />  
108.                         <span asp-validation-for="City" class="text-danger"></span>  
109.                     </div>  
110.                     <div class="col-lg-2">  
111.                         <label asp-for="ZipCode" class="control-label"></label>  
112.                     </div>  
113.                     <div class="col-lg-4">  
114.                         <input asp-for="ZipCode" class="form-control" />  
115.                         <span asp-validation-for="ZipCode" class="text-danger"></span>  
116.                     </div>  
117.                 </div>  
118.                 <div class="form-row">  
119.                     <hr />  
120.                 </div>  
121.                 <div class="form-row">  
122.                     <div class="col-lg-3">  
123.                     </div>  
124.                     <div class="col-lg-7">  
125.                         <input type="submit" value="Save" class="btn btn-primary" />  
126.                            
127.                         <a asp-action="Index" class="btn btn-secondary">Cancel</a>  
128.                     </div>  
129.                 </div>  
130.             </div>  
131.         </form>  
132.     </div>  
133. </div>  
134.   
135. @section Scripts {  
136.     @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}  
137. }  

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.

1. [HttpPost]  
2.         public IActionResult Create(Employee model)  
3.         {  
4.             if (ModelState.IsValid)  
5.             {  
6.                 _dbContext.Employees.Add(model);  
7.                 _dbContext.SaveChanges();  
8.                 return RedirectToAction("Index");  
9.             }  
10.             this.GetModelData();  
11.             return View();  
12.         }  

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:

1. public IActionResult Index()  
2.         {  
3.             var employees = (from employee in this._dbContext.Employees  
4.                              join desig in this._dbContext.Designations on employee.DepartmentId equals desig.DesignationId  
5.                              join dept in this._dbContext.Departments on employee.DepartmentId equals dept.DepartmentId  
6.                              select new Employee  
7.                              {  
8.                                  Id = employee.Id,  
9.                                  EmployeeCode = employee.EmployeeCode,  
10.                                  EmployeeName = employee.EmployeeName,  
11.                                  DateOfBirth = employee.DateOfBirth,  
12.                                  JoinDate = employee.JoinDate,  
13.                                  Salary = employee.Salary,  
14.                                  Address = employee.Address,  
15.                                  State = employee.State,  
16.                                  City = employee.City,  
17.                                  ZipCode = employee.ZipCode,  
18.                                  DepartmentId = employee.DepartmentId,  
19.                                  DepartmentName = dept.DepartmentName,  
20.                                  DesignationId = employee.DesignationId,  
21.                                  DesignationName = desig.DesignationName  
22.                              }).ToList();  
23.   
24.             return View(employees);  
25.         }  

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.

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

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.

1. [HttpPost]  
2.         public IActionResult Edit(Employee model)  
3.         {  
4.             if (ModelState.IsValid)  
5.             {  
6.                 _dbContext.Employees.Update(model);  
7.                 _dbContext.SaveChanges();  
8.                 return RedirectToAction("Index");  
9.             }  
10.             this.GetModelData();  
11.             return View("Create", model);  
12.         }  

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.

1. public IActionResult Delete(int id)  
2.         {  
3.             Employee data = this._dbContext.Employees.Where(p => p.Id == id).FirstOrDefault();  
4.             if (data != null)  
5.             {  
6.                 _dbContext.Employees.Remove(data);  
7.                 _dbContext.SaveChanges();  
8.             }  
9.             return RedirectToAction("Index");  
10.         }  

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.

1. Create procedure uspGetEmployee  
2. (  
3.     @p_EmployeeId   INT = 0  
4. )  
5. As  
6. BEGIN  
7.     IF @p_EmployeeId > 0  
8.     BEGIN  
9.         Select * from DBO.Employee WHERE Id= @p_EmployeeId  
10.     END  
11.   
12. END  
13. GO  

Step 7

 

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

1. public Employee GetEmployeeById(int employeeId)  
2.         {  
3.             IQueryable<Employee> data = this.Employees.FromSql<Employee>(  
4.                 "Exec [dbo].uspGetEmployee " +  
5.                     "@p_EmployeeId", new SqlParameter("p_EmployeeId", employeeId));  
6.   
7.             if (data != null)  
8.                 return data.FirstOrDefault();  
9.             else  
10.                 return new Employee();  
11.         }  

Step 8

 

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

1. public IActionResult Edit(int id)  
2.         {               
3.             Employee data = this._dbContext.GetEmployeeById(id);  
4.             this.GetModelData();  
5.             return View("Create", data);  
6.         }  

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.

1. <thead>  
2.         <tr>  
3.             <th style="width:10%;">                                 @Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder }) 
4.             </th>  
5.             <th style="width:20%;">                                 @Html.ActionLink("Employee Name", "Index", new { sortField = "EmployeeName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })  
6.             </th>  
7.             <th style="width:10%;">                                 @Html.ActionLink("Join Date", "Index", new { sortField = "JoinDate", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder }) 
8.             </th>  
9.             <th style="width:15%;">                                 @Html.ActionLink("Department", "Index", new { sortField = "DepartmentName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })             
10.             </th>  
11.             <th style="width:15%;">                                @Html.ActionLink("Designation", "Index", new { sortField = "DesignationName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder }) 
12.             </th>  
13.             <th style="width:10%;">                                 @Html.ActionLink("Salary", "Index", new { sortField = "Salary", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })              </th>  
14.             <th style="width:10%;">                                 @Html.ActionLink("City", "Index", new { sortField = "City", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder }) 
15.             </th>  
16.             <th style="width:10%;"></th>  
17.         </tr>  
18.     </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:

1. public IActionResult Index(string sortField, string currentSortField, string currentSortOrder)  
2.         {  
3.             var employees = this.GetEmployeeList();  
4.             return View(this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder));  
5.         }  
6.   
7. private List<Employee> SortEmployeeData(List<Employee> employees, string sortField, string currentSortField, string currentSortOrder)  
8.         {  
9.             if (string.IsNullOrEmpty(sortField))  
10.             {  
11.                 ViewBag.SortField = "EmployeeCode";  
12.                 ViewBag.SortOrder = "Asc";  
13.             }  
14.             else  
15.             {  
16.                 if (currentSortField == sortField)  
17.                 {  
18.                     ViewBag.SortOrder = currentSortOrder == "Asc" ? "Desc" : "Asc";  
19.                 }  
20.                 else  
21.                 {  
22.                     ViewBag.SortOrder = "Asc";  
23.                 }  
24.                 ViewBag.SortField = sortField;  
25.             }  
26.               
27.             var propertyInfo = typeof(Employee).GetProperty(ViewBag.SortField);  
28.             if (ViewBag.SortOrder == "Asc")  
29.             {  
30.                 employees = employees.OrderBy(s => propertyInfo.GetValue(s, null)).ToList();  
31.             }  
32.             else  
33.             {  
34.                 employees = employees.OrderByDescending(s => propertyInfo.GetValue(s, null)).ToList();  
35.             }  
36.             return employees;  
37.         }  

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.

1. <p>  
2.     <a asp-action="Create">Create New</a>  
3. </p>  
4. @using (Html.BeginForm())  
5. {  
6.     <p>  
7.         Find by Employee Name: @Html.TextBox("SearchString")  
8.         <input type="submit" value="Search" />  
9.     </p>  
10. }  
11. <table class="table">  
12.     <thead>  
13.         <tr>  
14.             <th style="width:10%;">  
15.                 @Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder })  
16.                 @*@Html.DisplayNameFor(model => model.EmployeeCode)*@  
17.             </th>  

Step 2

 

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

1. public IActionResult Index(string sortField, string currentSortField, string currentSortOrder, string SearchString)  
2.        {  
3.            var employees = this.GetEmployeeList();  
4.            if (!String.IsNullOrEmpty(SearchString))  
5.            {  
6.                employees = employees.Where(s => s.EmployeeName.Contains(SearchString)).ToList();  
7.            }  
8.            return View(this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder));  
9.        }  

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

1. using Microsoft.EntityFrameworkCore;  
2. using System;  
3. using System.Collections.Generic;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Threading.Tasks;  
7.   
8. namespace DataContext.Paging  
9. {  
10.     public class PagingList<T> : List<T>  
11.     {  
12.         public int PageIndex { get; private set; }  
13.         public int TotalPages { get; private set; }  
14.   
15.         public PagingList(List<T> items, int count, int pageIndex, int pageSize)  
16.         {  
17.             PageIndex = pageIndex;  
18.             TotalPages = (int)Math.Ceiling(count / (double)pageSize);  
19.   
20.             this.AddRange(items);  
21.         }  
22.   
23.         public bool HasPreviousPage  
24.         {  
25.             get  
26.             {  
27.                 return (PageIndex > 1);  
28.             }  
29.         }  
30.   
31.         public bool HasNextPage  
32.         {  
33.             get  
34.             {  
35.                 return (PageIndex < TotalPages);  
36.             }  
37.         }  
38.   
39.         public int TotalPageNo  
40.         {  
41.             get  
42.             {  
43.                 return TotalPages;  
44.             }  
45.         }  
46.   
47.         public static PagingList<T> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)  
48.         {  
49.             var count = source.Count();  
50.             var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();  
51.             return new PagingList<T>(items, count, pageIndex, pageSize);  
52.         }  
53.     }  
54. }  

Step 3

 

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

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

Step 4

 

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

1. <thead>  
2.        <tr>  
3.            <th style="width:10%;">  
4.                @Html.ActionLink("Code", "Index", new { sortField = "EmployeeCode", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })                  
5.            </th>  
6.            <th style="width:20%;">  
7.                @Html.ActionLink("Employee Name", "Index", new { sortField = "EmployeeName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
8.            </th>  
9.            <th style="width:10%;">  
10.                @Html.ActionLink("Join Date", "Index", new { sortField = "JoinDate", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
11.            </th>  
12.            <th style="width:15%;">  
13.                @Html.ActionLink("Department", "Index", new { sortField = "DepartmentName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
14.            </th>  
15.            <th style="width:15%;">  
16.                @Html.ActionLink("Designation", "Index", new { sortField = "DesignationName", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
17.            </th>  
18.            <th style="width:10%;">  
19.                @Html.ActionLink("Salary", "Index", new { sortField = "Salary", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
20.            </th>  
21.            <th style="width:10%;">  
22.                @Html.ActionLink("City", "Index", new { sortField = "City", currentSortField = ViewBag.SortField, currentSortOrder = ViewBag.SortOrder, currentFilter = ViewBag.CurrentFilter })  
23.            </th>  
24.            <th style="width:10%;"></th>  
25.        </tr>  
26.    </thead>  

Step 5

 

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

1. <br />  
2.   
3. @{  
4.     var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";  
5.     var nextDisabled = !Model.HasNextPage ? "disabled" : "";  
6.     var totalPageNo = Model.TotalPageNo;  
7.     var currentPageNo = Model.PageIndex;  
8. }  
9.   
10. <a asp-action="Index"  
11.    asp-route-sortOrder="@ViewData["CurrentSort"]"  
12.    asp-route-pageNo="@(Model.PageIndex - 1)"  
13.    asp-route-currentFilter="@ViewData["CurrentFilter"]"  
14.    class="btn btn-default @prevDisabled">  
15.     Previous  
16. </a>  
17. <a asp-action="Index"  
18.    asp-route-sortOrder="@ViewData["CurrentSort"]"  
19.    asp-route-pageNo="@(Model.PageIndex + 1)"  
20.    asp-route-currentFilter="@ViewData["CurrentFilter"]"  
21.    class="btn btn-default @nextDisabled">  
22.     Next  
23. </a>  
24. <span>  
25.     Page No : @currentPageNo of @totalPageNo  
26. </span>  

Step 4

 

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

1. public IActionResult Index(string sortField, string currentSortField, string currentSortOrder, string currentFilter, string SearchString,
2.           int? pageNo)  
3.         {  
4.             var employees = this.GetEmployeeList();  
5.             if (SearchString != null)  
6.             {  
7.                 pageNo = 1;  
8.             }  
9.             else  
10.             {  
11.                 SearchString = currentFilter;  
12.             }  
13.             ViewData["CurrentSort"] = sortField;  
14.             ViewBag.CurrentFilter = SearchString;  
15.             if (!String.IsNullOrEmpty(SearchString))  
16.             {  
17.                 employees = employees.Where(s => s.EmployeeName.Contains(SearchString)).ToList();  
18.             }  
19.             employees = this.SortEmployeeData(employees, sortField, currentSortField, currentSortOrder);  
20.             int pageSize = 10;  
21.             return View(PagingList<Employee>.CreateAsync(employees.AsQueryable<Employee>(), pageNo ?? 1, pageSize));  
22.         }  

Step 5

 

Now run the application.