Following NerdDinner tutorial, don't allow duplicate

Question

My sql database doesn't allow 2 records to be added with the same number. If I try to create a record with a previously used number or edit a record to use a previously used number, it doesn't allow it and returns to the edit/create page with an asterisk by the subcontract number field. I would like to add a Rule Violation for this so an appropriate message can be displayed. I tried adding this:

 if (db.subcontracts.Count(s => s.subcontract_no == subcontract_no) > 0)
      yield return new RuleViolation("Subcontract number already exists", "subcontract_no");

When writing this, I was only thinking of the Create method. The problem here is it finds a violation every time I edit a record, even if the subcontract_no hasn't changed. So, I guess I need it to find a violation when the record is created and when edited if the subcontract_no is changed. But, it can't "find itself" and create a rule violation. How can I do this?

Answer 1

First, on your edit/update page, if the value can't be changed, don't display it as an editable value. You might want to get back a ViewModel in the action, retrieve the corresponding model from the DB, and use TryUpdateModel with a restriction that excludes the immutable fields. Second, in your validation code, omit the check for a duplicate subcontract number when the change type is Update (instead of Insert). Augment this with a bit of code that ties into the property changing handler for the subcontract number property that throws an exception if the subcontract number already has a (non-initial) value and you attempt to change it. This should prevent the value from being set except at insertion and allow you to skip the validation check on update.

You could also couple this with a DB constraint that forces uniqueness on the column -- a unique index on a non-null column would work.

Answer 2

Try:

db.subcontracts.Count(s => (s.subcontract_no == subcontract_no) 
    && (s.id != actually_edited_or_created_contract.id)).Count

It will work with new or existing entity, even when entity.id == null.