Fluent NHibernate: How to create a clustered index on a Many-to-Many Join Table?

Question

In order to use my Fluent NHibernate mappings on SQL Azure, I need to have a clustered index on every table. The default heap tables that Fluent NHibernate creates for many-to-many joins obviously don't do this as they don't have primary keys.

I want to be able to tell one side of the relationship to create a clustered index for its join table, but I'm not sure how. Here's what my mappings look like:

 public class UserMap : ClassMap<User>{
    public UserMap()
    {
        Table("Users");
        Id(x => x.UserId).GeneratedBy.Identity().Column("UserId");
        Map(x => x.UserName).Unique().Not.Nullable().Length(DataConstants.UserNameLength).Column("UserName");
        Map(x => x.Email).Unique().Not.Nullable().Length(DataConstants.EmailAddressLength).Column("Email");
        Map(x => x.Password).Not.Nullable().Length(DataConstants.PasswordHashLength).Column("Password");
        HasMany(x => x.Clicks).Cascade.AllDeleteOrphan();
        HasManyToMany(x => x.Roles).Cascade.SaveUpdate().Table("UsersInRole").ParentKeyColumn("UserId").
            ChildKeyColumn("RoleId");

    }
}

Please let me know if you need any more information!

Answer 1

I don't know if Fluent supports it directly (if not, just include the xml), but you can do it with Auxiliary Database Objects

<nhibernate-mapping>
  <database-object>
    <create>create clustered index ix on UsersInRole(UserId, RoleId)</create>
    <drop>drop index UsersInRole.ix</drop>
  </database-object>
</nhibernate-mapping>

Answer 2

I struggled with the same problem as the topic starter (as I'm combining Fluent NHibernate and Sql Azure as well) but the given answer didn't satify. This is because it is not dynamic by convention. Of course the HBM file could be dynamically created and added to the configuration afterwards with configuration.AddXmlFile("AddClusteredIndexesToManyToManyTables.hbm.xml"); but I just don't like the HBM files and I can't imagine there is a better way.

After several hours I found another solution which is dynamic (and readable!) and does not deal with hbm xml files. The solution is as follows:

Assumption #1: I will create a composite primary key for each junction table that results in a clustered index in SQL Server.

After the configuration has been build (thus the mappings are parsed), (Fluent) NHibernate gives us the oppertunity to look into the actual mappings with configuration.ClassMappings and configuration.CollectionMappings. The latter is used because we are interested in the many-to-many mappings.

foreach (var collectionMapping in configuration.CollectionMappings
    // Filter on many-to-many
    .Where(x => !x.IsOneToMany)) {
    // Build the columns (in a hacky way...)
    const string columnFormat = "{0}_id";
    var leftColumn = new Column(string.Format(
        columnFormat,
        collectionMapping.Owner.MappedClass.Name));
    var rightColumn = new Column(string.Format(
        columnFormat,
        collectionMapping.GenericArguments[0].Name));
    // Fetch the actual table of the many-to-many collection
    var manyToManyTable = collectionMapping.CollectionTable;
    // Shorten the name just like NHibernate does
    var shortTableName = (manyToManyTable.Name.Length <= 8)
                                ? manyToManyTable.Name
                                : manyToManyTable.Name.Substring(0, 8);
    // Create the primary key and add the columns
    var primaryKey = new PrimaryKey {
        Name = string.Format("PK_{0}", shortTableName),
    };
    primaryKey.AddColumn(leftColumn);
    primaryKey.AddColumn(rightColumn);
    // Set the primary key to the junction table
    manyToManyTable.PrimaryKey = primaryKey;
}

And yes, the logic to get the left and right hand columnsAfter that the columns are a bit hacky but it works and you are free to adjust and edit my solution (^_-). The problem is that the collection mapping is fairly empty/unfilled.

Good luck with coding and creating conventions!

Answer 3

Any set of column(s) can be a clustered index... there is no requirement that I know of which forces you to use a PK constraint in order to build a clustered index.

More over I do not understand how a client could REQUIRE a clustered index. It might make them as a default but that's different than require. This is often reported as a "best practice" for SQL Server, but to the client, there's no real distinction between a secondary b-tree index on a column and the clustered index which orders the table's record. How would the client be able to distinguish the underlying storage of the data? One stores the data ordered by the cluster key, the other doesn't.

Maybe fluent-nhibernate performs better, or claims to - but it will "work" without any indexes.

But I'm not an expert in either so YMMV.

Answer 4

Great solution M.Mimpen.

When need map interfaces, put the ChildKeyColumn with interface name.

Ex: HasManyToMany(x => x.Acessos).("IRole_id");

The class Acesso implements IRole interface. If you don´t inform child key name, the column created will be "Acesso_id", but when create the key will try "IRole_id".