Problem with Updating One-To-Many (sets FK to null)

DocHoliday · **Joined:** Thu Aug 07, 2008 2:08 am **Posts:** 3

Hello.

I'm new to NHibernate and its really cool. Up to now all things worked just fine, but now I have a problem that I don't know how to solve.

I hope you can help me :-)
Hibernate version: 2.0.0.3001

Mapping documents:
Customer-Mapping:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HibernateTest" namespace="HibernateTest.DBLayer.CustomerTest">
  <class name="Customer" table="dbo.Customers" lazy="false">
    <id name="Id" column="CustomerId">
      <generator class="native"></generator>
    </id>
    <property name="Name" column="CustomerName" type="String" length="50"/>
    <property name="Address" column="CustomerAddress" type="String" length="50"/>
    <set name="Orders">
      <key>
        <column name="CustomerId" not-null="true"/>
      </key>
      <one-to-many class="Order"/>
    </set>
  </class>
</hibernate-mapping>

Order-Mapping

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HibernateTest" namespace="HibernateTest.DBLayer.CustomerTest">
  <class name="Order" table="dbo.Orders" lazy="false">
    <id name="Id" column="OrderId">
      <generator class="native"></generator>
    </id>
    <property name="Text" column="OrderText" type="String" length="50"/>
    <property name="CustomerId" type="int" not-null="true"/>
    <set name="Details">
      <key column="OrderId"/>
      <one-to-many class="OrderDetail"/>
    </set>
  </class>
</hibernate-mapping>

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

Code:

ITransaction trans = session.BeginTransaction();
try
{

   foreach (T data in Data)
   {
      if (data.IsNew)
      {
         session.Save(data);
      }
      else
      {
         session.Update(data);
      }
   }
   trans.Commit();
}
catch (Exception e)
{
   MessageBox.Show(e.Message);
   trans.Rollback();
}

Where T is substituted by the real class (in this case Customer).
Full stack trace of any exception that occurs:

Quote:

2008-08-07 08:17:55,156 DEBUG UPDATE dbo.Orders SET CustomerId = null WHERE CustomerId = @p0; @p0 = '1'
2008-08-07 08:17:55,250 WARN System.Data.SqlClient.SqlException: Der Wert NULL kann in die 'CustomerId'-Spalte, 'nhibernateTest.dbo.Orders'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.
Die Anweisung wurde beendet.
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
bei NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:Zeile 196.
bei NHibernate.Persister.Collection.AbstractCollectionPersister.Remove(Object id, ISessionImplementor session) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Persister\Collection\AbstractCollectionPersister.cs:Zeile 940.
2008-08-07 08:17:55,515 ERROR Der Wert NULL kann in die 'CustomerId'-Spalte, 'nhibernateTest.dbo.Orders'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.
Die Anweisung wurde beendet.
2008-08-07 08:17:55,531 ERROR Could not sA first chance exception of type 'NHibernate.Exceptions.GenericADOException' occurred in NHibernate.dll
ynchronize database state with session
NHibernate.Exceptions.GenericADOException: could not delete collection: [HibernateTest.DBLayer.CustomerTest.Customer.Orders#1][SQL: SQL not available] ---> System.Data.SqlClient.SqlException: Der Wert NULL kann in die 'CustomerId'-Spalte, 'nhibernateTest.dbo.Orders'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.
Die Anweisung wurde beendet.
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
bei NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:Zeile 196.
bei NHibernate.Persister.Collection.AbstractCollectionPersister.Remove(Object id, ISessionImplementor session) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Persister\Collection\AbstractCollectionPersister.cs:Zeile 940.
--- Ende der internen Ausnahmestapelüberwachung ---
bei NHibernate.Persister.Collection.AbstA first chance exception of type 'NHibernate.Exceptions.GenericADOException' occurred in NHibernate.dll
ractCollectionPersister.Remove(Object id, ISessionImplementor session) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Persister\Collection\AbstractCollectionPersister.cs:Zeile 957.
bei NHibernate.Action.CollectionRemoveAction.Execute() in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Action\CollectionRemoveAction.cs:Zeile 23.
bei NHibernate.Engine.ActionQueue.Execute(IExecutable executable) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Engine\ActionQueue.cs:Zeile 130.
bei NHibernate.Engine.ActionQueue.ExecuteActions(IList list) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Engine\ActionQueue.cs:Zeile 113.
bei NHibernate.Engine.ActionQueue.ExecuteActions() in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Engine\ActionQueue.cs:Zeile 148.
bei NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session) in c:\CSharp\NH2.0.0\nhibernate\src\NHibernate\Event\Default\AbstractFlushingEventListener.cs:Zeile 240.

Name and version of the database you are using: SQL Server 2005 Express

The generated SQL (show_sql=true):

Code:

UPDATE dbo.Orders SET CustomerId = null WHERE CustomerId = @p0; @p0 = '1'

Debug level Hibernate log excerpt:
see before.

All I do is just display all the Customers in a DataGridView and all the Order for one Customer in a second DataGridView.

I change nothing, and only call session.Update() on, in my case, two Customers, where Customer One has two Orders and Customer Two hast one Order.

When I set the CustomerId-column of the Orders-table to nullable, everything works fine, but I don't think that this was ment so, because NHibernate generates some strange SQL-Statements as posted next:

Code:

2008-08-07 08:06:53,328 DEBUG UPDATE dbo.Orders SET CustomerId = null WHERE CustomerId = @p0; @p0 = '1'
2008-08-07 08:06:53,328 DEBUG UPDATE dbo.Orders SET CustomerId = null WHERE CustomerId = @p0; @p0 = '2'
2008-08-07 08:06:53,359 DEBUG UPDATE dbo.Orders SET CustomerId = @p0 WHERE OrderId = @p1; @p0 = '1', @p1 = '1'
2008-08-07 08:06:53,359 DEBUG UPDATE dbo.Orders SET CustomerId = @p0 WHERE OrderId = @p1; @p0 = '1', @p1 = '2'
2008-08-07 08:06:53,468 DEBUG UPDATE dbo.Orders SET CustomerId = @p0 WHERE OrderId = @p1; @p0 = '2', @p1 = '3'

As you can see, at first NHibernate sets all FK-columns to null, and then updates them with their real value. I don't get the idea behind this, because I also defined this column als not-null="true" in the mapping xml.

And I don't understand why NHibernate wants to delete the Orders-Collection, because I made no changes, and when I debug, all Orders are listed in the Collection.

I hope you can help me out :)

Best Regards,
Doc Holiday

PS: I'm an Austrian and therefore the output of the exception is in German. But I think you get the idea, NHibernate tries to set a column to null which is set not nullable.

wolli · **Posted:** Thu Aug 07, 2008 3:03 am

There are two problems. First the updates of the fk in general. To change this behavior you have to define a many-to-one association between Order and Customer and set the Orders collection as the inverse end of the now bi-directional association. Have a look at the parent/child section in the docs:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/example-parentchild.html

The second problem is the "deletion" of the collection items. When you make an update on the customer object hibernate tries to perist the curren tobject state to the DB. Since hibernate does not know what's currently in the database, it first deletes the associations (which maybe different from the ones defined in you instance) and the inserts the current associations. I think this behavior will also change when you use a bi-directional assocaition, because the the association is owned by the order object and not by the customer anymore. Another way might be adding select-before-update="true" on the class, but then you get additonal selects before the update statement.

DocHoliday · **Joined:** Thu Aug 07, 2008 2:08 am **Posts:** 3

Many thanks to you wolli.

It did solve the problem :-)