Fluent NHibernate many-to-many clustered index in link table

Question

I have two objects in many ways.

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 a clustered index for both columns (userId, securityGroupId) from the UserGroups table.

Or simply create a primary key in UserGroups on both of them columns, since at the same time there cannot be two identical links.

thanks

Answer 1

I assume you need the NHibernate SchemaExport to create these indexes / keys for you.

For many-to-many packages (the default collection type is FluentNHibernate), 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 actually makes sense if you are thinking about what bags and sets are. The elements of the set must be unique, and the bags do not have a uniqueness requirement.

Answer 2

You need IAutoMappingOverride<Member> instead at the top of the code. And you only need one mapping.HasManyToMany . If you need bidirectional communication, you need to do the opposite mapping by creating the MemberMappingOverride class and there you will notice the opposite.

Hope this helps.