FLuent nHibernate Linq Query

Question

I m facing issue to access the bridge table to get the values using nHibernate and LINQ. I have 4 table, ROLES , MODULES , PERMISSIONS and the RoleModulePermission(bridge).

Roles contains ROLEID(pk) , ROLENAME
Modules contains MODULEID(pk) , MODULENAME
Permission contains PERMISSIONID , PERMISSIONTYPE
RoleModulePermission contains ROLEID , MODULEID and PERMISSIONID

I want to get the Modules n Permission applied to the ROLES on the basis of ROLEID.

Role Mapping

  Table("tblRoles");
        Id(role => role.RoleID).GeneratedBy.Identity();
        Map(role => role.RoleName).Not.Nullable();
        Map(role => role.IsActive).Not.Nullable();
        Map(role => role.Description).Not.Nullable();

        HasManyToMany(x => x.Users)
            .Table("tblUserInRoles")
            .ParentKeyColumn("RoleID")
            .ChildKeyColumn("UserID")
            .Not.LazyLoad();

        HasManyToMany(x => x.Modules)
            .Table("tblRolesPermission")
            .ParentKeyColumn("RoleID")
            .ChildKeyColumn("ModuleID")
            .Not.LazyLoad();

        HasManyToMany(x => x.Permissions)
            .Table("tblRolesPermission")
            .ParentKeyColumn("RoleID")
            .ChildKeyColumn("PermissionID")
            .Not.LazyLoad();

Module Mapping

 Table("tblAppModules");
        Id(mod => mod.ModuleID).GeneratedBy.Identity();
        Map(mod => mod.ModuleName).Nullable();
        Map(mod => mod.CreationDate).Nullable();

        HasManyToMany(x => x.Roles)
            .Table("tblRolesPermission")
            .ParentKeyColumn("ModuleID")
            .ChildKeyColumn("RoleID")
            .Not.LazyLoad();

Permission Mapping

Table("tblPermission");
        Id(p => p.PermissionID).GeneratedBy.Identity();
        Map(p => p.PermissionType).Not.Nullable();

        HasManyToMany(p => p.PermitRole)
            .Table("tblRolesPermission")
            .ParentKeyColumn("PermissionID")
            .ChildKeyColumn("RoleID")
            .Not.LazyLoad();

It seems that i did wrong in mapping ?

please do not assume 'AllowAccess' in tblRolesPermission How to achieve this ?

Thanks

Answer 1

Mapping for ternary associations in FluentNHibernate depends from specifics of your scenario and domain model.

Basically, if you are going to have the many-to-many relations everywhere (i.e. your Module can have multiple Permissions, Role can have multiple Permissions and Role can have multiple Modules), you'll need to have a separate entity for the relation, called i.e. RoleModulePermission. It will change your graph from triangle-like (3 classes) into a star-like (4 classes with common "root"). The new entity will have three many-to-one relations and Role, Module and Permission will have one-to-many relation to RoleModulePermission. Set up the cascades and you can try querying this model like any other:

session.Query<Permission>()
    .Where(x => x.RoleModules.Any(rm => rm.Module == module));

If your model is more restricted, i.e. you have some constraints, you can try to map it simpler using AsTernaryAssociation or AsEntityMap, which is pretty much the same. Then in your Role, Module and Permission classes you should have relation of type IDictionary<TFrom, To>. This can mean i.e. that Permission X maps Role Y to Module Z, but it means that within Permission X Role Y have no other Modules. Something like this in POCO class:

 public virtual IDictionary<Role, Module> RoleModules { get; set; }

and like this in mapping:

HasManyToMany(x => x.RoleModules).Table("tblRolesPermissions").AsEntityMap();

does support it, but. You have to specify one-to-many or many-to-many relations at each side of the relation (in POCO's for Roles, Modules and Permission) and map each relation like that:

m.HasMany(x => x.RolesPermissionsModules).AsTernaryAssociation()

Querying using LINQ should then look somehow like this:

session.Query<Permission>()
    .Where(x => x.RoleModules[role] == module);