+972 52-548-6969
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:
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'") .ExecuteUpdate();
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'") .ExecuteUpdate();
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") .ExecuteUpdate();
And generates:
All in all, this is a really awesome feature.
Thanks, Fabio.
Comments
This is amazing.
How does this affect objects in the first-level cache? Are they all automatically updated or evicted from the session?
The session-cache is not affected because executable queries are not loading entities.
As Fabio said, this does NOT affect the session cache, but it DOES affect the 2nd level cache.
So this is not HQL?
How complex would it be to try to make NHibernate update the session cache? Would that kill performance?
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.
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
Andrey,
Because the strings are the simplest thing that work, and anything else turned out to be way too much hassle
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.
would that work with criteria as well?
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
Deletes also work
Peter,
Um, don't use that that if you care about the setter side affects?
Krzysztof,
No, it will not.
@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
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
Comment preview