How to Handle a reference class with a unique field in NHibernate

Question

I am using NHibernate and need to update/add an entity class(a) that can reference another entity class(b) that needs to have a unique "name" field.
So would it be best to:

• query with NHibernate to find an existing entity(b) before the call to save entity(a).
• create stored proc to handle this update/insert logic.
• let table(b) unique constraint throw back an exception when NHibernates session.save is called?

Answer 1

I have actually long pondered upon this scenario and have tried a few things.

First of all, I do not like stored procs very much, why use NHibernate if you're gonna tie yourself to a particular DB engine I say. For this scenario, the stored procs (option 2) can behave very much like options 1 and 3, depending on your implementation of the proc (ie. is it querying first then saving, or doing a more complex transaction with rollback). So unless you have something else in mind, I won't comment further on this one.

You could use option 1, but this involves more work from you as a programmer. It could be fine for 1-2 entities but whether you could generalize this for all your entities is unknown, as I don't know the details of your project. Furthermore, why do the querying if NHibernate can do it for you?

So I would recommend option 3, I prefer it and it works very well for me. When the contraint is met, I rollback the entire transaction. I also make sure I obtain the offending element, so I can say something 'clever' to the user such as "User x cannot be saved because email y is duplicate" rather than "User x cannot be saved: Duplicate entry".