Problem for deleting with many to many association

neozzz · **Joined:** Thu Mar 16, 2006 12:35 pm **Posts:** 17

Hello

The problem is that when I try to delete a Country that has a Visa (with a many-to-many relationship) I get an exception because he try to delete directly the Country in the table without deleting first the link between the Country and the Visa is the table country_has_visa.

How to force NHibernate to delete all links between a Visa and a Country object in the table country_has_visa before deleting a Country or Visa object ?

Without that I broke always the constraint of the foreign key in the country_has_visa table.

Thanks in advance for your help !!!

Hibernate version:
last version

Mapping documents:

Code:

<hibernate-mapping xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.0">
   <class name="ng.bcngGeo.Visa, mapping" table="visa">
      <id name="id" column="id_visa" unsaved-value="0">
         <generator class="sequence" />
      </id>
      <property name="name" column="name"/>
      
      <set name="countries" lazy="true" inverse="false" cascade="none" access="NHibernate.Generics.GenericAccessor, NHibernate.Generics" table="country_has_visa">
         <key column="visa_id"/>
         <many-to-many class="ng.bcngGeo.Country,mapping" column="country_id"/>
      </set>
      
   </class>
</hibernate-mapping>

Code:

<hibernate-mapping xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.0">

   <class name="ng.bcngGeo.Country, mapping" table="country">
      <id name="code" column="code_country" unsaved-value="0">
         <generator class="sequence" />
      </id>
      <property name="codeIso" column="code_iso"/>
      <property name="name" column="name"/>

      <set name="visas" lazy="true" inverse="true" access="NHibernate.Generics.GenericAccessor, NHibernate.Generics" table="country_has_visa" cascade="none">
         <key column="country_id"/>
         <many-to-many class="ng.bcngGeo.Visa,mapping" column="visa_id"/>
      </set>

      <set name="vaccinations" lazy="true" inverse="false" access="NHibernate.Generics.GenericAccessor, NHibernate.Generics" table="country_has_vaccination">
         <key column="country_id"/>
         <many-to-many class="ng.bcngGeo.Vaccination,mapping" column="vaccination_id"/>
      </set>
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
Exception non gérée : NHibernate.ADOException: could not delete: [ng.bcngGeo.Country#6] --
-> System.Data.OracleClient.OracleException: ORA-02292: violation de contrainte (MAPPING.F
K81778864BACBF007) d'intégrité - enregistrement fils existant

à System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int3
2 rc)
à System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, Co
mmandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList&
resultParameterOrdinals)
à System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, Oci
RowidDescriptor& rowidDescriptor)
à System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
à NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
à NHibernate.Persister.EntityPersister.Delete(Object id, Object version, Object obj, IS
essionImplementor session)

Name and version of the database you are using:
Oracle 10 g

neozzz · **Joined:** Thu Mar 16, 2006 12:35 pm **Posts:** 17

nobody know how to solve my problem ?

I need to create a trigger to delete all child element for the moment and I would prefer to use NHibernate mapping to delete all child rows ...

dado2003 · **Posted:** Fri Apr 28, 2006 10:38 am

Hmm, I can bet that I did this before and it worked...can't really find were right now and also couldn't find anything wrong with your mapping. Anyway, try clearing the set before deleting (obj.Set.Clear(); session.Delete(obj);) as a workaround before you find the problem.

dado2003 · **Posted:** Fri Apr 28, 2006 10:40 am

Oh, and you can try to set the cascade="save-update" for the set.

neozzz · **Joined:** Thu Mar 16, 2006 12:35 pm **Posts:** 17

I have already try that and it doesn't work :(

Thanks for answer.

Has someone another idea ?

BasvdB · **Posted:** Mon May 08, 2006 9:38 am

Hi neozzz,

I'm having the same kind of problem and I think that the only solution is to load every country, clear the visa collection and then save it. This will clear the many-to-many table.

After that it's obviously easy to delete countries and visa's ...

neozzz · **Joined:** Thu Mar 16, 2006 12:35 pm **Posts:** 17

BasvdB wrote:

Hi neozzz,

I'm having the same kind of problem and I think that the only solution is to load every country, clear the visa collection and then save it. This will clear the many-to-many table.

After that it's obviously easy to delete countries and visa's ...

Yes I have find this solution too but haven't choose it because NHibernate will need X query to delete the X numbers of object in the collection, so I have choose to create a trigger in order to have the best performance BUT it's really bad that NHibernate doens't have such option for many-to-many-option ! Maybe in futur release ... I hope :)

jacobilsoe · **Joined:** Wed Jun 28, 2006 8:08 am **Posts:** 5

I have the same problem. Is this feature being implemented in NHibernate and/or has it been filed as a feature request?

Regards,
Jacob Ilsø

benhyrman · **Joined:** Wed Jun 21, 2006 3:13 pm **Posts:** 110

Can you guys set show_sql to true and post the output of that?

I'm not having an issue at all with 1.2 alpha and I haven't had problems with 1.0.2 before that either.

My mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="MobysThinkTank.mobyProject.Account, mobyProject" table="Account" lazy="false">
      <id name="Id" type="Int32" unsaved-value="0" access="nosetter.camelcase">
         <column name="accountId" not-null="true" unique="true" />
         <generator class="identity" />
      </id>

      <version name="Revision" column="Revision" unsaved-value="0" access="nosetter.camelcase"/>

      <property name="Username" column="Username"/>
      <property name="Password" column="Password"/>
      <property name="EmailAddress" column="EmailAddress"/>
      <property name="CurrentLoginTime" column="CurrentLoginDate"/>
      <property name="LastLoginTime" column="LastLoginDate"/>

      <bag name="Roles" lazy="true" cascade="none" generic="true" table="AccountRoles">
         <key column="accountId"></key>
         <many-to-many class="MobysThinkTank.mobyProject.Role, mobyProject" column="roleId"></many-to-many>
      </bag>

      <bag name="ReportedIssues" lazy="true" generic="true" inverse="true" >
         <key column="reportedById"></key>
         <one-to-many class="MobysThinkTank.mobyProject.Issue, mobyProject"/>
      </bag>

      <bag name="AssignedIssues" lazy="true" generic="true" inverse="true">
         <key column="assignedToId"></key>
         <one-to-many class="MobysThinkTank.mobyProject.Issue, mobyProject"/>
      </bag>

      <one-to-one name="Profile" class="MobysThinkTank.mobyProject.Profile, mobyProject" cascade="all-delete-orphan" />
   </class>
</hibernate-mapping>

A call like account.Delete() produces the following SQL:

Code:

NHibernate: DELETE FROM AccountRoles WHERE accountId = @p0
@p0 = '17'
NHibernate: DELETE FROM Account WHERE accountId = @p0 AND Revision = @p1
@p0 = '17'
@p1 = '1'