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();

Published by

Ricardo Peres

Team Leader at Dixons Carphone. Microsoft MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *