Summary:
I'd like to use a single column as the source for multiple foreign keys, but NHibernate does not seem to support this. I've searched around, and Gavin says that such a schema is "broken," however I'm stuck with what the DBA designed and can't change it.
The problem is pretty simple to state, actually: imagine any object graph, then add a "VersionNo" column to each entity so you can essentially maintain different versions of the entire graph. The primary key of each object becomes the combination of an identifier (in my case, a GUID) and the "VersionNo." (Note: This is different from the semantics of Hibernate's <version> mapping.)
For one such graph, I have A Taxonomy entity that contains a set of Categories, and a Category entity that contain a set of (sub)Categories. The corresponding Taxonomy table has a (TaxonomyGUID, VersionNo) PK, and the TaxonomyCategory table has a (TaxonomyCategoryGUID, VersionNo) PK. It also has a (TaxonomyGUID, VersionNo) FK and a (ParentGUID, VersionNo) FK.
The "VersionNo" column in the TaxonomyCategory table is used in 3 keys--the primary key, and two foreign keys. You can see this in the mapping below too. So when NHibernate tries to insert a row into the TaxonomyCategory table, it lists the "VersionNo" column 3 times in the INSERT statement, and the database barfs. I need a way to tell NHibernate to specify the value of the VersionNo column just once. I saw some posts that talk about setting insert="false" and update="false," but there is no ability to do that at the <key-property> level.
Is there a way for NHibernate to share a column among multiple keys (primary and foreign)?
Hibernate version:
NHibernate 1.2.0.2003
Mapping documents:
Code:
<class name="Avalere.Api.Server.Taxonomies.Taxonomy" table="Taxonomy_">
<composite-id>
<key-property name="_id" access="field" column="TaxonomyGUID"/>
<key-property name="_packageNumber" access="field" column="VersionNo" />
</composite-id>
<version name='_version' access='field' column='WorkingVersion' unsaved-value='null'/>
<set name="_rootCategories" access="field" where="RootInd = 'Y'" cascade="all-delete-orphan" inverse="true">
<key>
<column name="TaxonomyGUID"/>
<column name="VersionNo"/>
</key>
<one-to-many class="Avalere.Api.Server.Taxonomies.Category"/>
</set>
</class>
<class name="Avalere.Api.Server.Taxonomies.Category" table="TaxonomyCategory_">
<composite-id>
<key-property name="_id" access="field" column="TaxonomyCategoryGUID"/>
<key-property name="_packageNumber" access="field" column="VersionNo" />
</composite-id>
<version name='_version' access='field' column='WorkingVersion' unsaved-value='null'/>
<property name="DisplayName" column="TaxonomyCategoryName"/>
<property name="Description" column="TaxonomyCategoryDescription"/>
<many-to-one name="_parent" access="field" class="Avalere.Api.Server.Taxonomies.Category" cascade="none">
<column name="ParentGUID"/>
<column name="VersionNo"/>
</many-to-one>
<many-to-one name="Taxonomy" class="Avalere.Api.Server.Taxonomies.Taxonomy" cascade="none">
<column name="TaxonomyGUID"/>
<column name="VersionNo"/>
</many-to-one>
<property name="_dbRootIndicator" access="field" column="RootInd"/>
<set name="_subcategories" access="field" cascade="all-delete-orphan" inverse="true">
<key>
<column name="ParentGUID"/>
<column name="VersionNo"/>
</key>
<one-to-many class="Avalere.Api.Server.Taxonomies.Category"/>
</set>
</class>
Full stack trace of any exception that occurs:Code:
NHibernate.ADOException: could not insert: [Avalere.Api.Server.Taxonomies.Category#Avalere.Api.Server.Taxonomies.Category][SQL: INSERT INTO TaxonomyCategory_ (WorkingVersion, CreateTS, UpdateTS, WorkingVersionInd, TaxonomyCategoryName, TaxonomyCategoryDescription, ParentGUID, VersionNo, TaxonomyGUID, VersionNo, RootInd, TaxonomyCategoryGUID, VersionNo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> System.Data.SqlClient.SqlException: Column name 'VersionNo' appears more than once in the result column list.
Column name 'VersionNo' appears more than once in the result column list.
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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd) in C:\NHibernateBeta3\src\NHibernate\Impl\BatcherImpl.cs:line 173
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation) in C:\NHibernateBeta3\src\NHibernate\Impl\NonBatchingBatcher.cs:line 37
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session) in C:\NHibernateBeta3\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2874
--- End of inner exception stack trace ---
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session) in C:\NHibernateBeta3\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2891
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session) in C:\NHibernateBeta3\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2535
at NHibernate.Impl.ScheduledInsertion.Execute() in C:\NHibernateBeta3\src\NHibernate\Impl\ScheduledInsertion.cs:line 39
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable) in C:\NHibernateBeta3\src\NHibernate\Impl\SessionImpl.cs:line 3264
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list) in C:\NHibernateBeta3\src\NHibernate\Impl\SessionImpl.cs:line 3243
at NHibernate.Impl.SessionImpl.Execute() in C:\NHibernateBeta3\src\NHibernate\Impl\SessionImpl.cs:line 3181
at NHibernate.Impl.SessionImpl.Flush() in C:\NHibernateBeta3\src\NHibernate\Impl\SessionImpl.cs:line 3015
at NHibernate.Transaction.AdoTransaction.Commit() in C:\NHibernateBeta3\src\NHibernate\Transaction\AdoTransaction.cs:line 161
Name and version of the database you are using:
Microsoft SQL Server 2005 Standard
The generated SQL (show_sql=true):
INSERT INTO TaxonomyCategory_ (WorkingVersion, CreateTS, UpdateTS, WorkingVersionInd, TaxonomyCategoryName, TaxonomyCategoryDescription, ParentGUID, VersionNo, TaxonomyGUID, VersionNo, RootInd, TaxonomyCategoryGUID, VersionNo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)