I have a class Resume with a collection of References. A Reference is a subclass of Contact. I would like to filter that collection to include only those References with Deleted = 0. Deleted is a property of Contact.

When I include --> where="Deleted=0" <--- in my Resume mapping file, I receive an error. NHib tells me Deleted isn't in my joined-subclass table. duh!

How can I filter the list of subclasses by a property of the super class? I suspect this problem can be resolved with a little creative mapping, but I'm at a loss as to what that is. Relevant details below.

tia,
ollie


Hibernate version: 1.2.0.4

Mapping documents:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

<class name="Domain.Contact" table="tContact" where="Deleted=0" lazy="true">

<id name="_id" column="ContactID" access="field">
<generator class="identity"/>
</id>

<property name="FirstName" />
<property name="MiddleInitial" />
<property name="LastName" />

<property name="IsDeleted" column="Deleted" />

<joined-subclass name="Domain.Reference" table="tResume_Reference" lazy="true">
<key column="ReferenceID" />
<property name="SortOrderIndex" column="SortOrder" />
<property name="Comments" />
<many-to-one name="_resume" access="field" class="Domain.Resume" column="ResumeID" not-null="true" fetch="select" cascade="save-update" />
</joined-subclass>

</class>

<class name="Domain.Resume" table="tResume" lazy="true">

<id name="_id" column="ResumeID" access="field">
<generator class="identity"/>
</id>

<property name="Summary" not-null="false" />

<list name="_references" access="field" cascade="save-update" fetch="select" inverse="true" lazy="true" generic="true" where="Deleted=0">
<key column="ResumeID" />
<index column="SortOrder" />
<one-to-many class="Domain.Reference" />
</list>

</class>
</hibernate-mapping>

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

IResume resume = (Resume)session.Load(typeof(Resume), 194);

Full stack trace of any exception that occurs:

at NHibernate.Proxy.CastleLazyInitializer.Intercept(IInvocation invocation, Object[] args)
at CProxyTypeDomainResumeDomain_NHibernate_ProxyINHibernateProxy1.ModifyChild(Object child, ChildAction action)
at Domain.Reference.set_Resume(IResume value) in C:\Website\Core\Domain\Reference.cs:line 56
at Domain.Reference..ctor(IResume resume, String title, String firstName, String lastName) in C:\Website\Core\Domain\Reference.cs:line 38
at Tests.Domain.ReadTests.ExecuteRead() in C:\Website\Tests\Domain\ReadTests.cs:line 207
--LazyInitializationException
at NHibernate.Loader.Loader.LoadCollection(ISessionImplementor session, Object id, IType type)
at NHibernate.Loader.Collection.CollectionLoader.Initialize(Object id, 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)
--ADOException
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.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
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.LoadCollection(ISessionImplementor session, Object id, IType type)

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

The generated SQL (show_sql=true):

Tests.Domain.ReadTests.ExecuteRead : NHibernate.LazyInitializationException : Failed to lazily initialize a collection
----> NHibernate.ADOException : could not initialize a collection: [Domain.Resume._references#194][SQL: SELECT reference0_.ResumeID as ResumeID__1_, reference0_.ReferenceID as Referenc1_1_, reference0_.SortOrder as SortOrder__1_, reference0_.ReferenceID as ContactID10_0_, reference0_.SortOrder as SortOrder11_0_, reference0_.Comments as Comments11_0_, reference0_.ResumeID as ResumeID11_0_, reference0_1_.Title as Title10_0_, reference0_1_.FirstName as FirstName10_0_, reference0_1_.MiddleInitial as MiddleIn4_10_0_, reference0_1_.LastName as LastName10_0_, reference0_1_.Phone as Phone10_0_, reference0_1_.PhoneExtension as PhoneExt7_10_0_, reference0_1_.Phone1 as Phone8_10_0_, reference0_1_.PhoneExtension1 as PhoneExt9_10_0_, reference0_1_.Fax as Fax10_0_, reference0_1_.Email as Email10_0_, reference0_1_.DateCreated as DateCre12_10_0_, reference0_1_.DateUpdated as DateUpd13_10_0_, reference0_1_.DateDeleted as DateDel14_10_0_, reference0_1_.Deleted as Deleted10_0_ FROM tResume_Reference reference0_ inner join tContact reference0_1_ on reference0_.ReferenceID=reference0_1_.ContactID WHERE reference0_.Deleted=0 and reference0_.ResumeID=?]
----> System.Data.SqlClient.SqlException : Invalid column name 'Deleted'.

I belive that you could use filters for it. See NH documentation for details on filters...

This is what I've found there as an example:

Hi rolandz,

I ran into the same problem with the filter as I did with the WHERE clause.

Tried this:

<filter-def name="HideDeleted">
<filter-param name="IsDeleted" type="Int16" />
</filter-def>

and then in the collection:

<filter name="HideDeleted" condition=":Deleted = Deleted" />

It still tries to find Deleted in the wrong table. :(

I found a solution, though I don't care for it.

Looked at the SQL outputted to the logger during the request and noticed that all tables receive an alias. So I added the alias to the WHERE:

<list name="_references" access="field" cascade="save-update" fetch="select" inverse="true" lazy="true" generic="true" where="reference0_1_.Deleted=0">
<key column="ResumeID" />
<index column="SortOrder" />
<one-to-many class="Domain.Reference" />

That solved it.

fyi
ollie

I'm glad to see you've solved it. However it is a real hack and introduces high risk if deployed as a production solution - you don't control the names of aliases that NH provides in the queries, do you? In the future the name can change for some reason...

I've looked at the collections mapping - there is an attribute named ``schema'' - what's this? Perhaps it controls where to look for in such cases?

Cheers,

re: hack. agreed. big ol' risk. at this stage of development, however, we can risk it.

there is an alternate solution I stumbled across that works for collections of BAG but not LIST (I have no idea why): custom SQL loaders.

So if that LIST were a BAG I could do this:

<sql-query name="references">
<load-collection alias="_references" role="Resume._references" />
SELECT c.*, r.*
FROM tResume_Reference r inner join tContact c on r.ReferenceID = c.ContactID
WHERE c.Deleted=0 and r.ResumeID = :id
</sql-query>


then in my BAG I would put:

<bag name="_references" access="field" cascade="save-update" fetch="select" inverse="true" lazy="true" generic="true">
<key column="ResumeID" />
<one-to-many class="Domain.Reference" />
<loader query-ref="references" />
</bag>


This problem I posted actually occurs 4 times in our mappings and the above custom SQL loader solution works for 3 of them. The only one for which it doesn't appear to be supported is the original Reference example, the one LIST of the 4 collections.

When our app goes into GUI testing I plan on finding a better solution. I'll post back when I do.

best,
ollie

ps. i believe "schema" is a reference to which database schema to use for the tables/views in question.

I get the same problem.
Looks like NH cannot properly determine if a property in "where" clause belongs to a class or to a superclass (or don't even trying). It doesn't matter when working with simple classes, but kills all inheritance.
I don't think it can be adequately corrected with a complicated mapping.
Now I'm using so called "hack" solution, but I wonder if these table aliases are constant.
Hope this bug will be fixed.