SQL index on NHibernate list key column?

Question

I have two C# classes, say Container and Item, and a unidirectional association:

class Container {
   [...]
   public IList<Item> Items {get;set;}
}

The NHibernate mapping looks like this:

<class name="Container">
  [...]
  <list name="Items" cascade="all-delete-orphan">
    <key column="ContainerId"/>
    <index column="Position"/>
    <one-to-many class="Item"/>
  </list>
</class>

As a result, NHibernate (2.0.1) generates a ContainerId column in the Item table. Since I usually navigate this connection from the Container side, I want to place an SQL index on the ContainerId column. NHibernate doesn't seem to provide a mapping syntax for this, or at least it's not obvious to me. What is the best way to accelerate this?

I'd like to avoid having to spread this over multiple places, so I'd prefer changing the mapping document only. Is there a way to do this in NHibernate syntax? Should I embed custom SQL commands? If so, how?

Answer 1

Have a look at the <database-object> mapping; it will allow you to create arbitrary indexes, triggers, etc. as part of your schema creation process. Usage is described in this article.