The problem
NHibernate maintains internal state of the entities that it has loaded from the database. When saving an entity back to the DB you may want some properties (columns) included in the UPDATE SQL generated although the property value hasn’t changed. Why ? Because the update on a particular column might force a database trigger to run.
Or, to put it another way:
Database triggers are not really compatible with Object Relationship Mappers
In my naievety I had assumed that NHibernate would contain a “simple” option to turn on “please generate all columns in UPDATE SQL”, but, alas, no.
The Solution
I consulted StackOverflow – many posts, all leading nowhere. Then I stumbled across one post which spoke of NHibernate IInterceptors. I then consulted the NHibernate documentation – to say the documentation on interceptors is brief and concise does not do it justice.
After much messing about I worked out how interceptors work and got a working implementation so that my UPDATE SQL now includes all the columns that I require.
To make an interceptor work you need
- Interceptor code (which implements FindDirty method)
- Plug interceptor into NHibernate Session Create
Interceptor
The “documentation” recommends deriving your interceptor from the NHibernate EmptyInterceptor. The FindDirty method must be overriden in your interceptor, it returns an array [] of ints, the value represent the index of the propertyNames parameter of the FindDirty method – which is a list of the properties of the entity being saved. In my case I returned all items – so that all UPDATE SQL columns were included
Plug Into NHibernate
In your code (in my case, UnitofWork/Repo pattern code) you need to pass the interceptor as a parameter to the SessionFactory.OpenSession() method – this registers the interceptor with NHibernate and it will be called for all Saves
Code Example
Interceptor
public class MyInterceptor : EmptyInterceptor { /// <summary> /// Interceptor method /// Return array of indices into propertyNames of columns that will be maked /// as dirty i.e. SQL will be generated from them /// </summary> /// <param name="entity"></param> /// <param name="id"></param> /// <param name="currentState"></param> /// <param name="previousState"></param> /// <param name="propertyNames"></param> /// <param name="types"></param> /// <returns></returns> public override int[] FindDirty(object entity, object id, object[] currentState, object[] previousState, string[] propertyNames, IType[] types) { List<int> dirty = new List<int>(); //--------------------------------------------- // Mark as dirty for all except for .... | //--------------------------------------------- for (int i = 0; i < propertyNames.Length; i++) { if (propertyNames[i] != "ColumnIDontwantInSql" && propertyNames[i] != "AnotherColumnIDontwantInSql") { dirty.Add(i); } } return dirty.ToArray(); } }
Wiring Up
CODE SNIPPET FROM UNIT OF WORK public UnitOfWork(INHibernateContext context, ISaveInterceptor interceptor) { this.context = context; this.Interceptor = interceptor; // See here for why dummy session ISession dummy = context.SessionFactory.OpenSession(this.Interceptor); this.Session = context.SessionFactory.OpenSession(dummy.Connection, this.Interceptor); }