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>
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Login
FAQ
Search