I am trying to implement a server side validation for email, where email field will only take unique value and to implement it I used fluent API for unique email which is like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Manager>()
            .HasIndex(b => b.Email)
            .IsUnique();
}

And now whenever I try to enter duplicate email it throws an exception like this:

SqlException: Cannot insert duplicate key row in object 'dbo.Managers' with unique index 'IX_Managers_Email'. The duplicate key value is ([email protected]). The statement has been terminated.

How can I stop this and instead letting compiler throw this exception, I make it throw a server side validation or is there another way to do it? Please guide.

Plus adding Manager model as well in case if it is needed

namespace Office.Models
{
    [Index (nameof(Email), IsUnique=true)]
    public class Manager
    {
        [Key]
        public int Id { get; set; }
        [Required]
        [DisplayName("Manager Name")]
        public string ManagerName { get; set; }
        [Required]
        [StringLength(450)]
        public string Email { get; set; }
        [Required]
        //[RegularExpression(@"^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,}$", ErrorMessage = "Password type is not valid include Capital, Small, number and Special Char and length should be Min 8")]
        public string Password{ get; set; }

        public string Role { get; set; } = "2";
        public DateTime JoiningDate { get; private set; }

        public Manager()
        {
            JoiningDate = DateTime.Now;
        }
    }
}

I tried making Email unique and expected email to simply not accepting duplicate values and do nothing or throw server side validation, but I got this error

SqlException: Cannot insert duplicate key row in object 'dbo.Managers' with unique index 'IX_Managers_Email'. The duplicate key value is ([email protected]). The statement has been terminated.

CodePudding user response：

I recommend you to use Remote attribute to achieve it. Please refer to this simple demo:

Create a action in Home controller:

        [AcceptVerbs("Get", "Post")]
        [AllowAnonymous]
        public async Task<IActionResult> IsEmailInUse(string email)
        {
            //check if the emailAddress already exists or not in database
            var user = await _dbContext.managers.Where(x => x.Email == email).FirstOrDefaultAsync();
            if (user == null)
            {
                return Json(true);
            }
            else
            {
                return Json($"Email {email} is already in use");
            }
        }

Add remote attribute on this property:

[Remote(action: "IsEmailInUse", controller: "Home")]
        public string Email { get; set; }

View:

@model Manager
<form method="post">
    <div >
                <label asp-for="Email" ></label>
            <input asp-for="Email"  />
            <span asp-validation-for="Email" ></span>
            </div>
            <div >
                <label asp-for="Name" ></label>
                <input asp-for="Name"  />
                <span asp-validation-for="Name" ></span>
            </div>

    <button type="submit">Submit</button>
</form>


@section Scripts {
    @{
        await Html.RenderPartialAsync("_ValidationScriptsPartial");
    }
}

Demo:

When you enter duplicate email, The form will show error message and not allow you to submit the form.

CodePudding user response：

When you add a unique index to a field of a table in database (apparently it is Manager table in your case), the validation error occurs in database level. So it throws an exception and EF Core will pass it through your code(.net-core level), if you don't handle that exception using try-catch block, your application will give you the unhandled error. You can use try-catch like this:

try {
    //the code that inserts a Manager field to database
}
catch (Exception e) {
   // give a handled message to the user which says the email was taken.
}