I'm having some trouble querying out the contents of a set relation using either HQL or Criteria queries.
For the purposes of describing this problem, I have three tables that map to two classes: Tasks, TaskItems, and Items. The Tasks and Items tables map to classes directly. TaskItems is used to persist the Task class having an ISet<Item> collection property. The TaskItems table consists solely of the primary keys of the Task and Item tables that are connected.
For reasons having to do with the more complex reality of my setup, there is no direct relationship from the Items back to the Tasks at the class level.
I'm trying to write a query, either in HQL or as a Criteria query, that gives me the distinct Items that are referenced by Tasks with certain (simple) conditions. I can express the conditions trivially in either HQL or Criteria queries. However, getting the distinct contents of the Task.Items sets is proving difficult.
I got NHibernate to run the query I want it to with this HQL:
select distinct contents(t.Items) from Tasks t where t.Property = 2
NHibernate properly runs something like:
select distinct ti.item_id from tasks t inner join taskitems ti on t.task_id = ti.task_id
And then starts trying to load the actual item records, and that's where the problem occurs. Hibernate tries to run the "select * from items where item_id = :p0" query before it finishes reading the results from the the "select distinct ..." query, and PostgreSQL/Npgsql does not like this and throws an exception.
I did some more digging, and this appears to have something to do with a recent optimization in the beta versions of npgsql. Specifically, npgsql used to pre-buffer all the results into the data reader, where now it streams the results as needed. The release notes for the version in which this was introduced (
http://pgfoundry.org/frs/shownotes.php?release_id=1124) indicate that the ADO.NET API does not technically allow working with a connection while an IDataReader is open, but that the prior behavior of npgsql happened to allow that as a side effect. It appears from looking at how NHibernate is operating that running this hql query would require support for that improper behavior on any database.
I can work around this by turning off the streaming behavior with a connection string setting, but that's undesirable, and from what I can understand of this issue, it seems like something that may need fixing generally.
From a quick look at the code, it seems like if I could somehow tell the query to return proxies with just the PK filled instead of fully populated objects, that would work. Criteria queries seem to be more prone to returning proxies, but I could not find a way to express that query as a criteria query.
Hibernate version: 1.2.0
Name and version of the database you are using: PostgreSQL 8.3.3 / npgsql 2.0.0 Beta 4
Stack trace of exception:
Code:
at Npgsql.NpgsqlCommand.CheckConnectionState()
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
at Npgsql.NpgsqlCommand.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.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, Type optionalEntityName, Object optionalIdentifier, IEntityPersister persister)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, Type optionalEntityName, Object optionalIdentifier, IEntityPersister persister)
at NHibernate.Loader.Entity.AbstractEntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId)
at NHibernate.Loader.Entity.AbstractEntityLoader.Load(Object id, Object optionalObject, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
at NHibernate.Impl.SessionImpl.DoLoad(Type theClass, Object id, Object optionalObject, LockMode lockMode, Boolean checkDeleted)
at NHibernate.Impl.SessionImpl.DoLoadByClass(Type clazz, Object id, Boolean checkDeleted, Boolean allowProxyCreation)
at NHibernate.Impl.SessionImpl.InternalLoad(Type clazz, Object id, Boolean eager, Boolean isNullable)
at NHibernate.Type.EntityType.ResolveIdentifier(Object id, ISessionImplementor session)
at NHibernate.Type.EntityType.ResolveIdentifier(Object id, ISessionImplementor session, Object owner)
at NHibernate.Type.EntityType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Hql.Classic.QueryTranslator.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, 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)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Hql.Classic.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.QueryImpl.List(IList results)
Login
FAQ
Search