In this article, we will implement Razor Pages CRUD operation with one-to-many relationships between the tables using the Entity Framework Core.Razor Pages:
(4) Here 'wwwroot' folder to store all static files.
(Step 2)
Razor Pages:
Razor Pages is a simplified web application model. Compared with the 'MVC' template, razor pages won't have 'Controllers', which means Razor Page is a combination of 'View' and 'Model'. The route will be configured within the razor page or view. A Razor Page composed with 2 files like '*.cshtml.cs'(Model) & '*.cshtml'(view).
One-To-Many Relationship Table SQL Script:
For this demo, we will create 2 tables 'Employee'(parent table) and 'EmployeeAddresses'(child table). Between the tables, we make a one-to-many relationship which means one employee record can have multiple records in the employee address table.
Employee Table Script:
CREATE TABLE Employee( Id int IDENTITY (1,1) NOT NULL, FirstName varchar (200) , LastName varchar (200) , JobRole varchar(50) CONSTRAINT PK_Employee_Id PRIMARY KEY (Id) )EmployeeAddresses Table Script:
Create Table EmployeeAddresses( Id int IDENTITY (1,1) NOT NULL, City varchar(100), Country varchar(100), EmployeeId int NOT NULL CONSTRAINT PK_EmployeeAddresses_Id PRIMARY KEY (Id) CONSTRAINT FK_Employee_Address_EmployeeId FOREIGN KEY (EmployeeId) REFERENCES Employee (Id) )
- Here 'EmployeeId' is the Foreign Key.
Create A .NET 7 Razor Page Application:
Let's create a .NET 7 Razor Page sample application to accomplish our demo. We can use either Visual Studio 2022 or Visual Studio Code(using .NET CLI commands) to create the .NET 7 applications. For this demo, I'm using the 'Visual Studio Code'(using the .NET CLI commands) editor.
.NET CLI command to create razor project.
CLI command
dotnet new webapp -o Your_Project_Name
dotnet new webapp -o Your_Project_Name
Let's explore the Razor Page project.
(1)In 'Program.cs' registered the 'AddRazorPage()' service for Razor Pages.
(2) In 'Program.cs' let's understand default middleware- (Line: 9-14) Here configured middlewars that needs to be run in other environment(not in development or local environment). The 'UseExceptionHandler' configured with end user friendly error page. The 'UseHsts' helps to signal the client(browser) that only secured requests(HTTPS requests) are accepted.
- (Line:16) The 'UseHttpsRedirection' middleware helps to redirect the non HTTP request to HTTPS
- (Line: 17) The 'UseStaticFiles' middleware to serve the files like 'js', 'css', 'images', etc
- (Line: 19) The 'UseRouting()' enables dotnet core endpoint routing
- (Line: 23) The 'MapRazorPages()' middleware helps to serve the razor pages by its route.
- Here '_Layout' Razor page is our master template that contains common HTML like header & footer.
- Here '_ViewStart' contains path our '_Layout' page.
- Here '_ViewImports' contains global namespaces
- Here 'Error', 'Index', 'Privacy' are default razro pages.
Entity Framework Core:
Entity Framework Core is an Object/Relational Mapping(ORM) framework. EF Core makes database communication more fluent and easy. The 'DatabaseContext' class acts as a database from our c# code, it will contain all registered classes DbSet<TEntity>(TEntity is any POCO class that represents the table).
Install Entity Framework Core NuGet Packages:
Let's install the Entity Framework Core Nuget Packages.
CLI command
dotnet add package Microsoft.EntityFrameworkCore --version 7.0.0
dotnet add package Microsoft.EntityFrameworkCore --version 7.0.0
Package Manager Command
NuGet\Install-Package Microsoft.EntityFrameworkCore -Version 7.0.0
NuGet\Install-Package Microsoft.EntityFrameworkCore -Version 7.0.0
Now install the SQL Server library which is dependent on the Entity Framework Core library
CLI command
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 7.0.0
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 7.0.0
Package Manager Command
NuGet\Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 7.0.0
NuGet\Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 7.0.0
Create Entities With One To Many Relationships:
Let's create 'Employee' & 'EnployeeAddresses' entities in the 'Data/Entities' folders(new folders).
Data/Entities/Employee.cs:
namespace dot7.razor.crudsample.Data.Entities; public class Employee { public int Id { get; set; } public string? FirstName { get; set; } public string? LastName { get; set; } public string? JobRole { get; set; } public List<EmployeeAddresses> EmployeeAddresses { get; set; } }
- (Line: 9) Here 'EmployeeAddresses' property is the navigation property. Here it represents one employee who can have multiple addresses.
namespace dot7.razor.crudsample.Data.Entities; public class EmployeeAddresses { public int Id { get; set; } public string? AddressType { get; set; } public string? City { get; set; } public string? Country { get; set; } public int EmployeeId { get; set; } public Employee Employee { get; set; } }
- (Line: 9) The 'EmployeeId' property will be our foreign key property.
- (Line: 11) The 'Employee' is our navigation property.
Create DatabaseContext:
Let's create the DatabaseContext like 'MyWorldDbContext' in the 'Data' folder.
Data/MyWorldDbContext.cs:
using dot7.razor.crudsample.Data.Entities; using Microsoft.EntityFrameworkCore; namespace dot7.razor.crudsample.Data; public class MyWorldDbContext : DbContext { public MyWorldDbContext(DbContextOptions<MyWorldDbContext> context) : base(context) { } public DbSet<Employee> Employee { get; set; } public DbSet<EmployeeAddresses> EmployeeAddresses{get;set;} protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<EmployeeAddresses>() .HasOne(_ => _.Employee) .WithMany(a => a.EmployeeAddresses) .HasForeignKey(p => p.EmployeeId); } }
- (Line: 7) The 'Microsoft.EntityFramwork.DbContext' needs to be inherited by our 'MyWorldDbContext' to act as a Database Context class.
- (Lin: 9) The 'Microsoft.EntityFrameworkDbContextOptions' is an instance of options that we are going to register in 'Program.cs' like 'Database Provider', 'Connectionstring', etc.
- (Line: 14&16) All our table classes must be registered inside of our database context class with 'DbSet<T>' so that the entity framework can communicate with the table of the database.
- (Line: 20-23) Using EF Core fluent API we are defining our one-to-many relationship between 'Employee' & 'EmployeeAddresses' classes.
appsettings.Development.json:
"ConnectionStrings": { "MyWorldDbConnection":"Data Source=[Your_Server_Name];Initial Catalog=[Your_Database_Name];Integrated Security=True;Connect Timeout=30" }Now register our database context in 'Program.cs'.
Program.cs:
using dot7.razor.crudsample.Data; using Microsoft.EntityFrameworkCore; builder.Services.AddDbContext<MyWorldDbContext>(options => { options.UseSqlServer(builder.Configuration.GetConnectionString("MyWorldDbConnection")); });
Read Operation Fetch Only Employee Table Data:
Let's create a Razor Page file like 'EmployeeIndex.cshtml.cs' & 'EmployeeIndex.cshtml' files in 'Pages/Employee' folder(new folder). Implement the read operation by fetching the 'Employee' table data.
Pages/Employee/EmployeeIndex.cshtml.cs:
using dot7.razor.crudsample.Data; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.EntityFrameworkCore; namespace dot7.razor.crudsample.Pages.Employee; public class EmployeeIndex : PageModel { private readonly MyWorldDbContext _myWorldDbContext; public EmployeeIndex(MyWorldDbContext myWorldDbContext) { _myWorldDbContext = myWorldDbContext; } public List<dot7.razor.crudsample.Data.Entities.Employee> AllEmployees { get; set; } public async Task<IActionResult> OnGetAsync() { AllEmployees = await _myWorldDbContext.Employee.ToListAsync(); return Page(); } }
- (Line: 8) To make our 'EmployeeIndex' class as a Razor Page model it needs to inherit the 'Microsoft.AspNetCore.Mvc.RazorPages.PageModel'.
- (Line: 11) Injected the Database context into our Razor Page model.
- (Line: 16) Declared a variable of a type that is collection 'Employee' to hold the data from the database and then bind the data to the UI.
- (Line: 18-22) The default method executed for the razor page HTTP Get request is 'OnGet' or 'OnGetAsync'. It is always ideal to have one method for each HTTP verbs like 'GET', 'POST'. There is an option to customize the name of the HTTP GET request method then it should be like 'OnGet{YourCustomNae}', or 'OnGet{YourCustomName}Async', but if we customize the method name then we have to specify the custom name as value to the query parameter 'handlre'. So don't give the custom name unless it is required. Here in our 'OnGetAsync' method we are fetching the 'Employee' data from the database and the result assigned to 'AllEmployees' variable.
@page "/employee/index" @model dot7.razor.crudsample.Pages.Employee.EmployeeIndex <div class="container"> <div class="row"> <table class="table table-striped table-hover"> <thead> <tr> <th scope="col">First Name</th> <th scope="col">Last Name</th> <th scope="col">Job Role</th> </tr> </thead> <tbody> @foreach (var emp in Model.AllEmployees) { <tr> <th>@emp.FirstName</th> <td>@emp.LastName</td> <td>@emp.JobRole</td> </tr> } </tbody> </table> </div> </div>
- (Line: 1) Using the '@page' directive we defined our razor page route.
- (Line: 2) Defined our Model
- (Line: 16-23)Looping our data to bind to UI.
Read Operation To Fetch Employee And EmployeeAddresses Table Data:
Let's create the new Razor Page files like 'EmployeeDetails.cshtml' & 'EmployeeDetails.cshtml.cs'. This page displays full details of employee including the collection of addresses.
Pages/Employee/EmployeeDetails.cshtml.cs:
using dot7.razor.crudsample.Data; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.EntityFrameworkCore; namespace dot7.razor.crudsample.Pages.Employee; public class EmployeeDetails : PageModel { private readonly MyWorldDbContext _myWorldDbContext; public EmployeeDetails(MyWorldDbContext myWorldDbContext) { _myWorldDbContext = myWorldDbContext; } public dot7.razor.crudsample.Data.Entities.Employee Employee { get; set; } public async Task<IActionResult> OnGetAsync(int id) { Employee = await _myWorldDbContext.Employee.Include(_ => _.EmployeeAddresses) .Where(_ => _.Id == id).FirstOrDefaultAsync(); return Page(); } }
- (Line: 19-21) The 'Include' from the entity framework library is configured with a navigation property like 'EmployeeAddress'. The 'Include' generate a SQL join query between the 'Employee' & 'EmployeeAddressed' table.
@page "/employee/details" @model dot7.razor.crudsample.Pages.Employee.EmployeeDetails <div class="container"> <div class="row"> <div class="col col-md-6 offset-md-3"> <div class="card"> <div class="card-body"> <h5 class="card-title">@Model.Employee.FirstName @Model.Employee.LastName</h5> <h5 class="card-title">@Model.Employee.JobRole</h5> @foreach (var item in Model.Employee.EmployeeAddresses) { <div class="card"> <div class="card-header"> @item.AddressType Address </div> <div class="card-body"> City - @item.City / Country - @item.Country </div> </div> } </div> </div> </div> </div> </div>
- (Line: 11-21) Looping the employee addresses to bind to UI.
Pages/Employee/EmployeeIndex.cshtml:
<!-- existing code hidden for display purpose --> <table class="table table-striped table-hover"> <thead> <tr> <th scope="col">Actions</th> </tr> </thead> <tbody> @foreach (var emp in Model.AllEmployees) { <tr> <td> <a asp-page="./EmployeeDetails" asp-route-id="@emp.Id">Details</a> </td> </tr> } </tbody> </table>
- (Line: 5) Added a new column like 'Actions'.
- (Line: 13) Added the anchor tag to navigate for the 'EmployeeDetails' page. Here 'asp-page' & 'asp-route-id' are razor tag helpers, for 'asp-page' pass the name of the razor page file and for 'asp-route-id' pass the value for the query parameter 'id'.
Create Operation:
Let's add new Razor Page files like 'EmployeeCreate.cshtml' & 'EmployeeCreate.cshtml.cs' to implement the create opertion.
Pages/Employee/EmployeeCreate.cshtml.cs:
using dot7.razor.crudsample.Data; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; namespace dot7.razor.crudsample.Pages.Employee; public class EmployeeCreate : PageModel { private readonly MyWorldDbContext _myWorldDbContext; public EmployeeCreate(MyWorldDbContext myWorldDbContext) { _myWorldDbContext = myWorldDbContext; } [BindProperty] public dot7.razor.crudsample.Data.Entities.Employee NewEmployee { get; set; } public async Task<IActionResult> OnGetAsync() { return Page(); } public async Task<IActionResult> OnPostAsync() { _myWorldDbContext.Employee.Add(NewEmployee); await _myWorldDbContext.SaveChangesAsync(); return Redirect("index"); } }
- (Line: 14-15) Here 'NewEmployee' property of type 'Employee' is registered with the 'BindProperty' attribute. So the 'BindProperty' attribute makes our 'NewEmployee' property capture the user-entered form data.
- (Line: 22) The 'OnPostAsync' method executes for HTTP Post request(eg: form post)
- (Line: 24) Trying to save our form data. Since 'NewEmployee' reads form data and has a navigation property like 'EmployeeAddress', if form data contains a collection of 'EmployeeAddress' information, on saving 'Employee' data 'EmployeeAddress' data will also be saved.
- (Line: 26) After saving finally redirect back to the index page.
@page "/employee/create" @model dot7.razor.crudsample.Pages.Employee.EmployeeCreate <div class="container"> <div class="row"> <div class="col col-md-6 offset-md-3"> <form method="post"> <legend>Add A Employee</legend> <div class="mb-3"> <label for="txtfirstName" class="form-label">First Name</label> <input asp-for="NewEmployee.FirstName" type="text" class="form-control" id="txtfirstName" /> </div> <div class="mb-3"> <label for="txtlastName" class="form-label">Last Name</label> <input asp-for="NewEmployee.LastName" type="text" class="form-control" id="txtlastName" /> </div> <div class="mb-3"> <label for="txtjobRole" class="form-label">Job Role</label> <input asp-for="NewEmployee.JobRole" type="text" class="form-control" id="txtjobRole" /> </div> @for (int i = 0; i <= 1; i++) { <div class="row"> <legend>Address @i</legend> <div class="mb-3"> <label for="txtCity" class="form-label">Address Type</label> <input asp-for="NewEmployee.EmployeeAddresses[i].AddressType" type="text" class="form-control" /> </div> <div class="mb-3"> <label for="txtCity" class="form-label">City</label> <input asp-for="NewEmployee.EmployeeAddresses[i].City" type="text" class="form-control" /> </div> <div class="mb-3"> <label for="txtCity" class="form-label">Country</label> <input asp-for="NewEmployee.EmployeeAddresses[i].Country" type="text" class="form-control" /> </div> </div> } <button type="submit" class="btn btn-primary">Submit</button> </form> </div> </div> </div>
- (Line: 1) The '@page' directive to define the route
- (Line: 11&15&19) Here we can observe that 'NewEmployee' variable properties are mapped to 'asp-for' tag. The 'asp-for' tag renders as an HTML 'name' attribute that helps to post the form data.
- (Line: 21-41) Here 2 address forms are trying to render by looping it.
- (Line: 28&33&37)Here are our forms for the 'EmployeeAddress' collection and configured with 'asp-for'.
In the 'EmployeeIndex.cshtml' add a link for navigating to 'EmployeeCreate.cshtml'
Pages/Employee/EmployeeIndex.cshtml:
<div class="col col-md-4 offset-md-4"> <a asp-page="./EmployeeCreate" >Add New Employee</a> </div>(Step 1)
(Step 3)
Update Operation:
Let's create new Razor Page files like 'EmployeeUpdate.cshtml.cs' & 'EmployeeUpdate.cshtml'.
Pages/Employee/EmployeeUpdate.cshtml.cs:
using dot7.razor.crudsample.Data; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.EntityFrameworkCore; namespace dot7.razor.crudsample.Pages.Employee; public class EmployeeUpdate : PageModel { private readonly MyWorldDbContext _myWorldDbContext; public EmployeeUpdate(MyWorldDbContext myWorldDbContext) { _myWorldDbContext = myWorldDbContext; } [BindProperty] public dot7.razor.crudsample.Data.Entities.Employee EmployeeToUpdate { get; set; } public async Task<IActionResult> OnGetAsync(int id) { EmployeeToUpdate = await _myWorldDbContext.Employee.Include(_ => _.EmployeeAddresses) .Where(_ => _.Id == id).FirstOrDefaultAsync(); return Page(); } public async Task<IActionResult> OnPostAsync() { _myWorldDbContext.Employee.Update(EmployeeToUpdate); await _myWorldDbContext.SaveChangesAsync(); return Redirect("index"); } }
- (Line: 18-23) The 'OnGetAsync' method is executed for HTTP GET request. Here we fetch the 'Employee' by 'id' and also fetch it 'EmployeeAddress' data to display it on the form. The 'Include' ef-core method loads the child table information
- (Line: 25-30) The 'OnPostAsync' method is executed for HTTP POST requests. The 'Update' method updates both 'Employee' & 'EmployeeAddress' data.
@page "/employee/update" @model dot7.razor.crudsample.Pages.Employee.EmployeeUpdate <div class="container"> <div class="row"> <div class="col col-md-6 offset-md-3"> <form method="post"> <legend>Update Employee</legend> <input type="hidden" asp-for="EmployeeToUpdate.Id"> <div class="mb-3"> <label for="txtfirstName" class="form-label">First Name</label> <input asp-for="EmployeeToUpdate.FirstName" type="text" class="form-control" id="txtfirstName" /> </div> <div class="mb-3"> <label for="txtlastName" class="form-label">Last Name</label> <input asp-for="EmployeeToUpdate.LastName" type="text" class="form-control" id="txtlastName" /> </div> <div class="mb-3"> <label for="txtjobRole" class="form-label">Job Role</label> <input asp-for="EmployeeToUpdate.JobRole" type="text" class="form-control" id="txtjobRole" /> </div> @for (int i = 0; i <= 1; i++) { <div class="row"> <legend>Address @i</legend> <input type="hidden" asp-for="EmployeeToUpdate.EmployeeAddresses[i].Id"> <div class="mb-3"> <label for="txtCity" class="form-label">Address Type</label> <input asp-for="EmployeeToUpdate.EmployeeAddresses[i].AddressType" type="text" class="form-control" /> </div> <div class="mb-3"> <label for="txtCity" class="form-label">City</label> <input asp-for="EmployeeToUpdate.EmployeeAddresses[i].City" type="text" class="form-control" /> </div> <div class="mb-3"> <label for="txtCity" class="form-label">Country</label> <input asp-for="EmployeeToUpdate.EmployeeAddresses[i].Country" type="text" class="form-control" /> </div> </div> } <button type="submit" class="btn btn-primary">Submit</button> </form> </div> </div> </div>
- (Line: 10&28) The only difference between 'create' and update forms are 'Id' values. In the 'Update' form we have to store our 'Id' values in the hidden field for both 'Employee' & 'EmployeeAddress' table
Pages/Employee/EmployeeIndex.cshtml:
<a asp-page="./EmployeeUpdate" asp-route-id="@emp.Id">Update</a>(Step 1)
(Step 2)
Delete Operation:
Let's create new Razor Pages files like 'EmployeDelete.cshtml' & 'EmployeeDelete.cshtml.cs' to implement the delete operation.
Pages/Employee/EmployeeDelete.cshtml.cs:
using dot7.razor.crudsample.Data; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.EntityFrameworkCore; namespace dot7.razor.crudsample.Pages.Employee; public class EmployeeDelete : PageModel { private readonly MyWorldDbContext _myWorldDbContext; public EmployeeDelete(MyWorldDbContext myWorldDbContext) { _myWorldDbContext = myWorldDbContext; } public dot7.razor.crudsample.Data.Entities.Employee Employee { get; set; } public async Task<IActionResult> OnGetAsync(int id) { Employee = await _myWorldDbContext.Employee.Include(_ => _.EmployeeAddresses) .Where(_ => _.Id == id).FirstOrDefaultAsync(); return Page(); } public async Task<IActionResult> OnPostAsync(int id) { var employeeToDelete = await _myWorldDbContext.Employee.Include(_ => _.EmployeeAddresses) .Where(_ => _.Id == id).FirstOrDefaultAsync(); _myWorldDbContext.Employee.Remove(employeeToDelete); await _myWorldDbContext.SaveChangesAsync(); return Redirect("index"); } }
- (Line: 18-23) In 'OnGetAsync' method fetches the item to display on our delete page.
- (Line: 25-34) In 'OnPostAsync' method fetch 'Employee' record along with 'EmployeeAddress' records and then delete by passing them to 'Reomve' method.
@page "/employee/delete" @model dot7.razor.crudsample.Pages.Employee.EmployeeDelete <div class="container"> <div class="row"> <div class="col col-md-6 offset-md-3"> <div class="card"> <div class="card-body"> <h5 class="card-title">@Model.Employee.FirstName @Model.Employee.LastName</h5> <h5 class="card-title">@Model.Employee.JobRole</h5> @foreach (var item in Model.Employee.EmployeeAddresses) { <div class="card"> <div class="card-header"> @item.AddressType Address </div> <div class="card-body"> City - @item.City / Country - @item.Country </div> </div> } </div> <div class="card-body"> <form method="post"> <button type="submit" class="btn btn-primary">Confirm Delete</button> </form> </div> </div> </div> </div> </div>
- (Line: 24-26) Our 'Confirm Delete' button wrapped around a 'form' tag so that on clicking button we will invoke our 'OnPostAsync()' method in 'EmployeeDelete.cshtml.cs' file.
Pages/EmployeeIndex.cshtml:
<a asp-page="./EmployeeDelete" asp-route-id="@emp.Id">Delete</a>
Support Me!
Buy Me A Coffee
PayPal Me
Video Session:
Wrapping Up:
Hopefully, I think this article delivered some useful information on .NET 7 Razor Pages CRUD operations. I love to have your feedback, suggestions, and better techniques in the comment section below.
Comments
Post a Comment