How to change the column order in a multi-column index using fluent-nhibernate?

Question

How to change the column order in a multi-column index?
I.e:

mapping.References(x => x.SomeReference).SetAttribute("index", "IX_index");
mapping.Map(x => x.SomeField).SetAttribute("index", "IX_index");

Produces the following Schema:

create index IX_index on ApplicantProgramDatas (SomeField, SomeReferenceId)

But I want to get:

create index IX_index on ApplicantProgramDatas (SomeReferenceId, SomeField)

Answer 1

You can define an index in NHibernate using <database-object> or IAuxiliaryDatabaseObject.

In a hbm.xml file:

<hibernate-mapping xmlns="urn:nhiernate-mapping-2.2">
  <database-object>
     <create>VALID SQL</create>
     <drop>VALID SQL</create>
  </database-object>
</hibernate-mapping>

N.B. <database-object> can go before or after a class mapping in the same hbm.xml file allowing you to keep your index definitions, triggers, etc. with the object to which they apply.

The other option is NHibernate.Mapping.IAuxiliaryDatabaseObject:

namespace NHibernate.Mapping {
    public interface IAuxiliaryDatabaseObject : IRelationalModel {
        void AddDialectScope(string dialectName);
        bool AppliesToDialect(Dialect dialect);
        void SetParameterValues(IDictionary<string, string> parameters);
    }
    public interface IRelationalModel {
        string SqlCreateString(Dialect dialect, IMapping p, string defaultCatalog, string defaultSchema);
        string SqlDropString(Dialect dialect, string defaultCatalog, string defaultSchema);
    }
}

Given that you're using Fluent NHibernate, IAuxiliaryDatabaseObject will probably work better for you. Just expose your configuration when building it and then call:

var sqlCreate = "CREATION SCRIPT";
var sqlDrop = "DROP SCRIPT";    
cfg.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(sqlCreate, sqlDrop));

N.B. NHibernate.Mapping.SimpleAuxiliaryDatabaseObject is part of NHibernate. You don't have to write it yourself if all you need to do is supply create/drop scripts for a database object.

I took a quick look in the Fluent NHibernate codebase and didn't see any direct support for IAuxiliaryDatabaseObject. So it is a matter of exposing your configuration object and supplying all your IAuxiliaryDatabaseObjects yourself. It wouldn't be too difficult to write some code that scan through your mapping assembly looking for types that implement IAuxiliaryDatabaseObject and then foreach'ing over them to pass to cfg.AddAuxiliaryDatabaseObject(obj).

You can find more information about auxiliary database objects in the NHibernate docs:

http://nhibernate.info/doc/nh/en/index.html#mapping-database-object

Answer 2

I guess it is impossible. 'FluentNHibernate.MappingModel.MappedMembers.AcceptVisitor()' iterates properties before references:

        foreach (var collection in Collections)
            visitor.Visit(collection);

        foreach (var property in Properties)
            visitor.Visit(property);

        foreach (var reference in References)
            visitor.Visit(reference);

As a result, you will always have properties before references in multi-column index.

BTW none of the ORMs will give you ability to set non-trivial index options like clustered, filtered, etc.