How to avoid entry of duplicate attribute?

Question

Do not allow entry of duplicate attribute value for individual attribute (user code)

public UserCode UpdateUserDefinedField(UserCode userCode, User user)
{
var dbCode = DbContext.UserCodes.Find(userCode.Id);
dbCode.Name = userCode.Name;
dbCode.Description = userCode.Description;
if (dbCode.UserCodeTypeId != userCode.UserCodeTypeId)
if (dbCode.UserCodeValidValues.Any(x=>x.UserCodeValues.Any()))
throw new NotSupportedException("You cannot change the attribute type because it has values in use.");
dbCode.UserCodeTypeId = userCode.UserCodeTypeId;
dbCode.IsFreeformTextValue = userCode.IsFreeformTextValue;
dbCode.IsMandatory = userCode.IsMandatory;
// Need to handle unique constraint violation
foreach (UserCodeValidValue ucvv in userCode.UserCodeValidValues)
{
UserCodeValidValue compare = dbCode.UserCodeValidValues.Where(x => x.Id == ucvv.Id).FirstOrDefault();
if (compare != null)
{
if (ucvv.Description != compare.Description || ucvv.CodeValue != compare.CodeValue)
{
compare.CodeValue = ucvv.CodeValue;
compare.Description = ucvv.Description;
UpdateWithUser(compare, user.UserId, "UpdateUserDefinedField");
}
}
else
{
ucvv.UserCode = dbCode;
AddWithUser(ucvv, user.UserId, "UpdateUserDefinedField");
}
}
UpdateWithUser(dbCode, user.UserId, "UpdateUserDefinedField");
return dbCode;
}

Salman Beg · Answer

First take id from UserCodes table and check with UserCodeId in UserCodeValidValues table. If it match then don't insert and show alert otherwise insert. Thanks

Praveen Kumar · Answer

Hi, Finally i done to avoid entry of duplicate attribute, [NonAction] private void SetModelStateErrorIfDuplicateValuesExist(List values) { var duplicates = values.GroupBy(t => new { Name = t.Name.ToUpperInvariant() }).Where(t => t.Count() > 1).ToList(); if (duplicates.Count > 0) ModelState.AddModelError("Values", "Duplicate Values On Name Feild Are Not Allowed."); } Then i called the method in Both Create and Edit Action, SetModelStateErrorIfDuplicateValuesExist(model.Values);

Praveen Kumar · Answer

Hi , public ActionResult Edit(UserDefinedFieldsViewModel model) { using (var dbContextScope = _dbContextScopeFactory.Create()) { if (!model.IsFreeformTextValue) { if (model.Values == null || (model.Values.Where(v => v.UIStatus != "DELETE").Count() == 1 && model.Values.First().UIStatus == "TEMPLATE")) { ModelState.AddModelError("Values", "Please enter at least one valid value."); } } else { var userCodeType = attrRepo.Find (model.UserCodeTypeId); if (!new string[] { "CO", "CT" }.Contains(userCodeType.Code)) { ModelState.AddModelError("IsFreeformTextValue", "Freeform text values only allowed for Company and Contact attributes."); } else if (model.Values != null && model.Values.Where(v => v.UIStatus != "DELETE").Count() > 1) { model.IsFreeformTextValue = false; ModelState.AddModelError("Values", "No values can be specified if 'Allow Freeform Text Value' is checked."); } } UserCode userCod = attrRepo.FindBy (uc => uc.Id == model.Id).FirstOrDefault(); if (userCod != null) { if (userCod.UserCodeValidValues != null) { foreach (UserCodeValidValue ucvv in userCod.UserCodeValidValues) { //strucked here with code - can help any one - for avoid duplicate } } } if (ModelState.IsValid) { model.Values.RemoveAll(x => x.UIStatus == "TEMPLATE" && x.Id == 0); UserCode userCode = model; User user = _Login.GetCurrentUser(); try { foreach (var uvv in model.Values) { if (uvv.UIStatus == "DELETE") { try { attrRepo.DeleteUDFValidValue(uvv.Id, user); var ucvv = userCode.UserCodeValidValues.Where(x => x.Id == uvv.Id).Single(); if (ucvv != null) userCode.UserCodeValidValues.Remove(ucvv); } catch (RMRepositoryException re) { ModelState.AddModelError("Values", re.Message); } } } try { attrRepo.UpdateUserDefinedField(userCode, user); } catch (NotSupportedException) { throw; } dbContextScope.SaveChanges(); return RedirectToAction("Index"); } catch (Exception e) { if (e is DbUpdateException dbUpdateEx) { if (dbUpdateEx.InnerException != null && dbUpdateEx.InnerException.InnerException != null) { if (dbUpdateEx.InnerException.InnerException is SqlException sqlException) { switch (sqlException.Number) { case 2601: // Duplicated key row error // Constraint violation exception ModelState.AddModelError("", "Error: You cannot edit an attribute with the same name, type code as an existing attribute"); break; default: break; } } } } else if (e is NotSupportedException nse) { ModelState.AddModelError("UserCodeTypeId", nse.Message); } else { ModelState.AddModelError("Values", e.Message); } } } var errors1 = ModelState.Values.SelectMany(v => v.Errors); foreach (var a in errors1) { Debug.WriteLine(a.ErrorMessage); } //var errors = ModelState // .Where(x => x.Value.Errors.Count > 0) // .Select(x => new { x.Key, x.Value.Errors }) // .ToArray(); model.UserCodeTypes = SelectListItemHelper.UserCodeTypes(); } return View(model); }

Laxmidhar Sahoo · Answer

hi Actually you compare with the same table

Amit Naik · Answer

Before inserting to Table check if the value exists, if exists then return with some validation message, do something like https://github.com/Amitpnk/ASP.NET-Core-API-using-EF-core-N-repository-dp/blob/cdf407fe036bcf93c7e31695cbe6d9e00a9d6e70/CustomerEFCore/CustomerEFCore/Controllers/CustomerController.cs#L69 Or other way, set unqiue constraints to the table, find below reference of it https://www.mssqltips.com/sqlservertip/4270/difference-between-sql-server-unique-indexes-and-unique-constraints/

Laxmidhar Sahoo · Answer

hi var code = usercode.id; if (DbContext.UserCodeValidValues.Any(x=>x.UserCodeId==code))

Praveen Kumar · Answer

Hi Salman, I need to avoid duplicate values in second table "Usercodevalidvalues" .

Salman Beg · Answer

When you are clicking on save button, check whether id exists or not inside the UserCodes table. If exists then don't insert otherwise insert. Thanks

How to avoid entry of duplicate attribute?

Answers (8)