Hello,

I have a problem getting NHibernate to do what I want it to do. Please look at the end of this post to see one of the the mapping documents I have. Note the m_items collection as well as the m_unreadItems property that follows right after. These are the ones that are causing my grievance.

First when I load an instance of this class and I try just to get the number of items in the list, this causes the whole list to be fetched (using single "select ... " statements for each item on the list - ouch, I have to be doing something wrong here. Any ideas ?

Second the calculated property m_unreadItems is causing the NHibernate to fail with ADOException("Failed to execute query") apparently when trying to load instances of InfoCore.Category. Please, have in mind that this select is not what I really mean, but rather me trying to get the formula thing to work at all. (To get the first issue, I had to comment out this property so it won't trouble NHibernate)


I would really appreciate any ideas!

Regards,

Petar

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

NHibernate version: 1.0.2

Mapping documents:

Code:

  <class name="InfoCore.Category, InfoCore" table="category">
    <id name="m_id" column="id" type="Int32" access="field" unsaved-value="0">
      <generator class="native" />
    </id>
    <discriminator column="cat_type" type="String"/>
    <property name="m_name" access="field" />
    <many-to-one name="m_parent" class="InfoCore.FolderCategory" access="field" not-null="true" cascade="save-update"/>
    <list name="m_items" access="field" table="category_item" cascade="save-update" lazy="true" >
      <key column="category_id" foreign-key="id"/>
      <index column="idx"/>
      <many-to-many class="IItem" column="item_id"/>
    </list>
    <property name="m_unreadItems" access="field" type="Int32" formula="(select count(elements(cate.m_items)) from category cate where cate.id = id)" />
  </class>

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:MSSQL Express 2005

The generated SQL (show_sql=true):NHibernate: SELECT m_categori0_.m_parent as m_parent__, m_categori0_.id as id__, m_categori0_.idx as idx__, m_categori0_.id as id0_, m_categori0_.cat_type as cat_type0_, m_categori0_.m_parent as m_parent0_, m_categori0_.m_name as m_name0_, m_categori0_.m_channel as m_channel0_, (select count(elements(cate.m_items)) from category cate where cate.id = m_categori0_.id) as f0_0_ FROM category m_categori0_ WHERE m_categori0_.m_parent=@p0
@p0 = '1'


Debug level Hibernate log excerpt:

Hi,

I just resolved "the second" issue described in my previous post. Turns out I was trying to mix HQL and SQL ("elements" was not recognized at all). The list count issue still stands though.

Regards,

Petar

P.S. I am getting the book "Hibernate in action" on tuesday. Probably would have answered all my questions ...

Have you tried with (from http://www.hibernate.org/hib_docs/nhibe ... tions-lazy) :

You can use the Filter() method of the NHibernate ISession API to get the size of a collection without initializing
it:
ICollection countColl = s.Filter( collection, "select count(*)" );
IEnumerator countEn = countColl.GetEnumerator();
countEn.MoveNext();
int count = (int) countEn.Current;

Hi,

luxspes, this is a workaround, I was hoping I was doing something wrong which made it behave this way.
If I go this route I have to implement my own IList implementation where I would forward all method implementation to the NHibernate provided (in this case lazy)collection except for the getter of the property Count and Add/Remove methods, where I would use the pointer you kindly provided and do some smart magic to account for adding/removing of items in the collection.
After that I would have to inject this IList implementation in the class that is supposed to have this collection.

Well now that I wrote it down is not that much, but I thought this would be done by NHibernate.

Thanx for the pointer !

Best regards,

Petar