How to map this class in NHibernate (not FluentNHibernate)?

Question

Suppose I have a database like this:

This is set up to give role-wise menu permissions.

Please note that, User-table has no direct relationship with Permission-table.

Then how should I map this class against the database-tables?

class User
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
        public bool? IsActive { get; set; }

        public IList<Role> RoleItems { get; set; }
        public IList<Permission> PermissionItems { get; set; }
        public IList<string> MenuItemKeys { get; set; }
    }

This means,

(1) Every user has some Roles.

(2) Every user has some Permissions (depending on to Roles).

(3) Every user has some permitted MenuItemKeys (according to Permissions).

How should my User.hbm.xml look like?

Answer 1

Here's a link: Chapter 6. Collection mapping Here's another useful link: Chapter 7. Association Mappings

EDIT

After having reasearched for an entire evening, I came to the following conclusion:

1. Considering NHibernate Best Practices, what you wish to do is no good;

Don't use exotic association mappings.
Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

1. As a programming philosophy, I prefer to keep it simple than having to write clever code where even me would no more understand what I wrote after a certain time;
2. Plus, I even considered using the subquery element of association mapping which would have worked if I would have found a way to parameterize it, if it is doable, but it seems it won't let me parameterize the query with the User instance's Id property value;
3. In the optic of a well designed OO model, a child being aware of his parent's properties is fine, but a parent accessing a child's property makes no sens - design smell;

4. As I may understand considering the context exposed the benefits of having permissions or MenuItemKey values accessible from the User directly, I suggest the following solution:

   • Create yourself a user defined dataview which will hold the values related to the MenuItemKey Permission attribute gotten through the Roles the User is a member like so:

CREATE VIEW udvUsersPermissions AS
SELECT UR.UserID, P.ID as N'ID', P.MenuItemKey
FROM Users U
INNER JOIN UsersRoles UR ON UR.UserID = U.ID
INNER JOIN Roles R ON R.ID = UR.RoleID
INNER JOIN Permissions P ON P.RoleID = R.ID
GO

Then, map it according in you User.hbm.xml file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="User" table="Users">
    <id name="Id" column="ID">
      <generator class="identity"/>
    </id>
    <property name="Name" length="100"/>
    <property name="UserName" length="10" not-null="true"/>
    <property name="Password" length="10" not-null="true"/>
    <property name="IsActive" not-null="true"/>

    <list name="Roles" table="UsersRoles" access="private-property" lazy="true">
      <key column="UserID" foreign-key="FK_UR_U"/>
      <list-index column="UserID"/>
      <many-to-many class="Role" column="RoleID" />
    </list>

    <!-- Here mapping Permissions granted to User. -->
    <list name="Permissions" table="udvUsersPermissions" lazy="true">
      <key column="UserID"/>
      <list-index column="MenuItemKey"/>
      <many-to-many column="ID" class="Permission"/>
    </list>

  </class>
</hibernate-mapping>

And here, I will let you know about the subselect solution, in case it works the way I didn't expect it to.

<list name="Permissions" lazy="true">
  <subselect> <!-- see section 7.6, Chapter 7 - Association mappings -->
    select U.ID, P.ID, P.MenuItemKey
    from Users U
    inner join UsersRoles UR ON UR.UserID = U.ID
    inner join Roles R ON R.ID = UR.RoleID
    inner join Permissions P ON P.RoleID = R.ID
    group by U.ID, P.ID, P.MenuItemKey
    order by P.MenuItemKey
  </subselect>
  <key column="U.ID"/>
  <list-index column="P.MenuItemKey"/>
  <many-to-many class="Permission" column="P.ID"/>
</list>

Now, I hope I brought enough details so that it helps you achieve what you want to do or either get on track. =)

Answer 2

Roles and Permissions are likely to be accessed a lot in the application. They are very likely to be in the second level cache, which means we can expect to efficiently iterate the User.RoleItems and Role.Permissions.

This has the advantage that we can generally expect to perform no queries when iterating those collections.

You could map the classes as follows.

The properties User.PermissionItems and User.MenuItemKeys are derived from the persistent entities, and thus do not appear in the mappings.

<class name="User" table="user">
  <id name="ID">
    <generator class="native"/>
  </id>
  <property name="Name"/> 
  <property name="Username"/>
  <property name="Password"/>
  <property name="IsActive"/>

  <bag name="RoleItems" table="userrole" lazy="true">
    <key column="userid" />
    <many-to-many class="Role" column="roleid"/>
  </bag>
</class>

<class name="Role" table="role">
  <id name="ID">
    <generator class="native"/>
  </id>
  <property name="RoleName"/>
  <property name="IsActive"/>

  <bag name="Permissions" table="permission">
    <key column="roleid" />
    <one-to-many class="Permission"/>
  </bag>
</class>

<class name="Permission" table="permission">
  <id name="ID">
    <generator class="native"/>
  </id>
  <property name="MenuItemKey"/>
</class>

I would make the 2 additional lists you had on User into derived enumerations. If they were lists, there is no unambiguous way to insert into them since you cannot know to which role the value applies. Also, a Role is not owned by a User.

Update: now using Diego's improved version of these properties.

class User
{
    public virtual IEnumerable<Permission> PermissionItems
    {
        get {
            return RoleItems.SelectMany(role => role.PermissionItems);
        }
    }

    public virtual IEnumerable<string> MenuItemKeys
    {
        get {
            return RoleItems.SelectMany(role => role.PermissionItems,
                        (role, permission) => permission.MenuItemKey);
        }
    }
}

Answer 3

The mapping posted by Lachlan is the best alternative. You could use queries that perform all the joins for each collection, but that'd make them read only for practical purposes.

There is a much easier way to implement the property code, however, that might help you decide:

public IEnumerable<Permission> PermissionItems
{
    get
    {
        return RoleItems.SelectMany(role => role.PermissionItems);
    }
}

public IEnumerable<string> MenuItemKeys
{
    get
    {
        return RoleItems.SelectMany(role => role.PermissionItems,
                                   (role, permission) => permission.MenuItemKey);
    }
}