Post Cover Photo

Implementing Uniqueness or Unique Key Constraint on a Model Property in Entity Framework Code First

03 April 2018 08:47 AM
24 May 2019 04:53 AM

Introduction

Sometimes, we are in need of not permitting the duplicate values of a column or property in a database table, such as: for a username column or property in database table, we should not allow user to insert a value that already exists in the database table because a username is a unique value.

Let's Start!

Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:

public  class Product
{
        public int Id { get; set; }

        public string ProductName { get; set; }

        public int ProductQuantity { get; set; }

        public decimal UnitPrice { get; set; }
}

Step 1: Add "Index" Attribute with ''IsUnique'' parameter to the ProductName property. Remember that! with "Index" attribute you must use "StringLength" attribute also.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ImplementingUniqueKey.Models
{
   public class Product
   {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        [Index("Ix_ProductName",Order =1,IsUnique =true)]
        public string ProductName { get; set; }

        public int ProductQuantity { get; set; }

        public decimal UnitPrice { get; set; }
    }
}

Now if you have a Product table of Product class already in the database, then update the changes in database using code first migration or you can simply drop the table and regenerate if there is no issue with existing data.

Now run the project and try to insert a product entity with a product name that already exists in the table. You will get an exception with the following error message:

Cannot insert duplicate key row in object 'dbo.Products' with unique index 'Ix_ProductName'. The duplicate key value is (ProductName). The statementhas been terminated.Which is meaningless to the client user. So you have to handle the exception to give a meaningful message to the client user.

As we cannot show the exception message to the end user, so we have to implement some validation that will prevent user to submit the form with duplicate values.

Step 2: Now we will use Remote Attribute validation as follows:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ImplementingUniqueKey.Models
{
    public class Product
    {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        [Index("Ix_ProductName",Order =1,IsUnique =true)]
        [Remote("IsProductNameExist", "Product", AdditionalFields = "Id", 
                ErrorMessage = "Product name already exists")]       
        public string ProductName { get; set; }

        public int ProductQuantity { get; set; }

        public decimal UnitPrice { get; set; }
    }
}

Step 3 : Now will implement the IsProductNameExist method in Product Controller as follows:

public async Task<JsonResult> IsProductNameExist(string ProductName, int ? Id)
{
     bool isProductNameExist = await db.Products.Any(x => x.ProductName == ProductName && x.Id != Id);
     
     return Json(!isProductNameExist, JsonRequestBehavior.AllowGet);
}

Now run the project and try to insert a product entity with a product name that already exists in the table. You will get the validation error message as follows:

Although eveything is working as but there is a small problem, that is the Remote Attribute validation is actually a Client side javascript validation. It will only fire  only if javascript is enable on client browser. if the the user disable the javascript on his browser, this validation will not work anymore. So that'w why have implement that validation rule on server side too so that validation is trigger even the javascript is disable on the client browser.

Step 4: Now we have to inherit the IValidatableObject interface and provide the implementation of Validate() method of IValidatableObject interface in the product class as follows:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace ImplementingUniqueKey.Models
{
    public class Product : IValidatableObject
    {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        [Index("Ix_ProductName",Order =1,IsUnique =true)]
        public string ProductName { get; set; }
        
        public int ProductQuantity { get; set; }
        
        public decimal UnitPrice { get; set; }

        public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
        {
            ProductDbContext db = new ProductDbContext();
            List<ValidationResult> validationResult = new List<ValidationResult>();
            bool isProductNameExist = db.Products.Any(x => x.ProductName == ProductName && x.Id != Id);
            if (isProductNameExist)
            {
                ValidationResult errorMessage = new ValidationResult("Product name already exists.", new[] { "ProductName" });
                validationResult.Add(errorMessage);
            }
            return validationResult;
        }
    }
}
You can also write the validate() method in a more simplified form as follows:
public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
{
      ProductDbContext db = new ProductDbContext();
      bool isProductNameExist = db.Products.Any(x => x.ProductName == ProductName && x.Id != Id);
      if (isProductNameExist)
      {
         ValidationResult errorMessage = new ValidationResult("Product name already exists.", new[] { "ProductName" });
         yield return errorMessage;
      }
      else
      {
         yield return ValidationResult.Success;
      }
}

Before running the project, keep in mind that your db.SaveChanges() methods in the controller class must be in "ModelState.IsValid" checking. Your Create and Edit/Update methods should look like follows:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "Id,ProductName,ProductQuantity,UnitPrice")] Product product)
{
    if (ModelState.IsValid)
    {
       db.Products.Add(product);
       db.SaveChanges();
       return RedirectToAction("Index");
    }

    return View(product);
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "Id,ProductName,ProductQuantity,UnitPrice")] Product product)
{
    if (ModelState.IsValid)
    {
        db.Entry(product).State = EntityState.Modified;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(product);
}

Now run the project and try to insert a product entity with a product name that already exists in the table. You will get the validation error message as follows:

R

Rufaida

24-May-2019 06:13 AM


Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:


R

Rufaida

24-May-2019 06:14 AM


Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:


T

TanvirArjel

24-May-2019 06:15 AM


Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:


T

TanvirArjel

24-May-2019 12:21 PM


Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:


T

TanvirArjel

24-May-2019 06:15 AM


Suppose we have an Inventory where there is a Product table/ class which is used to track all products as a list of products. So it is rational not to permit the user to insert a product name that already exists in the table. Here is our product model class which will be used throughout this article:


Want to post a comment?
Please log in here

TanvirArjel Profile Picture
Tanvir Ahmad Arjel -Software Engineer

Father of a Princess, a Passionate Programmer, Software Architect, Software Engineer, Programming Blogger and a high ambitious dreamer..

Get Connected

Stay updated

Subscribe to stay updated! whenever a new blog post will be posted here, you will be notified so that you don't miss!

Search