How to Enforce ON DELETE NO ACTION in ASP.NET Core Identity

Back to: ASP.NET Core Identity Tutorials

How to Enforce ON DELETE NO ACTION in ASP.NET Core Identity

In this article, I will discuss How to Enforce ON DELETE NO ACTION in ASP.NET Core Identity tables with Examples. Please read our previous article discussing How to Delete Identity Users in ASP.NET Core. At the end of this article, you will understand how to enforce foreign key constraint referential integrity ON DELETE NO ACTION in the entity framework core.

Relationship Between AspNetUsers and AspNetRoles Identity Tables:

The ASP.NET Core Identity maintains many-to-many relationships between AspNetUsers and AspNetRoles tables by maintaining a joining table called AspNetUserRoles.

The users are stored in the AspNetUsers table, and roles are stored in the AspNetRoles table. The User and Role mapping data is stored in the AspNetUserRoles table. This AspNetUserRoles table has just 2 columns, UserId and RoleId, and both are foreign keys pointing to the Id column of AspNetUsers and AspNetRoles tables. For a better understanding, please have a look at the following diagram.

Default Behavior in ASP.NET Core Identity:

The Default Delete Behavior for foreign key relationships in EF Core is Cascade Delete, which means that if a record is deleted from the parent table, all related records will also be deleted from the child table.

In ASP.NET Core Identity, the foreign keys in the AspNetUserRoles table have Cascade DELETE behavior by default. That means if a record in the parent table (AspNetUsers and AspNetRoles) is deleted, the corresponding records in the child table (AspNetUserRoles) are automatically deleted.

If you verify the AspNetUserRoles table Create SQL Script, then you will see the following two constraints are created with the ON DELETE CASCADE option.

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] 
FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] 
FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE

Note: Cascading Referential Integrity Constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign key points. Please check our Cascading Referential Integrity Constraint in SQL Server article to learn more about Cascading Referential Integrity Constraints.

How Do We Enforce the ON DELETE NO ACTION in ASP.NET Core Identity?

Enforcing the ON DELETE NO ACTION in ASP.NET Core Identity involves configuring the Entity Framework Core model to prevent cascading deletes, which ensures that an error is thrown if a delete operation is attempted on a record that has dependent records in other tables. This is important in applications where the integrity of relationships between tables needs to be strictly maintained.

Now, we do not want the role to be deleted from the parent table (AspNetRoles) if the row is referenced in the child table (AspNetUserRoles). To do so, we need to enforce ON DELETE NO ACTION on the foreign keys of the AspNetUserRoles table. 

So, we need to modify the Fluent API configuration to change the delete behavior of the AspNetUserRoles table, i.e., the model that is representing the AspNetUserRoles table. This will be done in the OnModelCreating method of the DbContext class, where we configure the models. So, modify the ApplicationDbContext class as follows:

using Microsoft.AspNetCore.Hosting.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System.Reflection.Emit;

namespace ASPNETCoreIdentityDemo.Models
{
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            // Customizing the ASP.NET Identity model and overriding the defaults if needed

            builder.Entity<IdentityUserRole<string>>()
                   .HasOne<ApplicationRole>()
                   .WithMany()
                   .HasForeignKey(ur => ur.RoleId)
                   .OnDelete(DeleteBehavior.NoAction);

            //The following code will set ON DELETE NO ACTION to all Foreign Keys
            //foreach (var foreignKey in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
            //{
            //    foreignKey.DeleteBehavior = DeleteBehavior.NoAction;
            //}
        }
    }
}

In this code, IdentityUserRole<string> represents the join table entity. The HasOne and WithMany methods configure the relationships, and OnDelete(DeleteBehavior.NoAction) sets the delete behavior to no action, which will prevent the parent record from being deleted if it has an existing child record.

Generate the Migration and Update the Database

After making changes to the context class, we need to create a new Entity Framework Core Migration and update the database schema to reflect these changes. So, open the Package Manager Console and Execute the following add-migration and update-database commands. You can give any name to your migration. Here, I am giving IdentityMigration4. The name that you are giving it should not be given earlier.

If you verify the database table, you will not see any delete behavior for the RoleId foreign key. The reason is the default behavior is No Action in SQL Server. But you can see the ON DELETE CASCADE as the delete behavior for the UserId foreign key.

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
GO

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE

Testing the Application:

With these changes in place, you cannot delete a role if any entry exists in the AspNetUserRoles table, and the DELETE action will be rolled back. You have to delete the CHILD rows before deleting the parent row. At this point, if you try to delete a role assigned to at least one user, the delete operation will be rolled back, and you will get the following error message: The delete statement conflicted with the Reference Constraint.

The above error message does not look good. Instead of the above error message, we need to show a user-friendly error page saying you cannot delete the role. When we delete a Role, the following DeleteRole Post Action Method is invoked.

[HttpPost]
public async Task<IActionResult> DeleteRole(string roleId)
{
    var role = await _roleManager.FindByIdAsync(roleId);
    if (role == null)
    {
        // Role not found, handle accordingly
        ViewBag.ErrorMessage = $"Role with Id = {roleId} cannot be found";
        return View("NotFound");
    }

    var result = await _roleManager.DeleteAsync(role);
    if (result.Succeeded)
    {
        // Role deletion successful
        return RedirectToAction("ListRoles"); // Redirect to the roles list page
    }

    foreach (var error in result.Errors)
    {
        ModelState.AddModelError("", error.Description);
    }

    // If we reach here, something went wrong, return to the view
    return View("ListRoles", await _roleManager.Roles.ToListAsync());
}

If there are users in the role, this action throws an exception. If there is an unhandled exception and the application runs in the Production or Staging environment, we need to be redirected to the ErrorController, which displays the custom Error view. The UseExceptionHandler middleware redirects the request to the Error controller. Let us proceed and implement this.

Creating Error Controller:

Create a new MVC Empty Controller named ErrorController.cs and copy and paste the following code.

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Diagnostics;
using Microsoft.AspNetCore.Mvc;

namespace ASPNETCoreIdentityDemo.Controllers
{
    public class ErrorController : Controller
    {
        private readonly ILogger<ErrorController> logger;

        public ErrorController(ILogger<ErrorController> logger)
        {
            this.logger = logger;
        }

        [AllowAnonymous]
        [Route("Error")]
        public IActionResult Error()
        {
            var exceptionHandlerPathFeature = HttpContext.Features.Get<IExceptionHandlerPathFeature>();

            logger.LogError($"The Path {exceptionHandlerPathFeature?.Path} " +
                $"Threw an Exception {exceptionHandlerPathFeature?.Error}");

            return View("Error");
        }
    }
}

Creating Error View:

Next, create a view named Error.cshtml within the /Views/Error directory and copy and paste the following code. This view will be displayed when an unhandled exception occurs in your application.

@{
    ViewData["Title"] = "Error";
}

<h3>
    An Error occured while processing your request.
    The support team is notified and we are working on the fix
</h3>
<h5>In case any urgent, please contact us on info@dotnettutorials.net</h5>

Next, we need to configure the app.UseExceptionHandler(“/Error”) middleware to handle the unhandled exception. If there is an unhandled exception and the application runs in the Production or Staging environment, the user is redirected to ErrorController, which displays the custom Error view. UseExceptionHandler middleware redirects the request to the Error controller. So, modify the Program.cs class file as follows:

using ASPNETCoreIdentityDemo.Models;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using System;

namespace ASPNETCoreIdentityDemo
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllersWithViews();

            //Configure Entity Framework Core
            var connectionString = builder.Configuration.GetConnectionString("SQLServerIdentityConnection") ?? throw new InvalidOperationException("Connection string 'SQLServerIdentityConnection' not found.");
            builder.Services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(connectionString));

            //Configuration Identity Services
            //Configurre the Custom User and Role Class
            builder.Services.AddIdentity<ApplicationUser, ApplicationRole>(
                options =>
                {
                    // Password settings
                    options.Password.RequireDigit = true;
                    options.Password.RequiredLength = 8;
                    options.Password.RequireNonAlphanumeric = true;
                    options.Password.RequireUppercase = true;
                    options.Password.RequireLowercase = true;
                    options.Password.RequiredUniqueChars = 4;

                    // Other settings can be configured here
                })
                .AddEntityFrameworkStores<ApplicationDbContext>();

            // Configure the Application Cookie settings
            builder.Services.ConfigureApplicationCookie(options =>
            {
                // If the LoginPath isn't set, ASP.NET Core defaults the path to /Account/Login.
                options.LoginPath = "/Account/Login"; // Set your login path here

                // If the AccessDenied isn't set, ASP.NET Core defaults the path to /Account/AccessDenied
                options.AccessDeniedPath = "/Account/AccessDenied"; // Set your access denied path here
            });

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (app.Environment.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
                app.UseStatusCodePagesWithReExecute("/Error/{0}");
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            //Configuring Authentication Middleware to the Request Pipeline
            app.UseAuthentication();
            app.UseAuthorization();
            app.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");

            app.Run();
        }
    }
}

With the above changes in place, set the environment as either Production or Staging, run the application, and try to delete a role assigned to at least one user. You should get the following error view.

This is a very general error view. When we try to delete a Role and cannot do it because users are in the role, we want to pass more information to the view so the end user knows what exactly went wrong. We want the custom Error view to look like the following.

For this, modify the DeleteRole Post action method of the AdministrationController as shown below.

[HttpPost]
public async Task<IActionResult> DeleteRole(string roleId)
{
    var role = await _roleManager.FindByIdAsync(roleId);
    if (role == null)
    {
        // Role not found, handle accordingly
        ViewBag.ErrorMessage = $"Role with Id = {roleId} cannot be found";
        return View("NotFound");
    }
    else
    {
        // Wrap the code in a try/catch block
        try
        {
            var result = await _roleManager.DeleteAsync(role);
            if (result.Succeeded)
            {
                // Role deletion successful
                return RedirectToAction("ListRoles"); // Redirect to the roles list page
            }

            foreach (var error in result.Errors)
            {
                ModelState.AddModelError("", error.Description);
            }

            // If we reach here, something went wrong, return to the view
            return View("ListRoles", await _roleManager.Roles.ToListAsync());
        }
        // If the exception is DbUpdateException, we know we are not able to
        // delete the role as there are users in the role being deleted
        catch (DbUpdateException ex)
        {
            // Log the exception to a file. 
            ViewBag.Error = ex.Message;

            // Pass the ErrorTitle and ErrorMessage that you want to show to the user using ViewBag.
            // The Error view retrieves this data from the ViewBag and displays to the user.
            ViewBag.ErrorTitle = $"{role.Name} Role is in Use";
            ViewBag.ErrorMessage = $"{role.Name} Role cannot be deleted as there are users in this role. If you want to delete this role, please remove the users from the role and then try to delete";
            return View("Error");
            throw;
        }
    }
}

Modify the Error View:

Next, modify the Error.cshtml view file that is present inside the /Views/Shared folder as follows. There is no error-specific information if the ErrorTitle property on the ViewBag is null. So, display the generic error message. Otherwise, display the error-specific data in ErrorTitle and ErrorMessage properties on the ViewBag object.

@{
    ViewData["Title"] = "Error";
}

@if (ViewBag.ErrorTitle == null)
{
    <h3 class="text-danger">
        An Error occured while processing your request.
        The support team is notified and we are working on the fix
    </h3>
    <h5>In case any urgent, please contact us on info@dotnettutorials.net</h5>
}
else
{
    <h1 class="text-danger">@ViewBag.ErrorTitle</h1>
    <h6 class="text-danger">@ViewBag.ErrorMessage</h6>
}

With the above changes in place, run the application and try to delete a role assigned to at least one user. You should get the following error view.

Points to Remember:

• Error Handling: Since enforcing NO ACTION can lead to exceptions when deleting records with dependencies, it’s essential to handle these exceptions in your application to provide a clear message to the user.
• Data Integrity: You need to properly determine where NO ACTION is appropriate and where alternative strategies like SetNull or manual handling of dependent data might be better.
• Performance Impacts: Adding foreign key constraints with NO ACTION can impact database performance. Ensure that these constraints are necessary and test the performance implications for your specific use case.

How ON DELETE NO ACTION Impact Performance?

When a foreign key is set with NO ACTION, the database engine must ensure that any DELETE or UPDATE operations do not violate the foreign key constraint. This means that every time a DELETE or UPDATE operation is performed on the parent table, the database must check whether there are any dependent records in the child table that would result in a foreign key constraint violation.

In the next article, I will discuss Managing User Roles in ASP.NET Core Identity. In this article, I explain How to Enforce ON DELETE NO ACTION in ASP.NET Core Identity. I hope you enjoy this article, How to Enforce ON DELETE NO ACTION in ASP.NET Core Identity.