Ayende @ Rahien

Refunds available at head office

NHibernate – Executable DML

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

image

With the following schema:

image

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'")
    .ExecuteUpdate();

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

image

image

image

image

image

image

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'")
    .ExecuteUpdate();

Which result in:

image

image

image

image

image

image

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")
    .ExecuteUpdate();

And generates:

image

All in all, this is a really awesome feature.

Thanks, Fabio.

Comments

len smith
05/28/2009 10:00 PM by
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
05/28/2009 10:53 PM by
Fabio Maulo

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

Ayende Rahien
05/28/2009 11:00 PM by
Ayende Rahien

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

Andres G. Aragoneses
05/29/2009 12:01 AM by
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
05/29/2009 12:03 AM by
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
05/29/2009 12:05 AM by
Ayende Rahien

Andres,

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
05/29/2009 12:17 AM by
Ayende Rahien

Andrey,

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

Peter Morris
05/29/2009 08:30 AM by
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.

Krzysztof Kozmic
05/29/2009 09:08 AM by
Krzysztof Kozmic

would that work with criteria as well?

pete w
05/29/2009 01:08 PM by
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
05/29/2009 10:08 PM by
Ayende Rahien

Peter,

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

Ayende Rahien
05/29/2009 10:08 PM by
Ayende Rahien

Krzysztof,

No, it will not.

Peter Morris
05/30/2009 10:10 AM by
Peter Morris

@Ayende

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 :-)

Regards

Pete

Ayende Rahien
05/30/2009 02:38 PM by
Ayende Rahien

Peter,

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

Comments have been closed on this topic.