We've got a requirement that all table access must go through stored procedures.
I'm trying to map a "one table per subclass" inheritance strategy. The base table also has two discriminators (PartyType and ParentPartyType). My initial attempt was to use joined-subclass for User and Role, but this attempt failed because there is no support for the discriminator PartyType column. I was able to combine the two tables (Role/User and Party) into a single result set by using custom SQL for CRUD operations.
I'm using any and many-to-any for the respective parent and child relationships between the Party table and itself. A Party's parent can be a User or Role (in our real app it is at least 6 different types.) The <any> parent relationship works great, but I'm having some difficulties with the <many-to-any>. When I allow dynamic SQL on the many-to-any bag only columns ParentPartyId, PartyType, and PartyId are retrieved during the initial collection load. As soon as I replace add a custom loader that returns these fields an exception is thrown looking for a column defined in the subclass.
Two questions:
Is there a better way to map this relationship, while still keeping the type inferred party.Parent and party.Parties[0]? (It's unfortunate that I can't use a discriminator with a <joined-subclass>.)
Why would the custom loader on the Parties bag look for different columns than the dynamic SQL?
Thanks,
Dave
1.2.0 CR1
Tables:
Party: PartyId, PartyType, ParentPartyId, ParentPartyType
User: PartyId, FirstName, MiddleName, LastName
Role: PartyId, RoleName
Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Party" polymorphism="implicit" discriminator-value="-1" >
<id name="Id" column="PartyId" type="Guid">
<generator class="guid.comb" />
</id>
<discriminator column="PartyType" type="Int32" />
<any name="Parent" meta-type="Int32" id-type="Guid" >
<meta-value value="1" class="User" />
<meta-value value="2" class="Role" />
<column name="ParentPartyType" />
<column name="ParentPartyId" />
</any>
<bag name="Parties" inverse="true" lazy="true" generic="true" cascade="all" table="Party" >
<key column="ParentPartyId" />
<many-to-any meta-type="Int32" id-type="Guid">
<meta-value value="1" class="User" />
<meta-value value="2" class="Role" />
<column name="PartyType" />
<column name="PartyId" />
</many-to-any>
<!--<loader query-ref="PartySelectByParent" />-->
</bag>
<subclass name="User" discriminator-value="1">
<property name="FirstName" column="FirstName" type="String" />
<property name="MiddleName" column="MiddleName" type="String" />
<property name="LastName" column="LastName" type="String" />
<loader query-ref="UserSelect" />
</subclass>
<subclass name="Role, Csi.Rics" discriminator-value="2">
<property name="Name" column="RoleName" type="String" />
<loader query-ref="RoleSelect" />
</subclass>
</class>
<sql-query name="PartySelectByParent">
<load-collection alias="Party.Parties" role="Party.Parties"/>
<return class="Party" />
exec Rics.dbo.PartySelectByParent @parentPartyId = :ParentPartyId
</sql-query>
<sql-query name="RoleSelect">
<return class="Role" />
exec Rics.dbo.RoleSelect @roleId = :Id
</sql-query>
<sql-query name="UserSelect">
<return class="User" />
exec Rics.dbo.UserSelect @userId = :Id
</sql-query>
</hibernate-mapping>
Code:
int count = session.Get<Role>(new Guid("d019d0f8-13e5-485a-a302-85c0ba8b6587").Parties.Count;
Full stack trace of any exception that occurs:Code:
NHibernate.LazyInitializationException: Failed to lazily initialize a collection ---> NHibernate.ADOException: could not execute query
[ exec Rics.dbo.PartySelectByParent @parentPartyId = ? ]
Name: ParentPartyId - Value: d019d0f8-13e5-485a-a302-85c0ba8b6587
[SQL: exec Rics.dbo.PartySelectByParent @parentPartyId = ?] ---> System.IndexOutOfRangeException: FirstName
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Loader.Loader.Hydrate(IDataReader rs, Object id, Object obj, ILoadable persister, ISessionImplementor session, String[][] suffixedPropertyColumns)
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, Type instanceClass, EntityKey key, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List()
at NHibernate.Persister.Collection.NamedQueryCollectionInitializer.Initialize(Object key, ISessionImplementor session)
at NHibernate.Persister.Collection.AbstractCollectionPersister.Initialize(Object key, ISessionImplementor session)
at NHibernate.Impl.SessionImpl.InitializeCollection(IPersistentCollection collection, Boolean writing)
at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
--- End of inner exception stack trace ---
at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
at NHibernate.Collection.AbstractPersistentCollection.Read()
at NHibernate.Collection.Generic.PersistentGenericBag`1.get_Count()
Name and version of the database you are using:MS SQL Server 2005 SP1
The generated SQL (show_sql=true):The following SQL is generated when the custom loader is commented out:
Code:
SELECT parties0_.ParentPartyId as ParentPa4___0_, parties0_.PartyType as PartyType0_, parties0_.PartyId as PartyId0_ FROM Rics.dbo.Party parties0_ WHERE parties0_.ParentPartyId=@p0; @p0 = 'd019d0f8-13e5-485a-a302-85c0ba8b6587'
Login
FAQ
Search