Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,026 | Comments: 44,842

filter by tags archive

NHibernate – Executable DML

time to read 4 min | 712 words

This is a new feature of NHibernate that Fabio has recently ported. Using the same model that I have talked about before:


With the following schema:


The feature is basically this, NHibernate can now execute set based operation on your model. This include all Data Modification Language operations, so we are talking about Update, Insert and Delete. Let us make things a bit interesting and talk about the following statement, which hopefully will make things clearer:

s.CreateQuery("update Owner o set o.Name = 'a' where o.Name = 'b'")

Executing this code will make NHibernate execute the following SQL statements:







As you can see, we have executed a very simple query against the model, which translate to a fairly complex data model (needing to update three separate tables. We can also see that we are doing significant effort to maintain the illusion of a single query (that is why we need the temp table here).

But we are not limited to just updates, we can also do deletes:

s.CreateQuery("delete Owner o where o.Name = 'b'")

Which result in:







I think that by now you are already familiar with the pattern :-)

As for insert statements, they are supported as well, but there are some limitations. In particular, you have to use an identifiers generation strategy that can generate identifiers in the database (sequence or identity), and there are some limitation on how you can make this work in several complex hierarchies. Using a simple Table Per Class, the following HQL works:

s.CreateQuery("insert into Individual (Name, Email) select i.Name, i.Email from Individual i")

And generates:


All in all, this is a really awesome feature.

Thanks, Fabio.


len smith

This is amazing.

How does this affect objects in the first-level cache? Are they all automatically updated or evicted from the session?

Fabio Maulo

The session-cache is not affected because executable queries are not loading entities.

Ayende Rahien

As Fabio said, this does NOT affect the session cache, but it DOES affect the 2nd level cache.

Andres G. Aragoneses

So this is not HQL?

How complex would it be to try to make NHibernate update the session cache? Would that kill performance?

Andrey Shchekin

Looks good (actually more like a mandatory feature, at least for updates). But why you use string-based HQL so often in your samples? From the author of NHQG this is surprising.

Ayende Rahien


Those things are happening in the database, outside of NHibernate. We don't want to update the session cache, it would be incredibly complex, and break the state equality between NH & the DB

Ayende Rahien


Because the strings are the simplest thing that work, and anything else turned out to be way too much hassle

Peter Morris

What if someone later adds setter code to o.Name? What if the data update breaks invariants?

I assume this is something which should really be used quite rarely.

pete w

This is major!

Good to know. En-masse updates has always been the achilles heel of ORMs. I write stored procedures for this kind of thing, and then invalidate all caches/sessions after the fact.

What would be nice is to do the same thing for en-masse deletes. Large-scale deletions are a common use case and it would be nice to do without loading each object, or falling back on a SPROC.

Nice work fabio! thank you for all the help, you rock

Ayende Rahien


Um, don't use that that if you care about the setter side affects?

Peter Morris


That's my point. When you first use some DML in an app there might not be side affects, but later on some may be introduced and the DML will bypass them.

I'm just pointing out that this is really something I think should be used in moderation, and with great care :-)



Ayende Rahien


Not really. It is not something that you would use for everything, but it is something that can be extremely useful in many aspects.

In particular, it can make certain tasks that would require you to write SQL so much easier.

Since obviously there is a disconnect between those sort of operations and in memory ones, it is pretty clear what is going to happen

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Technical observations from my wife (3):
    13 Nov 2015 - Production issues
  2. Production postmortem (13):
    13 Nov 2015 - The case of the “it is slow on that machine (only)”
  3. Speaking (5):
    09 Nov 2015 - Community talk in Kiev, Ukraine–What does it take to be a good developer
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats