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