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

Hibernate version:

NHibernate 2.0.1.4000

Mapping documents:

LineItemVersion.hbm.xml :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="LSC.LIDB.DVO.LineItemVersion, LSC.LIDB.DVO" table="LINEITEM_VERSIONS">
<id name="Id" column="ID" type="Int32">
<generator class="identity" />
</id>
...
<bag name="ChapterVersions" table="CHAPTER_LINEITEM_VERSIONS" inverse="true" order-by="CHAPTER_VERSION_ID">
<key column="LINE_ITEM_VERSION_ID" />
<many-to-many class="LSC.LIDB.DVO.ChapterVersion, LSC.LIDB.DVO" column="CHAPTER_VERSION_ID"/>
</bag>
</class>
</hibernate-mapping>

ChapterVersion.hbm.xml :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="LSC.LIDB.DVO.ChapterVersion, LSC.LIDB.DVO" table="CHAPTER_VERSIONS">
<id name="Id" column="ID" type="Int32">
<generator class="identity" />
</id>
...
<list name="LineItemVersions" table="CHAPTER_LINEITEM_VERSIONS">
<key column="CHAPTER_VERSION_ID" />
<index column="LINE_ITEM_VERSION_ORDER"/>
<many-to-many class="LSC.LIDB.DVO.LineItemVersion, LSC.LIDB.DVO" column="LINE_ITEM_VERSION_ID" />
</list>
</class>
</hibernate-mapping>


Right, so I have a many-to-many relationship between ChapterVersion and LineItemVersion. ChapterVersion now has a list of LineItemVersions with an approprite index. This works find.

LineItemVersion has a bag of ChapterVersions, with an order-by attribute set, so when retreived from the database, ChapterVersions come back in order of creation (ascending auto ids). This works fine, when requesting ChapterVersions from a LineItemVersion when the NHibernate Cache is clear.

However, I have created a unit test to test this logic, and when I create the LineItemVersion and ChapterVersions, and save them, they go into the nhibernate cache, and when I request myLineItemVersion.ChapterVersions, no sql is executed, and the order-by clause has no effect. I have fixed the unit tests by clearing the cache between saving and getting the data back out and the ordering works.

However, should I reallly be clearing the cache for the production code if a user creates a new ChapterVersion for example? Seems wrong to me, so I am sure I am approaching this relationship a little wrong.

Hopefully, I have described this issue appropriately to allow some feedback...

Cheers