Nhibernate efficient cascade delete

Question

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 ?

Answer 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 ...