NHibernate unique constraints

Question

I've run into some trouble with unique constraints in NHibernate.

I have a User entity that is mapped with a unique constraint for the Username property. What I want to do is to be able to check if a particular username exists before a new user is added and also before an existing user updates it's username.

The first scenario (adding a new user) works just fine. However, when I try to check if the username exists before updating an existing user, I get a constraint violation. Here's what the code for my Save method looks like.

public void Save<T>(T entity) where T : User
 {
  using (var session = GetSession())
  using (var transaction = session.BeginTransaction())
  {
   try
   {
    CheckIfUsernameExists(entity);

    session.SaveOrUpdate(entity);
    session.Flush();
    transaction.Commit();
   }
   catch (HibernateException)
   {
    transaction.Rollback();
    throw;
   }
  }
 }

The constraint is violated in the CheckIfUsernameExists() method and it looks like this:

public void CheckIfUsernameExists<T>(T entity) where T : User
 {
  var user = GetUserByUsername(entity);
  if (user != null)
   throw new UsernameExistsException();
 }

 private T GetUserByUsername<T>(T entity) where T : User
 {
  var username = entity.Username;
  var idToExclude = entity.Id;

  var session = GetSession();

  var user = session.CreateCriteria<T>()
   .Add(Restrictions.Eq("Username", username))
   .Add(Restrictions.Not(Restrictions.IdEq(idToExclude)))
   .UniqueResult() as T;

  return user;
 }

It is the session.CreateCriteria() line that causes it to crash resulting in an NHibernateException (SQLiteException) with the message "Abort due to constraint violation. column Username is not unique".

Is it related to the NHibernate cash? The entity that is passed to the save method has been updated with the desired username at the time session.CreateCriteria() is called.

Maybe I'm doing this all wrong (I am an NHibernate beginner) so please feel free to state the obvious and suggest alternatives.

Any help is much appreciated!

Answer 1

Hmm, I'm not sure about the core of the problem, but for the strategy of trying to see whether a user already exists, why do you need the ".UniqueResult()"?

Couldn't you just assume to get a list of users which match that username and which do not have the same id as your current user (obviously). Pseudo-code like I'd do something like this

public bool ExistsUsername(string username, int idToExclude)
{
   IList<User> usersFound = someNHibernateCriteria excluding entries that have id = idToExclude

   return (usersFound.Count > 0)
}

Answer 2

Two thoughts: - Why don't you just SaveOrUpdate and see if you succeed. Is that not possible in your scenario? - I've seen you mentioning SQLite. Is that your real production system, or just something you use for testing. If so, have you checked if it's SQLite that makes the problems, and the query works against a fully featured DBMS? - SQLite frequently makes that kind of problems, because it does not support every kind of constraint...

Answer 3

Are you sure the exception is thrown at CreateCriteria? Because, I don't see how you could get a SQLlite constraint exception from a select statement. I do virtually the same thing...

public bool NameAlreadyExists(string name, int? exclude_id)
{
    ICriteria crit = session.CreateCriteria<User>()
     .SetProjection(Projections.Constant(1))
     .Add(Restrictions.Eq(Projections.Property("name"), name));

    if (exclude_id.HasValue)
     crit.Add(Restrictions.Not(Restrictions.IdEq(exclude_id.Value)));

    return crit.List().Count > 0;
}

I would look at the order of the generated sql to see what's causing it. If that entity was loaded in that session, it could be getting updated before the query.

Answer 4

transaction.Rollback() doesn't remove your entity from session cache, use session.Evict() instead.

See: - https://www.hibernate.org/hib%5Fdocs/nhibernate/html/performance.html#performance-sessioncache