Many-to-many: DELETE and INSERT

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

I have a bidirectional relationship between the following 3 tables:

Role <-- NewUserRole --> NewUser

In my domain, I only have a Role and a NewUser entity that are mapped with a many-to-many relationship in the mapping file.

I can successfully query a NewUser or a Role and have the related collections (eg. NewUser.Roles or Roles.NewUsers) properly loaded.

However, if I add a new Role to the NewUser.Roles collection after saving it to the database and save the NewUser entity, NHibernate will delete all the entries for that user from the NewUserRole table and then re-insert all of the existing NewUserRoles plus the new one added to the collection.

Is this by design? If not, what should I be doing to fix this?

Code:

<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2" assembly="xxx" namespace="xxx">
   <class name="NewUser" table="NewUser">
      <id name="ID" type="System.Int32" column="NewUserId" unsaved-value="0">
         <generator class="identity" />
      </id>
      <!-- Properties -->
      <property name="EmailAddress" type="System.String" column="EmailAddress" not-null="true" length="50" />
      <property name="FirstName" type="System.String" column="FirstName" not-null="true" length="50" />
      <property name="LastName" type="System.String" column="LastName" not-null="true" length="50" />
      <property name="CompanyName" type="System.String" column="CompanyName" not-null="true" length="50" />
      <property name="MiddleInitial" type="System.String" column="MiddleInitial" not-null="false" />
      <property name="JobTitle" type="System.String" column="JobTitle" not-null="false" length="50" />
      <property name="CustomerNumber" type="System.String" column="CustomerNumber" not-null="false" length="50" />
      <property name="IndustryTypes" type="System.String" column="IndustryTypes" not-null="false" length="50" />
      <property name="SecretAnswer" type="System.String" column="SecretAnswer" not-null="true" length="200" />
      <property name="NotificationFlag" type="System.Boolean" column="NotificationFlag" not-null="true" />
      <property name="RegisteredDate" type="System.DateTime" column="RegisteredDate" not-null="true" />

      <!-- The Address component (class) for a NewUser (allows user.Address.City) -->
      <component name="Address" class="Address" insert="true" update="false">
         <property name="Street" type="System.String" column="StreetAddress" not-null="false" length="50" />
         <property name="City" type="System.String" column="City" not-null="true" length="20" />
         <property name="Province" type="System.String" column="Province" not-null="true" length="50" />
         <property name="Country" type="System.String" column="Country" not-null="true" />
      </component>

      <!-- The PhoneNumber component (class) for a NewUser (allows user.PhoneNumber.WorkPhone) -->
      <component name="PhoneNumber" class="PhoneNumber" insert="true" update="true">
         <property name="WorkPhone" type="System.String" column="WorkPhone" not-null="true" length="10" />
         <property name="MobilePhone" type="System.String" column="MobilePhone" not-null="false" length="10" />
      </component>
      
      <!-- Relationships -->
      <!-- m:n relationship with Role-->
      <bag name="Roles" table="NewUserRole" inverse="false" lazy="false" fetch="join">
         <key column="NewUserId" />
         <many-to-many class="Role" column="RoleId" />
      </bag>

      <!-- m:1 relationship with SecretQuestion -->
      <many-to-one name="SecretQuestion" class="Finning.CustomerPortal.Entity.SecretQuestion, Finning.CustomerPortal.Entity" 
            fetch="select" column="SecretQuestionId" cascade="none" lazy="false" />
   </class>
</hibernate-mapping>

Code:

<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2" assembly="xxx" namespace="xxx">
   <class name="Role" table="Role">
      <id name="ID" type="System.Int32" column="RoleId" unsaved-value="0">
         <generator class="identity" />
      </id>
      <!-- Properties -->
      <property name="RoleName" type="System.String" column="RoleName" not-null="true" length="20" />
      <property name="Description" type="System.String" column="Description" not-null="true" length="50" />
   
      <!-- Relationships -->
      <!-- m:1 relationship between Role and Application -->
      <many-to-one name="Application" class="Application" fetch="join" column="ApplicationId" />

      <!-- m:n relationship between Role and NewUser -->
      <bag name="NewUsers" table="NewUserRole" inverse="true" lazy="false" fetch="join">
         <key column="RoleId" />
         <many-to-many class="NewUser" column="NewUserId" />
      </bag>
   </class>
</hibernate-mapping>

Code:

NewUser user = session.Get<NewUser>(1);
Role newrole = new Role();
newrole.RoleName = "New Role";
newrole.Description = "New Role";
newrole.Application = session.Get<Application>(1);
user.Roles.Add(newrole);
session.SaveOrUpdate(newrole);
session.SaveOrUpdate(user);
session.Flush();

karlchu · **Posted:** Tue Jun 19, 2007 4:00 pm

I do believe it is by design, see here:

http://www.hibernate.org/hib_docs/nhibe ... s-taxonomy

If you have the luxury of changing the NewUserRole table, I suggest adding a surrogate key (such as an auto-increment ID column) and use the <idbag> mapping instead.

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

Cool, thanks! Thankfully I'm the one designing the DB so I can do whatever I want to it at this point. :D I'll have a look at that.

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

I modified my NewUserRole table to have an identity PK of NewUserRoleId. The NewUserId and RoleId columns remain as FKs to the NewUser and Role tables respectively.

I also changed the <bag> elements in my Role and NewUser mappings files to the following:

Code:

<idbag name="NewUsers" table="NewUserRole" inverse="true" lazy="true" fetch="join">
   <collection-id column="NewUserRoleId" type="System.Int32">
      <generator class="identity"/>
   </collection-id>
   <key column="RoleId"/>
   <many-to-many column="NewUserId" class="NewUser" />
</idbag>

Code:

<idbag name="Roles" table="NewUserRole" inverse="false" lazy="true" fetch="join">
   <collection-id column="NewUserRoleId" type="System.Int32">
      <generator class="identity"/>
   </collection-id>
   <key column="NewUserId"/>
   <many-to-many column="RoleId" class="Role" />
</idbag>

What is happening now when I save the user with the new role in the Roles collection is I get the following exception:

{NHibernate.ADOException: could not insert collection rows: [xxx.Entity.NewUser.Roles#1] ---> System.InvalidCastException: Failed to convert parameter value from a Object to a Int32. ---> System.InvalidCastException: Object must implement IConvertible.
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Collection.AbstractCollectionPersister.InsertRows(IPersistentCollection collection, Object id, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernateTest.Program.Main(String[] args) in D:\CHumber\My Documents\Visual Studio 2005\Projects\NHibernateTest\NHibernateTest\Program.cs:line 49
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()}
[/code]

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

I just found the JIRA issue regarding this bug (NH-364) but it was referring to the 1.2 beta. Does anyone know if this was fixed in the 1.2 general release?

karlchu · **Posted:** Wed Jun 20, 2007 12:31 pm

It looks like the issue is still open.

Vote for it on JIRA if you want it to bubble up on priority.