Sample Code to Create, Read, Update and Delete (CRUD) records, EF Core Many to Many relationship

Melvin Zacharias
7 min readApr 26, 2021

This article explains how CRUD(Create, read, update, delete) operations can be performed on tables connected through a many-to-many relationship. I am using an ASP.NET Core Razor Page application with SQL Server 2019 to demonstrate this. The complete code related to this article can be downloaded from GitHub. In this article, we will use two entities named Employees and Skills as an example of a many-to-many relationship. A skill can be associated with more than one employee, and one employee can have more than one skill. If you just want to view the CRUD code, you can directly go to the following sections as well.

To begin with, create a razor page web application using visual studio 2019. Once the project is ready, add two folders named Data and Models in the root directory. We will create entity class files in the Models folder and our DbContext class will be added to the Data folder. Next, install Microsoft.EntityFrameworkCore. SqlServer and Microsoft.EntityFrameworkCore.Tools packages to the application. Add two class files named Employee and Skill to the Model folder and copy the following code there.

public class Employee
{
public int Id { get; set; }
[MaxLength(50)]
[Required]
[Display(Name = "First Name")]
public string FirstName { get; set; }
[MaxLength(50)]
[Required]
[Display(Name = "Last Name")]
public String LastName { get; set; }
public IList<Employeeskill> Employeeskills { get; set; }

}
public class Skill
{
public int Id { get; set; }
[MaxLength(50)]
public string SkillName { get; set; }
public IList<Employeeskill> Employeeskills { get; set; }
}

A joining table is necessary to create a many-to-many relationship. For that, add a class named Employeeskill to the Model folder and copy the following code there.

[Table("EmployeeSkills")]
public class Employeeskill
{
public Employee Employees { get; set; }
public int EmployeeId { get; set; }
public Skill Skills { get; set; }
public int SkillId { get; set; }
}

Make sure that you import the System.ComponentModel.DataAnnotations namespace in all the models. Next, create a class named AppDbContext in the Data folder and copy the following code there.

public class AppDbContext:DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options):base(options)
{ }
public DbSet<Employee> Empployees { get; set; }
public DbSet<Skill> Skills { get; set; }
public DbSet<Employeeskill> EmployeeSkills { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employeeskill>().HasKey(ES => new { ES.EmployeeId, ES.SkillId });
modelBuilder.Entity<Skill>().HasData(
new Skill {Id=1, SkillName = "Communication" },
new Skill {Id=2, SkillName = "Decision-Making" },
new Skill { Id = 3, SkillName = "Flexibility" },
new Skill { Id = 4, SkillName = "Innovation" },
new Skill { Id = 5, SkillName = "Integrity" },
new Skill { Id = 6, SkillName = "Leadership" },
new Skill { Id = 7, SkillName = "Time Management" },
new Skill { Id = 8, SkillName = "Negotiation" }
);
}
}

I have added a few skills to the skill table inside the OnModelCreating method so that we have some prepopulated values there.

We need to add a connection string in the appsettings.json file and register AppDbContext for dependency injection. Copy the following connection string to the appsettings.json file.

"Connectionstrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=EFCoreMToM;Trusted_Connection=True;MultipleActiveResultSets=true"
},

Copy the following code to ConfigureServices method in Startup.cs file. You are now ready to do the migration.

services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer( Configuration.GetConnectionString("DefaultConnection")));

Go to the Package Manager Console and run the following commands (Packet Manager Console can be accessed from Tools -> NuGet Package Manager -> Package Manager Console menu in visual studio).

add-migration CreateTablesupdate-database

Once the migration is done, the tables related to the entities will be created in the database.

The next step is generating the pages for CRUD using scaffolding. For this, right-click on the Pages folder and select Add->New Scaffolded Item->Razor Pages Using Entity Framework (CRUD) option. In the next window, select Employee Model from the Model Class dropdown and Select AppDbContext from the Data Context class dropdown and click Add button. Visual Studio will generate pages for CRUD the entities.

Create a new record

Create.cshtml file has the autogenerated code to add a new Employee record. It has to be customized to add a checkbox list for displaying the skills records from the Skill table. A textbox is also needed there to add a new skill. A collection of type IList<SelectListItem> is used to hold the skill name and its Ids. The skills are retrieved from the database and assign to the SkillList collection inside the OnGet Method of the PageModel class.

[BindProperty]
public IList<SelectListItem> SkillList { get; set; }
[BindProperty]
[MaxLength(50)]
[Display(Name = "Add a New Skill")]
public String NewSkill { get; set; }
public IActionResult OnGet()
{
SkillList = _context.Skills.ToList<Skill>().Select(m=>new SelectListItem {Text= m.SkillName,Value=m.Id.ToString()}).ToList<SelectListItem>();
return Page();
}

The following razor markup is used to generate the checkboxes. A hidden textbox is mandatory when a tag helper is used to generate the checkboxes. Without that, you won’t be able to identify if a checkbox is checked or not when the form is submitted.

<div class="form-group">
@{ for (int i = 0; i < Model.SkillList.Count; i++)
{
<input asp-for="@Model.SkillList[i].Value" type="hidden" />
{
<input asp-for="@Model.SkillList[i].Selected" /> @Model.SkillList[i].Text
}
<br />
}
}
</div>

When the form is submitted, we loop through the skill list collection and get the selected skill Ids and assign it to IList<Employeeskill> Employeeskills collection. This collection is then assigned to Employee entity instance. If a new skill is added, a new instance of the Skill entity is created, and that is assigned to a new instance of Employeeskills entity. It is then added to IList<Employeeskill> Employeeskills collection and assigned to the Employee entity. Check the following code, which saves a new employee record when the form is submitted.

public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
IList<Employeeskill> Employeeskills=new List<Employeeskill>();

foreach (SelectListItem skill in SkillList)
{
if (skill.Selected)
{
Employeeskills.Add(new Employeeskill {SkillId= Convert.ToInt32(skill.Value)});
}
}
//checking if a new skills was added or not
if(!string.IsNullOrEmpty(NewSkill))
{
//when a new skill is added, create a new skill instance and assign it to and EmployeeSkill entity.
//It is then assigned to a collection of Employeeskills
Skill skill = new Skill { SkillName = NewSkill};
Employeeskill employeeskill = new Employeeskill { Skills = skill };
Employeeskills.Add(employeeskill);
}
//The collection of Employeeskills is assigned to the Employee entity and saved to the database
Employee.Employeeskills = Employeeskills;
_context.Empployees.Add(Employee);
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}

Edit a record

The code on the Edit page (Edit.cshtml) is similar to the code on the create page. But here, when the page loads, we need to load the employee record based on the Id value and select the skills associated with it. Both these are done in the OnGetAsync method, as shown below.

public async Task<IActionResult> OnGetAsync(int? id)
{
if (id == null)
{
return NotFound();
}
Employee = await _context.Empployees.Include(m => m.Employeeskills)
.FirstOrDefaultAsync(m => m.Id == id);
SkillList = _context.Skills.ToList<Skill>().Select(m => new SelectListItem
{
Text = m.SkillName,
Value = m.Id.ToString(),
Selected = Employee.Employeeskills.Any(S => S.SkillId == m.Id) ? true : false
}).ToList<SelectListItem>();
if (Employee == null)
{
return NotFound();
}
return Page();
}

The following code shows how the edited record is saved. When the page is submitted, the employee record is retrieved from the database and assign the modified values to it, and saves to the database. Here, two collections named SelectedSkill and SkillsToRemove are used to hold the values of newly assigned skills and unassigned skills, respectively. Entries in SkillsToRemove collection are removed using the code _context.RemoveRange(SkillsToRemove) and newly assigned skills are added to the EmployeeSkills collection. It is then updated to the database by calling the SaveChangesAsync method.

public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
Employee EmployeeFromDB = await _context.Empployees.
Include(m => m.Employeeskills).FirstOrDefaultAsync(m => m.Id == Employee.Id);
IList<Employeeskill> Employeeskills = new List<Employeeskill>();
//variable to hold removed skills
IList<Employeeskill> SkillsToRemove = new List<Employeeskill>();
//variable to hold newly added skills
IList<Employeeskill> SkillsToAdd = new List<Employeeskill>();
foreach (SelectListItem skill in SkillList)
{
if (skill.Selected)
{
// Add all the selected skills to Employeeskills collection.
Employeeskills.Add(new Employeeskill
{ EmployeeId = Employee.Id, SkillId = Convert.ToInt32(skill.Value) });
//if a new skill is assigned to the employee it is added
//to the SkillsToAdd collection
Employeeskill selectedSkill = EmployeeFromDB.Employeeskills.
Where(m => m.SkillId == Convert.ToInt32(skill.Value)).FirstOrDefault();
if (selectedSkill==null)
{
SkillsToAdd.Add(new Employeeskill
{ EmployeeId = Employee.Id, SkillId = Convert.ToInt32(skill.Value) });

}
}
}
//If a skill is not in the edited skill list, but present
//in the skill list from the DB, it is added to
// the SkillsToRemove collection.
foreach (Employeeskill employeeskill in EmployeeFromDB.Employeeskills)
{
if (Employeeskills.Any(e => e.EmployeeId == employeeskill.EmployeeId
&& e.SkillId == employeeskill.SkillId) == false)
{
SkillsToRemove.Add(employeeskill);
}
}
//Section which assigns the modified values
//to the employee entity from the database
EmployeeFromDB.FirstName = Employee.FirstName;
EmployeeFromDB.LastName = Employee.LastName;
//Delete the skills which are to be removed
_context.RemoveRange(SkillsToRemove);
//Adding newly assigned skills
foreach (var empSKill in SkillsToAdd)
{
EmployeeFromDB.Employeeskills.Add(empSKill);
}
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!EmployeeExists(Employee.Id))
{
return NotFound();
}
else
{
throw;
}
}
return RedirectToPage("./Index");
}

Read a record

Details.cshtml razor page is used to display employee details based on the Id passed to it. The following code is used to get all the details of an employee record, including the skills assigned to it.

public async Task<IActionResult> OnGetAsync(int? id)
{
if (id == null)
{
return NotFound();
}
Employee = await _context.Empployees.Include(m=>m.Employeeskills).ThenInclude(s=>s.Skills).AsNoTracking().FirstOrDefaultAsync(m => m.Id == id);
if (Employee == null)
{
return NotFound();
}
return Page();
}

The skills assigned to the employee are displayed comma-separated using the following razor markup and C# code.

<dd class="col-sm-10">
@{
string skills = "";
skills = string.Join(", ", Model.Employee.Employeeskills.Select(m => m.Skills.SkillName));
<span>@skills</span>
}
</dd>

Delete a record

The following code is used to delete an employee record. As cascade delete is enabled by default, the associated records in the EmployeeSkills table will automatically be deleted when an employee record is deleted.

public async Task<IActionResult> OnPostAsync(int? id)
{
if (id == null)
{
return NotFound();
}
Employee = await _context.Empployees.FindAsync(id);if (Employee != null)
{
_context.Empployees.Remove(Employee);
await _context.SaveChangesAsync();
}
return RedirectToPage("./Index");
}

Originally published at https://mycodingtips.com.

--

--

Melvin Zacharias
0 Followers

I am a software engineer with more than 15 years of work experience in all aspects of software development. Link to my personal site https://mycodingtips.com/