THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

NHibernate wonders

This ORM piece of tool just can't stop to surprise. A week ago I've found out that although NHibernate can execute stored procedure, it doesn't support output parameters - when procedure returns one, it causes failure. Today I've found another pearl. Imagine following scenario: 2 entities with many-to-many relationship between them. For example, projects and employees - project contains many employees, employee can participate in several projects. So we have ProjectsEmployees table that maps employees to projects. Done with a foreword, now the scenario itself. Project X contains 9 employees already mapped to it in the ProjectsEmployees table. We want to add another employee to the project. Just a simple INSERT operation, right? NHibernate doesn't look for easy ways. It performs the following operations:

1. DELETE FROM ProjectsEmployees WHERE ProjectID = X

2-11. INSERT INTO ProjectsEmployees VALUES()...

Instead of single INSERT we have 11 operations here. Besides unnecessary pressure on IO system and possible locks, we have much more serious consistency issue - if HR department queries table after DELETE operation but before all the INSERTs have been completed, it would receive project status that never occurred in real life - 3 employees for example. But that's the way NHibernate works - "just in case" overwrites the entire projects object.

Ah, and how did I find out? Noticed that after mapping new employee to project trigger on delete fires. WEB team developers (those actually programming with NHibernate) keep log of commands NHibernate executes in development environment. There I have seen it plain and simple.

Published Tuesday, February 17, 2009 12:06 AM by Michael Zilberstein
Filed under: ,



Sean Chambers said:

Without looking at your mapping files and with just the information you describe above two things spring to mind.

1. Your developers have defined the cascades incorrectly in the nhibernate mapping file. This is probably the case with what you describe above.


2. Your developers have not defined the inverse="true" attribute on whichever end of the relationship controls the associations. This may be the problem if the cascades are defined properly.

Either way, this is NOT a limitation of NHibernate, but rather your development team is using the tool incorrectly. So, in the future would you please make sure to check with the NHibernate documentation and your development before jumping to conclusions about NHibernate which you already clearly have an animosity towards.

Spreading incorrect information such as this is very detrimental to people actively looking to use the tool properly.

February 16, 2009 8:07 PM

Michael Zilberstein said:


I asked my developers to check the "cascade" and "inverse" settings. We even tried to play with different options together - meanwhile with no results but they continue to check. Anyway, what I described is _default_ behavior (at least I was assured so by developers). And that's just not the way to work with database! I have a list of such pearls, starting from what I wrote in my post - that NHibernate doesn't support output parameters from stored procedures. If procedure returns page from some long list and total number of rows in the list, the total number of rows I would like to return as output parameter. Ad I can't - I have to add it as a column to the resultset - meaning for every row instead of returning it just once. Another issue - when I want to update one column, there're 2 options: first - to save object meaning update _all_ mapped columns (while all besides one will be updated with the same value they had earlier), second - execute select before update in order to determine which column(s) to update.

No doubt, NHibernate greatly simplifies development of non-database layers. But the price for DB is too high.

February 17, 2009 5:00 AM

Gauthier Segay said:

Michael, as Sean said, the collection is not mapped correctly (must be set to inverse=true), then you can get the correct behaviour (one insert).

as for output parameter, I didn't encounter this in nhibernate app, but you could work around easily by calling the procedure with a plain command, you can do this by getting the Connection property of the ISession instance and enlisting the IDbCommand to it's transaction property.

There is also the MultiQuery option and separating the treatment into separated procedures, theses would be called in a single roundtrip.

Last workaround is for your developer to log an issue for output parameter support.

Also for your update question, there is concurrency and dynamic update settings that could help with that, check :

Hope this helps.

April 25, 2009 9:44 AM

Michael Zilberstein said:


Thank you for your help. Developers found some way to fix the behavior, using inverse setting as you and Sean said and something else (don't know what exactly - I'm on DB side :-)).

As for output parameter - I know about command but if I use - what is NHibernate for? The hole point was that developers now work with NHibernate as data access layer instead of ADO.Net. So we new about the workaround from the beginning, that's a question of concept.

April 25, 2009 10:01 AM

Gauthier Segay said:

Ok Michael, so that's just a semantic problem: "over" and not "instead": "developers now work with NHibernate over".

That is NHibernate will never avoid you to get down to the inner workings, you simply use NHibernate where it make sense and when it is needed (hopefully, everything has not to be written manually with anymore), NHibernate just sits atop

It's like saying "why use stored procedure where you can't pass table variables around multiple calls when everything could be transact-sql script", reality is that you use stored procedure most of the time and fallback to plain unnested scripts when you are forced to.

Feel free to blog about other "wonders" you find about NHibernate (or any other ORM for the matter), it's all about getting a better product that suits the dba and the application developers sides.

April 25, 2009 4:03 PM

Arnis L. said:

"Inverse" or not, that still does not solve output parameters problem. :/

May 12, 2009 8:54 AM

Steve said:


If I set 'inverse='true'', then I do not get the join table insert to occur.

August 17, 2009 12:19 PM
New Comments to this post are disabled
Privacy Statement