In this post, we will explore the implementation of CRUD (Create, Read, Update, Delete) operations for Applications. As we continue, we will also delve into the world of Code First Migrations and tackle some common issues that can arise during the migration process.
Index for Applications
For a start, we will start building Index page for applications. Later on, we will build the rest of the pages such as Add, Update and Delete pages.
Data Access Layer
Let's add an entity class named Application in Entities folder.
Update content of Application class with the following code:
namespace Portal.Entities;
public class Application
{
public int Id { get; set; }
public string? Name { get; set; }
}
At the moment, the class has 2 properties, Id and Name.
We also need to let our ApplicationDbContext to manage data access to this entity.
In DataAccess\ApplicationDbContext.cs file, add the following line:
public DbSet<Application> Applications { get; set; }
We also need to configure the data model for this entity.
In DataAccess\ApplicationDbContext.cs file, add the following lines in OnModelCreating method:
modelBuilder.Entity<Application>(entity =>
{
entity.ToTable("Applications");
entity.HasKey(e => e.Id);
entity.Property(e => e.Id).ValueGeneratedOnAdd();
entity.Property(e => e.Name).IsRequired().HasMaxLength(255);
});
It will map to a database table named Applications. Name property has a maximum characters of 255.
The final content of ApplicationDbContext.cs as shown below:
UI Layer
Let's add a view model class named ApplicationViewModel in Models folder. The purpose of this class is to display applications in a Razor Page which we will create later.
Update content of ApplicationViewModel class with the following code:
namespace Portal.Models;
public class ApplicationViewModel
{
public int Id { get; set; }
public string? Name { get; set; }
}
Create a new folder named Apps in Pages folder.
Add a new Razor Page named Index in Apps folder.
The content of this Index is very similar to Index from Tenants folder.
Update the content of Index.cshtml.cs with the following code:
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Portal.DataAccess;
using Portal.Models;
namespace Portal.Pages.Apps;
public class IndexModel : PageModel
{
private readonly ApplicationDbContext _context;
public List<ApplicationViewModel> Applications { get; set; } = new();
public IndexModel(ApplicationDbContext context)
{
_context = context;
}
public async Task OnGetAsync()
{
Applications = await _context.Applications.Select(x => new ApplicationViewModel
{
Id = x.Id,
Name = x.Name
}).ToListAsync();
}
}
Update content of Index.cshtml with the following code:
@page
@model IndexModel
@{
ViewData["Title"] = "Applications";
}
<div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
<h1 class="h2">@ViewData["Title"]</h1>
<div class="btn-toolbar mb-2 mb-md-0">
<div class="btn-group me-2">
<a class="btn btn-sm btn-outline-secondary" asp-page="Add">Add</a>
</div>
</div>
</div>
@if (TempData["Message"] != null)
{
<div class="alert alert-primary alert-dismissible fade show" role="alert">
@TempData["Message"]
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
}
<table class="table">
<thead>
<tr>
<th scope="col">Name</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
@foreach (var app in Model.Applications)
{
<tr>
<td>@app.Name</td>
<td>
<a class="btn btn-outline-secondary" asp-page="Edit" asp-route-id="@app.Id">
Edit
</a>
<a class="btn btn-outline-info" asp-page="Delete" asp-page-handler="Delete" asp-route-id="@app.Id" onclick="return confirm('Are you sure?')">
Delete
</a>
</td>
</tr>
}
</tbody>
</table>
Let's not forget to add a link to this new page in the side menu.
Locate Shared\_Layout.cshtml and search for the following code:
<li class="nav-item">
<a class="nav-link" href="#">
Apps
</a>
</li>
Replace with the following code:
<li class="nav-item">
<a class="nav-link" href="/Apps/">
Apps
</a>
</li>
Test Application
Press F5 to run the project.
If we navigate to Applications page, an exception page is thrown as shown below:
The exception is a SqlException type which indicates that this is a database issue.
At first, the message Invalid object name 'Applications' seems too vague to understand the actual issue. Objects in the database can include Stored Procedure, User Function, Table, etc.
However, in our case, it means there is no table called Applications which is true as we haven't figured out how to make changes to the database. If you refer to the previous code, we added this line of code entity.ToTable("Applications"); in ApplicationDbContext class to map Applications table which is yet to exist.
If we refer to our previous post, we have a method named EnsureCreated which creates an empty database. However, it is not able to make changes to an existing database.
There are a few ways to resolve this issue:
Drop and re-create the database. However, we wish to keep the existing data. It is certainly not a recommended way to a production database.
Explicitly modify the schema of the database. It can be done by using a SQL script. But this step is tedious and error-prone as we need to test the change script and make sure it doesn't have side effects such as accidentally modifying or deleting the table schema.
Use Code First Migrations to update the database schema. EF Core will help to auto-generate code and migrate the changes to our database.
As Microsoft recommends using Code First Migrations, we will use this approach to migrate changes to our existing database.
EF Core provides a set of tools which allows us to use in Package Manager Console (PMC) in Visual Studio. To install these tools, please run the following command in PMC:
Install-Package Microsoft.EntityFrameworkCore.Tools
Furthermore, since Microsoft continuously releases new versions of the package, it is advisable to ensure that all EF Core NuGet packages are of the same version.
To check the version of those already installed EF Core packages, right-click the project and select Edit Project File as shown below:
A XML file should appear in Visual Studio, look for something which begins with <PackageReference Include="Microsoft.EntityFrameworkCore... as shown below:
Notice that they are using version 7.0.12.
With this information, we can run the PowerShell command with specified version as shown below:
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 7.0.12
Next, let's run the following command in PMC:
Add-Migration AddApplicationsTable
The command generates 2 files in a newly created folder named Migrations. You can learn more about those files here.
To migrate the changes, run the following following command in PMC:
Update-Database
However, there is an error while running the command.
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Tenants' in the database.
The message indicates that EF Core was trying to create a table named Tenants which already there in our database.
Why?
When we inspect the code for <TimeStamp>_AddApplicationsTable.cs, there are 2 methods called CreateTable. Both methods are trying to create tables for Tenants and Applications. So when EF Core was trying to migrate changes to our database, it tried to re-create the Tenants table which caused the database error as shown above.
To fix this issue, we just need to comment on those lines which are creating and deleting the Tenants table as shown below:
Run the command Update-Database again in PMC and this time we should be able to see the new Applications table along with existing Tenants table.
To verify, open MS SQL Management Studio, and locate our database. You should see a new table named dbo.Applications created.
Notice that there is a new table named dbo.__EFMigrationsHistory. This table contains the historical migration information that we performed on this database.
Let's run the project again by pressing F5.
This time we should be able to load the Index page for Applications which were fetched from our database.
The table has no application which is normal as we haven't added any application to our database. We will implement the Add button functionality to the project in the next section.
Add for Applications
To add a new application, we need to create 2 files which are AddApplicationViewModel.cs and Apps\Add.cshtml.
In Models folder, create a new class file named AddApplicationViewModel.cs.
Update content of AddApplicationViewModel.cs with the following code:
using System.ComponentModel.DataAnnotations;
namespace Portal.Models;
public class AddApplicationViewModel
{
[Required]
public string? Name { get; set; }
}
Note that we assign RequiredAttribue to Name property to make it a mandatory field.
Next, create a Razor Page named Add in Pages\Apps folder.
Update content of Add.cshtml.cs with the following code:
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Portal.DataAccess;
using Portal.Models;
namespace Portal.Pages.Apps;
public class AddModel : PageModel
{
private readonly ApplicationDbContext _context;
[BindProperty]
public AddApplicationViewModel Application { get; set; } = new();
public AddModel(ApplicationDbContext context)
{
_context = context;
}
public IActionResult OnPost()
{
if (!ModelState.IsValid)
{
return Page();
}
_context.Applications.Add(new Entities.Application
{
Name = Application.Name,
});
_context.SaveChanges();
TempData["Message"] = $"Application {Application.Name} added successfully.";
return RedirectToPage("Index");
}
}
Update content of Add.cshtml with the following code:
@page
@model AddModel
@{
ViewData["Title"] = "Add Application";
}
<div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
<h1 class="h2">@ViewData["Title"]</h1>
<div class="btn-toolbar mb-2 mb-md-0">
<div class="btn-group me-2">
<a class="btn btn-sm btn-outline-secondary" asp-page="Index">Back</a>
</div>
</div>
</div>
<form id="addAppForm" method="post">
<div class="mb-3 col-4">
<label asp-for="Application.Name">Name</label>
<input class="form-control" asp-for="Application.Name">
<span asp-validation-for="Application.Name" class="text-danger"></span>
</div>
<div class="col-6 text-right">
<input id="addApp" type="submit" class="btn btn-sm btn-outline-primary" value="Save" />
</div>
</form>
@section Scripts {
<partial name="_ValidationScriptsPartial" />
<script>
$(function () {
$('#addApp').on('click', function () {
$('#addAppForm').validate();
if (($('#addAppForm').valid() === false)
return false;
return true;
});
});
</script>
}
That's it.
Press F5 to run the project. Go and click Add button to load Razor Page Add as shown below:
Enter a name for the new application and click Save button. A success message should appear on top of the table as shown below:
Edit for Application
To edit an application, we need to create 2 files.
Create a class file named EditApplicationViewModel.cs in Models folder and update the content of the file with the following code:
using System.ComponentModel.DataAnnotations;
namespace Portal.Models;
public class EditApplicationViewModel
{
[Required]
public int Id { get; set; }
[Required]
public string? Name { get; set; }
}
Create a Razor Page named Edit in Pages\Apps folder.
Update content of Edit.cshtml.cs with the following code:
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Portal.DataAccess;
using Portal.Models;
namespace Portal.Pages.Apps;
public class EditModel : PageModel
{
private readonly ApplicationDbContext _context;
[BindProperty(SupportsGet = true)]
public int Id { get; set; }
[BindProperty]
public EditApplicationViewModel Application { get; set; } = new();
public EditModel(ApplicationDbContext context)
{
_context = context;
}
public void OnGet()
{
var application = _context.Applications.Find(Id);
if (application is null)
{
TempData["Message"] = "Application not found!";
RedirectToPage("Index");
}
Application = new EditApplicationViewModel
{
Id = application!.Id,
Name = application.Name
};
}
public IActionResult OnPost()
{
if (!ModelState.IsValid)
{
return Page();
}
var application = _context.Applications.Find(Id);
if (application is null)
{
TempData["Message"] = "Application not found!";
RedirectToPage("Index");
}
application!.Name = Application.Name!;
_context.Entry(application).State = EntityState.Modified;
_context.SaveChanges();
TempData["Message"] = $"Application {application.Name} updated successfully.";
return RedirectToPage("Index");
}
}
Update content of Edit.cshtml file with the following code:
@page "{id:int}"
@model EditModel
@{
ViewData["Title"] = "Edit Application";
}
<div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
<h1 class="h2">@ViewData["Title"]</h1>
<div class="btn-toolbar mb-2 mb-md-0">
<div class="btn-group me-2">
<a class="btn btn-sm btn-outline-secondary" asp-page="Index">Back</a>
</div>
</div>
</div>
<form id="editAppForm" method="post">
<div class="mb-3 col-4">
<label asp-for="Application.Name">Name</label>
<input class="form-control" asp-for="Application.Name">
<span asp-validation-for="Application.Name" class="text-danger"></span>
</div>
<div class="col-6 text-right">
<input id="saveApp" type="submit" class="btn btn-sm btn-outline-primary" value="Save" />
<a class="btn btn-sm btn-outline-secondary" asp-page="Delete" asp-page-handler="Delete" asp-route-id="@Model.Id" onclick="return confirm('Are you sure?')">Delete</a>
</div>
</form>
@section Scripts {
<partial name="_ValidationScriptsPartial" />
<script>
$(function () {
$('#saveApp').on('click', function () {
$('#editAppForm').validate();
if (($('#editAppForm').valid() === false)
return false;
return true;
});
});
</script>
}
Press F5 to run the project.
Click Edit button on one of the table rows in Applications page.
Change the name of the application and click Save button to update.
A success message should appear on top of the page like below:
Application <Name> updated successfully.
Delete for Applications
To delete an application, we just need to add a Razor Page named Delete.
Create a Razor Page named Delete in Pages\Apps folder.
Update content of Delete.cshtml.cs file with the following code:
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Portal.DataAccess;
namespace Portal.Pages.Apps;
public class DeleteModel : PageModel
{
private readonly ApplicationDbContext _context;
[BindProperty(SupportsGet = true)]
public int Id { get; set; }
public DeleteModel(ApplicationDbContext context)
{
_context = context;
}
public IActionResult OnGetDelete()
{
var application = _context.Applications.Find(Id);
if (application is null)
TempData["Message"] = "Application not found!";
_context.Applications.Remove(application!);
_context.SaveChanges();
TempData["Message"] = "Application deleted successfully.";
return RedirectToPage("Index");
}
}
That's it. Press F5 to run the project.
Click Delete button one of the table rows in Applications page.
A success message should appear on top of the page like below:
Application deleted successfully.
Conclusion
In conclusion, we've successfully explored the implementation of CRUD operations for Applications in our .NET Core project. Additionally, we've gained valuable insights into the world of Code First Migrations and how to handle migration challenges. Armed with this knowledge, you're now well-prepared to build and manage your .NET Core applications effectively.