Hi,
i guess this is a standard newbie problem:

I have a Biderectional association of an Invoice to its Details.

The Details are declared as "bag" of the Invoice, the problem ist when i call the Save Method hibernate saves my Invoice but cant Insert the Details Records because of null value in the foreign key column to the Invoice.


Hibernate version:
1.0.4.0

Mapping documents:
<class name="Hobex.CRM.Invoice, Hobex.CRM.Models" table="TerminalInvoice">
<id name="Id" type="Int32" column="TerminalInvoiceID" access="field.pascalcase-m-underscore">
<generator class="identity" />
</id>
<property name="Number" column="InvoiceNumber" type="String" />
<property name="State" column="InvoiceStatusID" type="Int32" />
<property name="CreateDate" column="CreateDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<property name="CreateUser" column="CreateUser" type="String" />
<property name="ModifyDate" column="ModifyDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<property name="ModifyUser" column="ModifyUser" type="String" />
<property name="IntendedPurposeLineOne" column="IntendedPurpose1" type="String" />
<property name="IntendedPurposeLineTwo" column="IntendedPurpose2" type="String" />
<property name="IntendedPurposeLineThree" column="IntendedPurpose3" type="String" />
<property name="Information" column="InfoText" type="String" />
<property name="EnquiryDate" column="EnquiryDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<property name="Expenses" column="Spesen" type="Decimal" />
<property name="Amount" column="Amount" type="Decimal" />
<property name="Date" column="InvoiceDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<many-to-one name="Terminal" column="TerminalID" class="Hobex.CRM.Terminal, Hobex.CRM.Models" />
<many-to-one name="Kind" column="InvoiceKindID" class="Hobex.CRM.InvoiceKind, Hobex.CRM.Models" />
<many-to-one name="Tax" column="UstId" class="Hobex.CRM.Tax, Hobex.CRM.Models" />
<bag name="Details" inverse="true" lazy="true" cascade="all">
<key column="TerminalInvoiceId"></key>
<one-to-many class="Hobex.CRM.InvoiceDetails, Hobex.CRM.Models" />
</bag>

</class>
<class name="Hobex.CRM.InvoiceDetails, Hobex.CRM.Models" table="TerminalInvoiceDetails">
<id name="Id" type="Int32" column="TerminalInvoiceDetailsID" access="field.pascalcase-m-underscore">
<generator class="identity" />
</id>
<property name="Number" column="ProductNumber" type="String" />
<property name="Name" column="ProductName" type="String" />
<property name="UnitCount" column="ProductCount" type="Int32" />
<property name="PricePerUnit" column="ProductPrice" type="Decimal" />
<property name="CreateDate" column="CreateDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<property name="CreateUser" column="CreateUser" type="String" />
<property name="ModifyDate" column="ModifyDate" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
<property name="ModifyUser" column="ModifyUser" type="String" />
<many-to-one name="Invoice" column="TerminalInvoiceId" class="Hobex.CRM.Invoice, Hobex.CRM.Models"/>
</class>

Full stack trace of any exception that occurs:
Test method Hobex.CRM.TestProject.TestClassTerminalInvoice.ImportTerminalInvoices threw exception: NHibernate.ADOException: could not insert: [Hobex.CRM.InvoiceDetails] ---> System.Data.SqlClient.SqlException: Der Wert NULL kann in die TerminalInvoiceId-Spalte, hobexDB.dbo.TerminalInvoiceDetails-Tabelle, nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei INSERT.
Die Anweisung wurde beendet..

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at Hobex.CRM.Business.InvoiceDomain.ImportInvoices(String FileName) in D:\Source\Hobex\C#\Hobex\CRM\Hobex.CRM\Hobex.CRM\Hobex.CRM.Business\InvoiceDomain.cs:line 263
at Hobex.CRM.TestProject.TestClassTerminalInvoice.ImportTerminalInvoices() in D:\Source\Hobex\C#\Hobex\CRM\Hobex.CRM\Hobex.CRM\TestProject\Hobex.CRM.TerminalInvoice.cs:line 35

Name and version of the database you are using:
MS SQL SERVER 2005

The generated SQL (show_sql=true):
NHibernate: INSERT INTO dbo.TerminalInvoice (InvoiceStatusID, TerminalID, Spesen, EnquiryDate, ModifyUser, InvoiceDate, InvoiceNumber, ModifyDate, IntendedPurpose1, IntendedPurpose3, IntendedPurpose2, CreateDate, UstId, Amount, InfoText, InvoiceKindID, CreateUser) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16); select SCOPE_IDENTITY()
@p0 = '1'
@p1 = '15245'
@p2 = '0'
@p3 = ''
@p4 = ''
@p5 = '05/23/2006 00:00:00'
@p6 = '39'
@p7 = ''
@p8 = ''
@p9 = ''
@p10 = ''
@p11 = '07/05/2007 15:55:23'
@p12 = '1'
@p13 = '149'
@p14 = ''
@p15 = '2'
@p16 = '****'
NHibernate: INSERT INTO dbo.TerminalInvoiceDetails (ProductPrice, ProductName, TerminalInvoiceId, ProductCount, CreateUser, ProductNumber, ModifyDate, CreateDate, ModifyUser) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY()
@p0 = '149'
@p1 = '****'
@p2 = ''
@p3 = '1'
@p4 = ''
@p5 = ''
@p6 = ''
@p7 = ''
@p8 = ''

When NHibernate cascade saves children entities, it first inserts the entities with null foreign key. It then updates the foreign key column to associate the children entities with the parent entity. There is a little blurp in the book Hibernate In Action that explains the rationale. If memory serves, I believe it is because if the child entity is a full-fledge entity, then it has its own life-cycle, and it should be able to exist on its own. The association to a parent is kind of "after the fact".

There are two ways to fix this:

1) Change the foreign key column to be nullable
2) Manually manage cascading

I don't think NHibernate 1.2 behaves any differently, but you can give that a try if you are using the .net 2.0 framework.

Karl, this is a parent-child relationship, for which non-null foreign keys are possible. Everything is mapped correctly to enable this behavior.

Alter: the most likely problem in this case is that you did not set the "Invoice" property on InvoiceDetails before saving. With a bidirectional relationship, both ends need to be set manually. Of course, this can be built into a method on the parent like so:

Thanks for the correction.

Hi guys,

Thanks for your help, i added the invoice object in my Create Methods like marcal said works fine

thanks again
bye
AS