How to insert a detached object, which may already exist in the database using NHibernate?

Question

Dear ladies and sirs.

I have the following code snippet:

using (var scope = DataAccessPortal.DataContext(DB.Main).OpenStatementScope())
using (var transaction = scope.BeginTransaction())
{
  // Several other database operations.
  if (scope.ExistsById(obj.GetType(), obj.Id))
  {
    scope.Update(obj);
  }
  else
  {
    scope.Insert(obj);
  }
  transaction.Commit();
}

Where scope and transaction are thin abstract layers around NHibernate session and transaction objects respectively, ExistsById maps to the HQL query to check if the given ID is found in the database, Update and Insert map to the respective session operations.

The purpose of the code is to update several objects in the database and then update some object in the database, which may or may not be already found there - the update-or-insert code.

I dislike the update-or-insert code, because it yields two round trips to the database. Is it possible to accomplish the same task with just one round trip using NHibernate?

My constraints are:

• The database comes with no predefined stored procedures. If NHibernate allows to create one dynamically in a DB independent manner, then fine (how???), otherwise using a stored procedure is not an option.
• Chances are that the object already exists in the database, so I could just do an Update, catch the exception if the object does not exist and then retry with Insert. However, the update-or-insert code is part of a larger transaction. So, failing it also fails the transaction, which means I will have to retry the whole transaction and not just the update-or-insert piece of code. I am not sure it is more preferable than the way it is now.

Answer 1

You will need a DB roundtrip in any case. A NHibernate session acts as a client-side cache that keeps the business object and the DB in sync. Thus, if you detach and then re-attach an object, the session has no other way than re-checking the DB against the object. There's no other way, if you think about it for a sec...

Usually, this is done with the sessions Refresh() method like this (pseudocode):

ISession session = GetSession(); 
session.Refresh(obj); 
session.DoSomething();

Whether you have some sort of facade or wrapper around that doesn't matter. The overall pattern remains the same: You have to check the DB before you can do something...