How to check unique constraint violation in nHibernate and DDD before saving?

Question

I've got an Account model object and a UNIQUE constraint on the account's Name. In Domain Driven Design, using nHibernate, how should I check for the name's unicity before inserting or updating an entity?

I don't want to rely on a nHibernate exception to catch the error. I'd like to return a prettier error message to my user than the obscure could not execute batch command.[SQL: SQL not available]

In the question Where should I put a unique check in DDD?, someone suggested using a Specification like so.

Account accountA = _accountRepository.Get(123);
Account accountB = _accountRepository.Get(456);
accountA.Name = accountB.Name;

ISpecification<Account> spec = new Domain.Specifications.UniqueNameSpecification(_accountRepository);
if (spec.IsSatisfiedBy(accountObjA) == false) {
   throw new Domain.UnicityException("A duplicate Account name was found");
}

with the Specification code as:

public bool IsSatisfiedBy(Account obj)
{
   Account other = _accountRepository.GetAccountByName(obj.Name);
   return (other == null);
}

This works for inserts, but not when doing an update because. I tried changing the code to:

public bool IsSatisfiedBy(Account obj)
{
   Account other = _accountRepository.GetAccountByName(obj.Name);

   if (obj == null) {  // nothing in DB
      return true;
   }
   else {              // must be the same object.
      return other.Equals(obj);
   }
}

The problem is that nHibernate will issue an update to the database when it executes GetAccountByName() to recover a possible duplicate...

return session.QueryOver<Account>().Where(x => x.Name == accntName).SingleOrDefault();

So, what should I do? Is the Specification not the right way to do it?

Thanks for your thoughts!

Answer 1

I'm not a fan of the specification pattern for data access, it always seems like jumping hoops to get anything done.

However, what you've suggested, which really just boils down to:

1. Check if it already exists.
2. Add if it doesn't; Show user-friendly message if it does.

... is pretty much the easiest way to get it done.

Relying on database exceptions is the other way of doing it, if your database and it's .NET client gracefully propagates the table & column(s) that were infringing the unique constraint. I believe most drivers don't do so (??), as they just throw a generic ConstraintException that says "Constraint XYZ was violated on table ABC". You can of course have a convention on your unique constraint naming to say something like UK_MyTable_MyColumn and do string magic to pull the table & column names out.

NHibernate has a ISQLExceptionConverter that you can plug into the Configuration object when you set NHibernate up. Inside this, you get exposed to the exception from the .NET data client. You can use that exception to extract the table & columns (using the constraint name perhaps?) and throw a new Exception with a user friendly message.

Using the database exception way is more performant and you can push a lot of the detecting-unique-constraint-violation code to the infrastructure layer, as opposed to handling each one case by case.

Another thing worth pointing out with the query-first-then-add method is that to be completely transaction safe, you need to escalate the transaction level to serializable (which gives the worst concurrency) to be totally bullet proof. Whether you need to be totally bullet proof or not, depends on your application needs.