Fluent NHibernate many-to-many create clustered index on link table

Question

I have two entities in a many-to-many relationship.

 public class SecurityGroupMappingOverride : IAutoMappingOverride<SecurityGroup>
{
    public void Override(AutoMapping<SecurityGroup> mapping)
    {
        mapping.HasManyToMany(x => x.Actions).ParentKeyColumn("securityGroupId").ChildKeyColumn("actionId").
            LazyLoad().Table("ActionGroups");

        mapping.HasManyToMany(x => x.Members).ParentKeyColumn("securityGroupId").ChildKeyColumn("userId").
            LazyLoad().Inverse().Table("UserGroups");

        mapping.Map(x => x.Name).Length(64);

    }
}

So I want to create an clustered index on both columns (userId, securityGroupId) from table UserGroups.

Or just to create a Primary Key on UserGroups on them both columns, as at same time could not be two same links.

Thanks

Answer 1

I assume you're wanting NHibernate's SchemaExport tool to generate these indexes/keys for you.

For many-to-many bags (FluentNHibernate's default collection type), SchemaExport generates:

create UserGroups (
    securityGroupId INT not null,
    userId INT not null,
)

For many-to-many sets, it generates:

create UserGroups (
    securityGroupId INT not null,
    userId INT not null,
    primary key (securityGroupId, userId)
)

... so just add .AsSet() to your mapping.

mapping.HasManyToMany(x => x.Members)
    .AsSet()
    // ... etc.

It makes sense, really, if you think about what bags and sets are. The elements of a set are supposed to be unique, whereas bags don't have a uniqueness requirement.