Nhibernate list mapping with not continuous index column

Question

I am developping a ASP.NET with VB using NHIBERNATE to map the tables of a pre-existing database (SQL Server 2005). I have a many-to-many relationship between to entities, that I map like this:

<list name="PropName" table="TableHoldingRelation" lazy="false" >
   <key column="idEntity1"></key>
   <index column ="orderingColumn" ></index>
   <many-to-many class="Entity2" column="idEntity2"></many-to-many>
</list>

The mapping works perfectly and the list(of Entity2) its ordered by the selected column.

The problem is that this column is not continuous, as there might be some values missing (ie: 0,1,3,8). NHibernate is leaving those spaces as null/nothing elements. I would want to have the list "compacted", only containing existing elements, ordered by that column.

Can I achieve this without having to update the database? (updating is not a good solution as it probably will happen in the future that some elements get removed)

Thanks in advance for your help.

EDIT: A bit more info in the problem. The tables/entities in this case refer to Menus and MenuItems. The application that I am working is is a very complex website, with lots of diferent roles. Each role has his unique menus congfiguration, with their unique items. There are even single users with unique settings. My task is to rewrite the .NET clases and mappings, as they are really messy and other things not relevant for this question. So the database design I am mapping is (for this question, obviously there are other tables): One table holding menus and their attributes(like wich role do they correspond to) One table holding menuitems and their attributes (like a link they point to) One table holding de relation menu-menuitem and a "position"/order column inside that menu.

Just in case more insight on the problem was needed.

Answer 1

As you can read here NHibernate Mapping - <list/> - by Ayende, this behaviour is by design. An extract from comments (close to your question):

...Because in general, having NH doing something like that for you can be bad. There is a meaning to null values.

But broadly, it is because it is not the responsibility of NHibernate to do so. If you want something like that, you don't need a list, you need an ordered set...

With this we can try to change your mapping (see the order-by attribute):

<bag name="PropName" table="TableHoldingRelation" 
     order-by="orderingColumn" 
     lazy="false" >
    <key column="idEntity1"></key>
    <many-to-many class="Entity2" column="idEntity2"></many-to-many>
</bag>

But, this mapping won't allow you to insert into that column orderingColumn.

This documentation 24. Best Practices says:

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

So, maybe introduce the man-in-the middle pairing object, put the management of the OrderBy property there, and use the sorted list..