WPF Binding issue (UNIQUE CONSTRAINT violation on UPDATE) how to reject changes?

Question

ok please be gentle, I am new to WPF and LINQ - I have a strange problem here. I have a search screen and an add/edit screen. I have the add/edit screen bound to a 'CompanyContact' object and a search screen bound to a collection (CompanyContacts).

I have a 3 column unique constraint (FirstName, LastName, CompanyId) on the CompanyContact db table so you can't have the same name appear twice for the same company.

I should also mention that I have an "AFTER UPDATE" trigger on CompanyContact table's 'ModifiedDate' column which refreshes the ModifiedDate because I don't like allowing the client PC to dictate the modifieddate/time... (I want the database to keep track of when the record was modified). I let the DEFAULT CONSTRAINT put GetDate() into this column on INSERTs.

let's say there is a "Steve Smith" at CompanyId 123 and there is also a "Steve Smith2" at CompanyId 123

If I attempt to edit an existing company contact (Steve Smith2 @CompanyId=123) and change the last name from "Smith2" to "Smith" so that it causes the Unique constraint to fire (collision with Steve Smith @ CompanyId=123), everything seems to work fine (i.e. I made it so that the Edit screen traps the SqlException and 'resets' the properties back to their original values by resetting the base.DataContext and the user is notified - "hey you can't do that... it would cause a duplicate record") but when I dismiss the Edit screen (Click the CANCEL button) and return to the Search screen, the offending data is showing in the Search results... (i.e. there are now 2 records showing Steve Smith @ CompanyId 123)

I have tried many things, including writing code in LINQ to check for duplicates before attempting to UPDATE... but it seems like there is a simpler solution than that? I am a big believer in putting rules into the database so there is consistent enforcement of a rule i.e. so that rules are enforced the same for everyone including those people who make work directly against the database (on the backend)

here's a snippet from Add/Edit screen (Search screen can call this function)...

    public CompanyContact EditCompanyContact(int companyContactId)
    {
        CompanyContact myCompanyContact;

        try
        {
            _screenMode = ScreenMode.Edit;

            myCompanyContact = new CompanyContactRepository().GetById(companyContactId);

            //experimental code -- use this to reset base DataContext if unique constraint violated...
            _originalCompanyContact = (CompanyContact)myCompanyContact.Clone();

            //make sure to clone the object so we can discard changes if user cancels
            base.DataContext = (CompanyContact)myCompanyContact.Clone();

            SetupScreen();
            this.ShowDialog();

            //if user cancels Edit this is 'reset' to originalCompanyContact 
            return ((CompanyContact)base.DataContext); 

        }
        finally
        {
        }
    }

and here is code from the 'cancel button'

    private void btnCancel_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            //HACK: this allows us to discard changes to the object passed in (when in EDIT mode)
            //TODO: research a truer WPF approach? (RoutedEvents?)
            _userCancelled = true;

            base.DataContext = _originalCompanyContact;

            this.Close();
        }
        finally
        {
        }
    }

here is the code that is executed when you try to Save on the Add/Edit screen:

try
{
  if (base.DataContext != null)
  {
   CompanyContactRepository ccr = new CompanyContactRepository();
   cc = ((CompanyContact)base.DataContext);
   ccr.Save(cc);
  }

  //dismiss the form after saving CompanyContact
  this.Close();
}
catch (SqlException sx)
{
  if (sx.Message.IndexOf("Violation of UNIQUE KEY constraint 'UN_CompanyContact_Value'.") == 0)
  {
    MessageBox.Show(String.Format("a CompanyContact with the name ({1} {0}) already exists for {2}", cc.FirstName, cc.LastName, cc.Company.Name), "Duplicate Record", MessageBoxButton.OK, MessageBoxImage.Exclamation);
  }
  else
  {
    //yes - catch and rethrow is probably a bad practice, but trying to ISOLATE UNIQUE constraint violation
    throw sx;
  }
}
finally
{
}

and here is some LINQ code for the Save (sorry it is FUGLY! - I've been hacking around with it all day)

public void Save(CompanyContact entityToSave)
{
  try
  {

    var saveEntity = (from cc in db.CompanyContacts
            where cc.CompanyContactId == entityToSave.CompanyContactId
            select cc).SingleOrDefault();

    if (saveEntity == null)
    {
      //INSERT logic                    
      entityToSave.CreatedById = new CompanyPersonRepository().GetCompanyPerson(DataContext.Default.LoginUsername).CompanyPersonId;
      entityToSave.ModifiedById = entityToSave.CreatedById;

      db.CompanyContacts.InsertOnSubmit(entityToSave);
      db.CompanyContacts.Context.SubmitChanges();
    }
    else
    {
      //UPDATE logic            
      saveEntity.ModifiedById = new CompanyPersonRepository().GetCompanyPerson(DataContext.Default.LoginUsername).CompanyPersonId;
      saveEntity.CompanyId = entityToSave.Company.CompanyId;
      saveEntity.FirstName = entityToSave.FirstName;
      saveEntity.LastName = entityToSave.LastName;
      saveEntity.CompanyContactTypeId = entityToSave.CompanyContactTypeId;

      db.CompanyContacts.Context.SubmitChanges();

    }
...

Answer 1

OK I found a solution, but it seems like it is more of a hack than a true solution... surely there is a better way to fix this?

I put a catch block in my LINQ Code (in the Save() function) like this:

if (sx.Message.IndexOf("Violation of UNIQUE KEY constraint 'UN_CompanyContact_Value'.") == 0)
  {
    //this will refresh the cache... so you won't see things that violate constraints
    //showing on the SearchCompanyContacts if you return from Edit CC screen...
    db.Refresh(RefreshMode.OverwriteCurrentValues, db.CompanyContacts);
    throw sx;
  }
  else
  {
    throw sx;
  }

this feels very hollow and unsatisfying.. I really don't like the fact that I am catching and rethrowing the exception, but it's the only way to have some detail to explain why the save failed... this wouldn't be necessary if I did not need to do the db.Refresh() call... (I could just let the SqlException bubble up the call stack and I could handle this with an ExceptionHandler class)...

I also changed the logic so that I return NULL from the Edit screen if the user cancels out, then on the search screen, I have this small change too...

selectedCompanyContact = editCompanyContact.EditCompanyContact(selectedCompanyContact.CompanyContactId);

if (selectedCompanyContact == null)
{
  //refresh from db..
  ExecuteSearch();
}

so that the search screen refreshes the search results and the "reset" value (i.e. original data) is shown.

but surely this cannot be the proper way to write LINQ? I would expect there is some sort of setting I can define that says, if the database rejects it (UNIQUE CONSTRAINT violated, FOREIGN KEY violated, etc.) then throw away the stuff cached in the collection that violates any database rules... what's interesting right now is that the search screen and the edit screen don't immediately erase what the user typed, but once I hit the Cancel button the screen 'refreshes' and you see the original value...

one thing I like about this solution is at least the (Edit) screen does not get dismissed if you violate a rule (you get a 'duplicate record exists' message) and you can still see your data 'unaltered' in the fields which are bound to the entity 'to be saved' so you can review the data and say "aha... I see why this data was rejected" then you can change your data and the "OK" button will continue to reject your bad data until your data meets all rules defined in the database, so if you want, you can hit the CANCEL button then it will throw away your changes and the search results screen will refresh to the "old data".

I was intrigued by this function:

db.CompanyContacts.Context.GetChangeSet().Updates.Clear();

but unfortunately it's a read-only list so a call to this fails... but I am wondering if there's a way to use an object from the list (e.g. db.CompanyContacts.Context.GetChangeSet().Updates[0]) to be able to take the offending entity out or detach/attach - not sure if there's something I'm missing or not, but it feels that way.

I am serious.. and don't call me Shirley... ;-)