Lesser-Known NHibernate Features: Executable HQL

var records = session.CreateQuery("update Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

What happens when you need to bulk change a lot of records on the database? The unwary novice might be tempted to load data from the database into class instances, change them and then either rely on change tracking to eventually make the changes persistent or even worse, explicitly do an update on every possibly changed entity. The non-novice readers should now rolling their eyes.

It so happens that NHibernate offers a great alternative in the form of executable HQL. Basically, it is HQL for doing bulk changes: inserts, updates and deletes.

HQL Inserts have a small gotcha: they need to come from selects. Here’s an example:

var records = session.CreateQuery("insert into Account (Name, Email, Birthday) select p.Name, p.Email, p.Birthday from Person").ExecuteUpdate();

Here is an update:

var records = session.CreateQuery("update Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

Two problems arise:

  • Cannot do joins with updates;
  • Does not update version properties.

The second one is easy to solve:

var records = session.CreateQuery("update versioned Person p set p.Email = p.Email + p.Username + '@somedomain.com' where p.Email is null").ExecuteUpdate();

Noticed the versioned keyword? This tells NHibernate to do the right thing: update the version on each affected entity, of the entity is versioned.

The final one is deletes:

var records = session.CreateQuery("delete Product p where size(p.Sales) = 0").ExecuteUpdate();

The only problem with this is that it does not cascade. You need to find another solution.

A final word on this: you can, of course, specify parameters in your queries, like in the following example.

var records = session.CreateQuery("delete Product p where p.Price = :price").SetParameter("price", 0).ExecuteUpdate();

NHibernate Pitfalls: Sharing a Session in Multiple Threads

The NHibernate ISession is not thread-safe, that is, it shouldn’t be shared by multiple threads. The ISessionFactory, however, is thread-safe, and thus it should be used to create ISession instances as needed. Do not worry, creating sessions does not mean that connections to the database are immediately open and kept alive, the ISession implementation only opens database connections when they are necessary and closes them when they cease to be.

Lesser-Known NHibernate Features: Statistics

NHibernate makes available a number of statistics about its work; this includes, among others:

  • All of the queries executed;
  • Number of entities loaded, inserted, updated and deleted;
  • Number of optimistic concurrency misses;
  • Number of second level cache hits and misses;
  • Number of transactions started and committed;
  • Number of connections opened and closed;
  • etc.

This is available per session factory:

var statistics = sessionFactory.Statistics;

And all of these settings can be filtered per entity:

var entityStatistics = sessionFactory.Statistics.GetEntityStatistics("MyEntity");

Statistics can help us diagnose, for example, second level or query cache issues.

If we have no need for them, we can disable them before building the session factory:

cfg.SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, Boolean.FalseString);

Or at runtime:

sessionFactory.Statistics.IsStatisticsEnabled = false;

An advice: do switch it off while on production, it does have an effect on performance.

Lesser-Known NHibernate Features: Dynamic Components

NHibernate, unlike others, has lots of ways to map columns from the database to and from classes. Normally, there is a 1:1 relation between columns and properties, but it doesn’t have to be so: there are, for example, components and dynamic components.

This time we will be looking at dynamic components. Dynamic components allow the usage of a dictionary, where one or several columns from the database will be stored, each in its own entry. This is pretty cool, if we need to add more columns at some point, and we do not want, or can, change the class!

Show me the code, I hear you say:

public class DataStore


    public DataStore()


        this.Data = new Dictionary<String, Object>();



    public virtual int Id { get; set; }


    public virtual IDictionary Data { get; set; }


Yes, it will be possible to use generic dictionaries (IDictionary<TKey, TValue>), when pull request for NH-3670 is merged, which should happen soon (NHibernate 4.1).

Now, the mappings:

var mapper = new ConventionModelMapper();

mapper.Class<DataStore>(x =>


        x.Id(y => y.Id, y => { });

        x.Component(y => y.Data, new


            A = 0,

            B = ""

        }, y =>


            y.Property(z => z.A);

            y.Property(z => z.B);



I am setting the template for whatever will be stored in the Data dictionary, in this case, an integer column (A) and a string one (B) as an anonymous object. In NHibernate 4.1, it will be possible to use a dictionary instead (see NH-3704), which will help in making it more dynamic. In the third parameter to Component, we can change the mapping, for example, the physical properties of each column; we can even use ManyToOne instead of Property, so that a particular entry in the dictionary will point to another entity!

A sample usage:

session.Save(new DataStore { Data = new Dictionary<String, Object> { { "A", 1 }, { "B", "two" } } });

Lesser-known NHibernate Features: Filters

Unlike other OR/Ms – which, as always, shall remain unnamed – NHibernate offers a couple of ways to automatic filter results. Basically, we have two options:

  • Static restrictions;
  • Dynamic restrictions, or filters.

Because filters offer everything that static restrictions do and more, we’ll focus on filters.

A filter can specify a restriction, in terms of a SQL clause, to either an entity as a whole (the class, not a specific query) or to a collection (bag, set, list, map, array, etc).

For example, imagine you have a table that holds values that can be translated and a translation table for that purpose:


You will want to retrieve only the translation for the current culture. A domain model could look like:


We would like to apply a restriction to the Translations property of Translatable, so as to filter the translations by the current culture.

First, we need to create a filter, this is done at Configuration level:

cfg.AddFilterDefinition(new FilterDefinition("CurrentCulture", string.Empty, new Dictionary<string, IType> { { "Culture", NHibernateUtil.String } }, false));

The restriction can be defined on the filter itself, or per entity or collection. In this case, I didn’t specify it on the filter (string.Empty), so I will need to do it at the collection level:

mapper.Class<Translatable>(x =>


        //rest goes here

        x.Set(y => y.Translations, y =>


            //rest goes here

            y.Filter("CurrentCulture", z =>


                z.Condition("Culture = :Culture");





A filter needs to be explicitly made active, and, if it contains parameters, all of its parameters must be set:

session.EnableFilter("CurrentCulture").SetParameter("Culture", CultureInfo.CurrentCulture.Name);

Now, whenever the Translations collection is retrieved, either through a SELECT or an INNER JOIN, the “Culture = :Culture” restriction – where, of course, :Culture is replaced by the current parameter value – will be applied automatically, together with the foreign key restriction.

The other option is to filter entities as a whole. Remember soft deletes? I wrote two posts on them (here and here). Instead of using static restrictions, we can instead use filters:

cfg.AddFilterDefinition(new FilterDefinition("SoftDeletes", "deleted = 0, new Dictionary<string, IType>(), true));


mapper.Class<Record>(x =>


    x.Filter("SoftDeletes", y => {});

    x.Set(y => y.Children, y =>


            y.Filter("SoftDeletes", z => {});



In this example, I define the restriction string on the filter itself, and I apply it to both the Record entity and its Children collection. This time, no parameters, but we still need to enable the filter before we issue a query:


If for any reason you want to disable a filter, it’s easy:


Or even get its definition:

var filter = sessionFactory.GetFilterDefinition("SoftDeletes");

Enjoy your filters!

Lesser-Known NHibernate Features: Mapping By Convention

Did you know that NHibernate, like other O/RMs out there, allows you to map classes by convention? Yes, it’s true… Smile Let me show you how!

First, you need to create an instance of the appropriately-named ConventionModelMapper:

var mapper = new ConventionModelMapper();

Next, you need to tell it where is the assembly (or assemblies) containing the entities you want to map:

var mappings = mapper.CompileMappingFor(typeof(MyEntity).Assembly.GetExportedTypes());

Finally, you need to add the generated mappings to a Configuration instance:


That’s all it takes! Really! Winking smile

Granted, this is very convenient, but we don’t know much what is happening inside; for example, what id generation strategy is it using? By default, it uses native, which means it will use the native strategy of the database engine currently being used – identity for SQL Server and MySQL, sequence for Oracle and PostgreSQL, etc. If you wish to override this, you certainly can:

mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>


    classCustomizer.Id(x =>





This tells the mapper to use the high-low strategy for all entities.

What if you want to change the default naming of columns and tables? Well, you have two options:

  1. Provide an handler for the BeforeMapClass or BeforeMapProperty events and in it change the name of the physical object:
    mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>





    mapper.BeforeMapProperty += (modelInspector, member, propertyCustomizer) =>




  2. Provide your own implementation of INamingStrategy to NHibernate:
    public class CustomNamingStrategy : INamingStrategy


        public String ClassToTableName(String className)


            return className;



        public String ColumnName(String columnName)


            return columnName;



        public String LogicalColumnName(String columnName, String propertyName)


            return columnName;



        public String PropertyToColumnName(String propertyName)


            return propertyName;



        public String PropertyToTableName(String className, String propertyName)


            return propertyName;



        public String TableName(String tableName)


            return tableName;




    cfg.SetNamingStrategy(new CustomNamingStrategy());

In general, you can trust NHibernate’s judgement, but if you wish, you can override other aspects of the mapping by providing handlers to the many events of ConventionModelMapper. Say, for example, that you want to exclude (or include) only certain classes from an assembly, you can provide an handler for the IsEntity event:

mapper.IsEntity += (type, @default, assemblyName) =>


    return typeof(IEntity).IsAssignableFrom(type);


Or you want to configure a collection as a set rather than a bag:

mapper.IsBag += (member, @default) =>


    return false;


mapper.IsSet += (member, @default) =>


    return true;


Or even set a collection as not lazy and use inner join fetching:

mapper.BeforeMapSet += (modelInspector, member, propertyCustomizer) =>






Lesser-Known NHibernate Features: Custom Loggers

Extensible as it is, it’s no wonder that NHibernate also supports injecting custom loggers. The support is twofold:

  • There’s the ILoggerFactory interface, that must be implemented by custom logger factories; this is the responsible for creating actual loggers;
  • Then there’s the IInternalLogger interface, the one that provides the common functions found in most loggers (warn, info, debug, error, fatal, etc).

By default, it uses Log4Net, but it is easy to add our own logger.

So, let’s start by implementing a logger factory – just the skeleton, I leave it to you as an exercise:

public class CustomLoggerFactory : ILoggerFactory


    public IInternalLogger LoggerFor(Type type)


        return new CustomLogger(type.FullName);



    public IInternalLogger LoggerFor(String keyName)


        return new CustomLogger(keyName);



And then the actual logger:

public class CustomLogger : IInternalLogger


    public String Key { get; private set; }


    public CustomLogger(String key)


        this.Key = key;



    public void Debug(Object message, Exception exception)




    public void Debug(Object message)




    public void DebugFormat(String format, params Object[] args)




    public void Error(Object message, Exception exception)




    public void Error(Object message)




    public void ErrorFormat(String format, params Object[] args)




    public void Fatal(Object message, Exception exception)




    public void Fatal(Object message)




    public void Info(Object message, Exception exception)




    public void Info(Object message)




    public void InfoFormat(String format, params Object[] args)




    public Boolean IsDebugEnabled


        get { return true; }



    public Boolean IsErrorEnabled


        get { return true; }



    public Boolean IsFatalEnabled


        get { return true; }



    public Boolean IsInfoEnabled


        get { return true; }



    public Boolean IsWarnEnabled


        get { return true; }



    public void Warn(Object message, Exception exception)




    public void Warn(Object message)




    public void WarnFormat(String format, params Object[] args)




Remember, this is just a skeleton, do implement these methods anyway you like.

Now, all that is left is the registration:

LoggerProvider.SetLoggersFactory(new CustomLoggerFactory());

And NHibernate will start logging using your logger!

PS – Issue NH-3776 is an attempt to make using logger factories even simpler and in a similar fashion to other pluggable features.

Lesser-Known NHibernate Features: Versioning and Optimistic Concurrency

NHibernate supports the notion of entity version. An entity version is the value of some entity property that is mapped as versioned. There are several strategies for versioning:

  • A numeric counter;
  • The current (UTC) timestamp;
  • The database timestamp;
  • The system elapsed ticks (DateTime.Ticks);
  • A database-specific row version (ROWVERSION in SQL Server, ORA_ROWSCN in Oracle, etc).


We map a version property as this:

public class VersionedEntity


    public int Id { get; protected set; }

    public int Version { get; protected set; }


public class VersionedEntityClassMapping<VersionedEntity>


    public VersionedEntityClassMapping()


        this.Id(x => x.Id, x => x.Generator(Generators.Identity));

        this.Version(x => x.Version, x => x.Type(NHibernateUtil.Int32));




If you wish to use a strategy other than auto-incrementing versioning, just replace NHibernateUtil.Int32 by another instance of a class implementing IVersionType, such as NHibernateUtil.Ticks, NHibernateUtil.Timestamp, NHibernateUtil.UtcDateTime, NHibernateUtil.DbTimestamp, NHibernateUtil.Binary (for SQL Server), or a similar one – or roll out your own strategy.

Close to the notion of versioning comes optimistic concurrency. Using optimistic concurrency control, when a record is about to be updated, it is checked against a pre-saved value or set of values (the version), together with its primary key. If it doesn’t match, the number of affected records will be 0 instead of 1, and we know something went wrong: because the primary key doesn’t change, then it has to be the version:

UPDATE some_table

SET some_column = @p1

WHERE id = @p2

AND version = @p3

Optimistic concurrency can use:

  • The version property (if any);
  • All of the entity’s properties;
  • All the dirty properties.

We just need to tell NHibernate which strategy we want to use:

public class VersionedEntityClassMapping<VersionedEntity>


    public VersionedEntityClassMapping()


        this.Id(x => x.Id, x => x.Generator(Generators.Identity));

        this.Version(x => x.Version, x => x.Type(NHibernateUtil.Int32));

        this.OptimisticLock(OptimisticLockMode.Version);//NHibernate 4.0, no need to specify since this is the default




And that’s it. Happy versioning!

Lesser-Known NHibernate Features: Filtering an Unloaded Collection

Suppose you have an entity with a collection of other entities (one to many, many to many); this collection is normally represented by some property implementing IEnumerable<T>, or one some more specific interface.

If we want to query this collection, we normally have to load all of its items. For indexed collections, it is possible to use extra-lazy laziness, which means NHibernate will only load one item at a time, instead of all at once, but this might even be worse, because it would result in multiple SELECT statements.

NHibernate offers a nice mechanism to filter and order a collection without actually loading all of its items; it’s the CreateFilter method of the ISession, and we use it like this:

   1: //get an order

   2: var order = session.Query<Order>().First();


   4: //all order details of this order where quantity is greater than one

   5: var detailsWithMoreThanOneItem = session.CreateFilter(order.Details, "WHERE Quantity > 1").List<OrderDetail>();


   7: //all order details of this order sorted by their quantity in descending order

   8: var detailsSortedByQuantityInDescendingOrder = session.CreateFilter(o.Details, "ORDER BY Quantity DESC").List<OrderDetail>();

I even once wrote a helper method for querying collections with LINQ without actually loading them; its usage is:

   1: //all order details of this order where quantity is greater than one

   2: var detailsWithMoreThanOneItem = order.Details.Query().Where(d => d.Quantity > 1).ToList();

The Query extension method will check to see if the collection has already been loaded, in which case, it resorts to LINQ to Objects, otherwise, it generates a proper LINQ query. You can find the source code here.

Lesser-Known NHibernate Features: Result Transformers

A result transformer, in NHibernate, is some class that implements the IResultTransformer interface:

   1: public interface IResultTransformer

   2: {

   3:     IList TransformList(IList collection);

   4:     Object TransformTuple(Object[] tuple, String[] aliases);

   5: }

Most query APIs, except LINQ, support specifying a result transformer. So, what is a result transformer used for? Just what the name says: it turns the values obtained from a query into some object. Normally, we just let NHibernate transform these values into instances of our entities, but we may want to do something different, either because we haven’t mapped some class that we want to use, or because we are not returning all of the entity’s properties, etc.

NHibernate includes some result transformers:

  • AliasToBeanResultTransformer: allows to transform a result to a user specified class which will be populated via setter methods or fields matching the alias names;
  • AliasToBeanConstructorResultTransformer: identical to AliasToBeanResultTransformer, but we specify a constructor for creating new instances of the target class;
  • AliasToEntityMapResultTransformer: returns a dictionary where the keys are the aliases and the values the corresponding columns;
  • AliasedTupleSubsetResultTransformer: ignores a tuple element if its corresponding alias is null;
  • CacheableResultTransformer: used to transform tuples to a value(s) that can be cached;
  • DistinctRootEntityResultTransformer: for joined queries, returns distinct root entities only;
  • PassThroughResultTransformer: just returns the row as it was obtained from the database;
  • RootEntityResultTransformer; returns the root entity of a joined query;
  • ToListResultTransformer: transforms each result row from a tuple into a IList, such that what you end up with is a IList of ILists.

All of these can be obtained from static properties in class NHibernate.Transform.Transformers. NHibernate implicitly uses some of these, for example, LINQ queries always use DistinctRootEntityResultTransformer.

It is easy to build our own transformer. Have a look at the following example:

   1: public class ExpressionsResultTransformer : IResultTransformer

   2: {

   3:     private readonly Type type;

   4:     private readonly Func<Object> constructorFunc;

   5:     private readonly ConstructorInfo constructor;

   6:     private readonly Object[] parameters;

   7:     private readonly Dictionary<Int32, String> expressions = new Dictionary<Int32, String>();

   8:     private PropertyDescriptorCollection props;


  10:     public ExpressionsResultTransformer(ConstructorInfo constructor, params Object[] parameters)

  11:     {

  12:         this.constructor = constructor;

  13:         this.parameters = parameters;

  14:     }


  16:     public ExpressionsResultTransformer(Func<Object> constructorFunc)

  17:     {

  18:         this.constructorFunc = constructorFunc;

  19:     }


  21:     public ExpressionsResultTransformer(Type type)

  22:     {

  23:         this.type = type;

  24:     }


  26:     private Object CreateInstance()

  27:     {

  28:         if (this.type != null)

  29:         {

  30:             return (Activator.CreateInstance(this.type));

  31:         }

  32:         else if (this.constructorFunc != null)

  33:         {

  34:             return (this.constructorFunc());

  35:         }

  36:         else

  37:         {

  38:             return (this.constructor.Invoke(this.parameters));

  39:         }

  40:     }


  42:     public ExpressionsResultTransformer Add(Int32 index, String property)

  43:     {

  44:         this.expressions[index] = property;

  45:         return (this);

  46:     }


  48:     public ExpressionsResultTransformer Add(params String[] properties)

  49:     {

  50:         foreach (var property in properties)

  51:         {

  52:             this.Add(property);

  53:         }


  55:         return (this);

  56:     }


  58:     public ExpressionsResultTransformer Add(String property)

  59:     {

  60:         var max = this.expressions.Keys.DefaultIfEmpty(-1).Max();

  61:         return (this.Add(max + 1, property));

  62:     }


  64:     public static ExpressionsResultTransformer Add<T>(params String[] properties) where T : new()

  65:     {

  66:         return (new ExpressionsResultTransformer<T>().Add(properties));

  67:     }


  69:     #region IResultTransformer Members


  71:     IList IResultTransformer.TransformList(IList collection)

  72:     {

  73:         return (collection);

  74:     }


  76:     Object IResultTransformer.TransformTuple(Object[] tuple, String[] aliases)

  77:     {

  78:         var entity = this.CreateInstance();


  80:         if (this.props == null)

  81:         {

  82:             this.props = TypeDescriptor.GetProperties(entity);

  83:         }


  85:         foreach (var expression in this.expressions)

  86:         {

  87:             this.props[expression.Value].SetValue(entity, tuple[expression.Key]);

  88:         }


  90:         return (entity);

  91:     }


  93:     #endregion

  94: }


  96: public sealed class ExpressionsResultTransformer<T> : ExpressionsResultTransformer where T : new()

  97: {

  98:     public ExpressionsResultTransformer() : base(typeof(T))

  99:     {

 100:     }


 102:     public ExpressionsResultTransformer(Func<T> constructor) : base(new Func<Object>(() => (Object) constructor()))

 103:     {

 104:     }


 106:     public ExpressionsResultTransformer(ConstructorInfo constructor, params Object[] parameters) : base(constructor, parameters)

 107:     {

 108:     }

 109: }

The TransformTuple method is the one used to turn each returned record into an instance of something. TransformList is called at the end, when all the records have been processed.

The ExpressionResultTransformer class allows us to select which indexes, in the database record, map to which properties in some entity. For our convenience, it offers a number of options to construct an instance (type, constructor + parameters and delegate). We would use it like this:

   1: var products = session.CreateSQLQuery("select p.Name, p.Price, p.ProductId from Product p").SetResultTransformer(ExpressionsResultTransformer.Add<Product>("Name", "Price", "ProductId")).List<Product>();