I'm working on a pilot project for my company using NHibernate. I'm mapping legacy data in an Oracle 9i DB with lost of composite keys.

I've run into trouble trying to use the ISession.Find method when the objects I'm looking for have composite keys mapped with at least one key-many-to-one element.

I've put together a stripped down version of the model that reproduces the error.

I've run the equilalent code in Java with Hibernate 2.1 and don't get any errors.

Here we go. I've got two persistent objects, Foo and Bar. Bars have sets of associated Foos and the there is a FK (pointing back at Bar) in the Foo's PK.

Here are the mappings I'm using:

For Bar:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="BlueNile.RMS.Persistent.Bar, MappedObjects" table="BAR">
<id name="Pk1" column="PK1" unsaved-value="" >
<generator class="assigned"/>
</id>

<property name="Data" type="string" column="DATA"/>
<set name="Foos" table="FOO" cascade="all" inverse="true">
<key column="PK1"/>
<one-to-many class="BlueNile.RMS.Persistent.Foo, MappedObjects"/>
</set>
</class>
</hibernate-mapping>


For Foo:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="BlueNile.RMS.Persistent.Foo, MappedObjects" table="FOO">
<composite-id>
<key-property name="Pk2" column="PK2" type="string"/>
<key-many-to-one name="Bar" column="PK1" class="BlueNile.RMS.Persistent.Bar, MappedObjects"/>
</composite-id>

<property name="Data" type="string" column="DATA"/>
</class>
</hibernate-mapping>


The class files are as you would expect. Given this mapping, I can find a list of all Bars using:

List bars = session.Find("from Bar");

Then I can find all of the Foos by traversing the relationship. So far so good.

Here is the problem. When I try to load all of the Foos directly with:

List foos = session.Find("from Foo");

I get the exception listed below. It looks like NHibernate fails when it tries to load the associated Bar for the first Foo in the result set. The stack trace indicates that it is trying to open a second reader on the connection without closing the first. The first reader must be the one containing the resluts of the select from the foo table. The second is the one that reads the results of the select from the bar table.

So, is this a bug? Is there a workaround? Any help would be greatly appreciated.

Thanks,

Jeff

Log4net Logs:

2005-09-13 11:41:46,261 [3316] DEBUG NHibernate.Impl.SessionFactoryImpl - Instantiated session factory
2005-09-13 11:41:46,277 [3316] DEBUG NHibernate.Impl.SessionImpl - opened session
2005-09-13 11:41:46,277 [3316] DEBUG NHibernate.Transaction.AdoTransaction - begin
2005-09-13 11:41:46,277 [3316] DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
2005-09-13 11:41:47,230 [3316] DEBUG NHibernate.Impl.SessionImpl - find: from Foo
2005-09-13 11:41:47,245 [3316] DEBUG NHibernate.Hql.QueryTranslator - compiling query
2005-09-13 11:41:47,261 [3316] DEBUG NHibernate.Impl.SessionImpl - flushing session
2005-09-13 11:41:47,261 [3316] DEBUG NHibernate.Impl.SessionImpl - Flushing entities and processing referenced collections
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - Processing unreferenced collections
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - Processed 0 unreachable collections.
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - scheduling collection removes/(re)creates/updates
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - Processed 0 for recreate (0), remove (0), and update (0)
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.SessionImpl - dont need to execute flush
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Hql.QueryTranslator - HQL: from BlueNile.RMS.Persistent.Foo
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Hql.QueryTranslator - SQL: select foo0_.PK2 as PK2, foo0_.PK1 as PK1, foo0_.DATA as DATA from FOO
foo0_
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Hql.QueryTranslator - HQL: from BlueNile.RMS.Persistent.Foo
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Hql.QueryTranslator - SQL: select foo0_.PK2 as PK2, foo0_.PK1 as PK1, foo0_.DATA as DATA from FOO
foo0_
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :1
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: select foo0_.PK2 as PK2, foo0
_.PK1 as PK1, foo0_.DATA as DATA from FOO foo0_
2005-09-13 11:41:47,277 [3316] INFO NHibernate.Loader.Loader - select foo0_.PK2 as PK2, foo0_.PK1 as PK1, foo0_.DATA as DATA from FOO foo0_
2005-09-13 11:41:47,277 [3316] DEBUG NHibernate.SQL - select foo0_.PK2 as PK2, foo0_.PK1 as PK1, foo0_.DATA as DATA from FOO foo0_
NHibernate :select foo0_.PK2 as PK2, foo0_.PK1 as PK1, foo0_.DATA as DATA from FOO foo0_
2005-09-13 11:41:47,339 [3316] DEBUG NHibernate.Impl.BatcherImpl - Opened Reader, open Readers :1
2005-09-13 11:41:47,339 [3316] DEBUG NHibernate.Loader.Loader - processing result set
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Type.NullableType - returning 'b' as column: PK2
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Type.NullableType - returning 'a' as column: PK1
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Impl.SessionImpl - loading [Bar#a]
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [Bar#a]
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Impl.SessionImpl - object not resolved in any cache [BlueNile.RMS.Persistent.Bar#a]
2005-09-13 11:41:47,370 [3316] DEBUG NHibernate.Persister.EntityPersister - Materializing entity: BlueNile.RMS.Persistent.Bar#a
2005-09-13 11:41:47,402 [3316] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :2
2005-09-13 11:41:47,402 [3316] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: SELECT bar0_.PK1 as PK10_, ba
r0_.DATA as DATA0_ FROM BAR bar0_ WHERE bar0_.PK1 = :bar0_.PK1
2005-09-13 11:41:47,402 [3316] DEBUG NHibernate.Type.NullableType - binding 'a' to parameter: 0
2005-09-13 11:41:47,402 [3316] INFO NHibernate.Loader.Loader - SELECT bar0_.PK1 as PK10_, bar0_.DATA as DATA0_ FROM BAR bar0_ WHERE bar0_.PK1 =
?
2005-09-13 11:41:47,402 [3316] DEBUG NHibernate.SQL - SELECT bar0_.PK1 as PK10_, bar0_.DATA as DATA0_ FROM BAR bar0_ WHERE bar0_.PK1 = ?
NHibernate :SELECT bar0_.PK1 as PK10_, bar0_.DATA as DATA0_ FROM BAR bar0_ WHERE bar0_.PK1 = ?
2005-09-13 11:41:48,527 [3316] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands :1
2005-09-13 11:41:48,527 [3316] ERROR NHibernate.ADOException - could not load object
System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.
at System.Data.OleDb.OleDbConnection.SetStateExecuting(OleDbCommand attempt, String method, Boolean flag)
at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method, Int32& localState)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.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, Object optionalObject, Object optionalId, Ob
ject[] optionalCollectionKeys, Boolean returnProxies)
...

Yes, it's a bug and I'll look into it. You don't get errors in Java because Java is usually able to handle multiple open result sets, while ADO.NET does not allow multiple open data readers. (I don't know exactly if it's by the spec, or just most data providers behave this way.)

Thanks Sergey.

BTW - I created another mapping that I thought would have the same problem but it worked fine. This one had a composit key where both parts were key-many-to-one.

I am able to do session.Find("from UserAccessItem"); for this one without errors.

Hope that helps.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="BlueNile.RMS.Persistent.UserAccessItem, RMSCode" table="jefft.USER_ACCESS_ITEM">
<composite-id>
<key-many-to-one name="User" column="USERNAME" class="BlueNile.RMS.Persistent.User, RMSCode"/>
<key-many-to-one name="AccessItem" column="ACCESS_ITEM" class="BlueNile.RMS.Persistent.AccessItem, RMSCode"/>
</composite-id>

<many-to-one name="AccessStatus" column="ACCESS_STATUS" class="BlueNile.RMS.Persistent.AccessStatus, RMSCode"/>
<property name="Notes" column="NOTES" type="string" />

</class>
</hibernate-mapping>

I set SupportsMultipleOpenReaders to false for OleDbDriver. This should make everything work.