Fluent NHibernate: How to create a clustered index on a table with many for many?

Question

To use Fluent NHibernate mappings on SQL Azure, I need to have a clustered index for each table. The default heap tables that Fluent NHibernate creates for many-to-many connections obviously do not, because they do not have primary keys.

I want to say one side of the relationship for creating a clustered index for my join table, but I'm not sure how to do it. Here's what my comparisons 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 more information!

Answer 1

, Fluent ( , xml),

<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

, ( Fluent NHibernate Sql Azure), . , . , HBM configuration.AddXmlFile("AddClusteredIndexesToManyToManyTables.hbm.xml");, HBM, , .

, ( !) hbm xml. :

№ 1: , SQL Server.

, ( , ), (Fluent) NHibernate configuration.ClassMappings configuration.CollectionMappings. , " ".

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;
}

, , , , (^ _-). , / .

!

Answer 3

... , , PK .

, , . , . " " SQL Server, b- , . ? , , - .

, fluent-nhibernate - "" - .

YMMV.

Answer 4

An excellent decision by M. Mimpen.

When map interfaces are needed, put a ChildKeyColumn with the interface name.

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

The Acesso class implements the IRole interface. If you do not provide the name of the child key, the created column will be "Acesso_id", but when creating the key, try "IRole_id".