CODEWITHSUNDEEP
nhibernateorm
k0lpak
k0lpak

Reputation: 553

many-to-many cascade delete performance

I have 2 entities User and Role

Role.hbm.xml :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="UserManagmentStudio.Domain.Model"
                   assembly="UserManagmentStudio.Domain">
  <class name="Role" table="Roles" lazy="true"  dynamic-update="false">
    <id name="Id" column="Id" type="int">
      <generator class="identity" />
    </id>
    <property name="Name" not-null="true" length="50" />
    <set name="Users" table="UsersInRoles"  fetch="subselect" lazy="extra" cascade="delete">
      <key column="RoleId"/>
      <many-to-many column="UserId"  class="User"/>
    </set>
  </class>
</hibernate-mapping>

User.hbm.sql:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="UserManagmentStudio.Domain.Model"
                   assembly="UserManagmentStudio.Domain">
  <class name="User" table="Users"  dynamic-update="false">
    <id name="Id" column="Id" type="int">
      <generator class="identity" />
    </id>
    <property name="Password" not-null="true" length="50"/>
    <property name="FirstName" not-null="true" length="50"/>
    <property name="LastName" not-null="true" length="50"/>
    <property name="Login" not-null="true" length="50"/>
    <property name="Birthday" not-null="true"/>
    <property name="IsActive" not-null="true" />
    <property name="RegistrationDate" not-null="true" />
    <set name="Roles" table="UsersInRoles" fetch="subselect" lazy="extra" cascade="none" >
      <key column="UserId" />
      <many-to-many column="RoleId"  class="Role"/>
    </set>
  </class>
</hibernate-mapping>

When i delete role:

Session.Delete(role);

If it's role contains 1000 users, NHibernate generates 1000 delete query to User table and 1000 delete query to UsersInRoles, instead of 1 to Users and 1 to UsersInRoles.

Upvotes: 1

Views: 250

Answers (2)

Paul Turner
Paul Turner

Reputation: 39645

The simplest thing to do is to tell NHibernate not to cascade deletes and enable the cascade in your database (if your database supports it). For SQL Server, the cascade option is under a foreign key's settings.

This means that NHibernate will issue a single delete statement for the parent record and the database will cascade the operation internally.

Upvotes: 1

jnicolau
jnicolau

Reputation: 106

Why don't you just enable Delete on Cascade directly in the database?

Upvotes: 1

Related Questions