How to avoid entry of duplicate attribute?

Praveen Kumar

6y2k.NET

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;
}

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Salman Beg6y ago
Accepted 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
+2
Praveen Kumar6y ago
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);
+1
Praveen Kumar6y ago
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);
}

+1
Laxmidhar Sahoo6y ago
hi
Actually you compare with the same table
0
Amit Naik6y ago
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/
0
Laxmidhar Sahoo6y ago
hi
var code = usercode.id;

if(DbContext.UserCodeValidValues.Any(x=>x.UserCodeId==code))
0
Praveen Kumar6y ago
Hi Salman,

I need to avoid duplicate values in second table "Usercodevalidvalues" .
0
Salman Beg6y ago
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
0