1

On a delete context with nHibernate, when deleting a parent with child collection I would like to know why Nhibernate do a delete line by line for children (on child PK)

DELETE FROM children where Id=1
DELETE FROM children where Id=2
...    
DELETE FROM parent where id=1

Why nhibernate can't do 

DELETE FROM children where parentId=1
DELETE FROM parent where id=1

It will be more efficient if parent have 100k children for example. I search in many topics without finding a correct anwser. I did some tests too but witout success An idea ?

Cyril035
  • 11
  • 4

1 Answers1

1

That is a case, where we can use NHibernate extensibility points. The doc

19.3. Custom SQL for create, update and delete

NHibernate can use custom SQL statements for create, update, and delete operations. The class and collection persisters in NHibernate already contain a set of configuration time generated strings (insertsql, deletesql, updatesql etc.). The mapping tags <sql-insert>, <sql-delete>, and <sql-update> override these strings:

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
    <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
    <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

So, if standard deletion is not useful, we can provide our own process, including some stored procedure

<sql-delete>exec deletePerson ?</sql-delete>

Summary, in most cases, the standard model is working and effective enough. In case we need to improve SQL .. we can ...

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I saw this solution but it breaks the logical domain model. If you have some logic before deleting your entity, you probalby need to duplicate it in your storedProcedure. Same problem with casade ; In this case you need define your cascade on the database (so we have now casdade definition in 2 places.. hbm and database) – Cyril035 Feb 25 '19 at 14:25
  • It is just an extension point.. You can add your deletion logic there.. if you want to optimize.. but you can use other places... e.g. you can introduce Event listener (check here https://stackoverflow.com/q/21838758) and hook on the delete with C#... The point is - basic deletion is there, working, maybe not the most efficient.. and you do have places, where you can override the defaults.. hope it helps a bit;) – Radim Köhler Feb 25 '19 at 14:28
  • Actually I use storedprocedure with HQL. I don't have the choice because if i let nhibernate do his own stuff, it falls in timeout. I would like to understand why it can't be managed by nhibernate properly (or more efficiently) – Cyril035 Feb 25 '19 at 18:04