views:

161

answers:

3

Suppose I have a database like this:

alt text

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?

+1  A: 

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. =)

Will Marcouiller
I brought some corrections to the <list> elements and added the <one-to-many> elements either. I got it right from the top of my head, so thanks for your undestanding if it's not working on the first try. You have the fundamentals anyway. Thanks for accepting my answer and the upvote!
Will Marcouiller
This is not working. Where do you get column 'IdUser' and the foreign key 'FK_U_MIK' and 'FK_U_PI'? I have already stated that, User and Permission have no relationships.
JMSA
IdUser is my mistake, this should have been UserId as stated in your datatable. Thus, as for the relation between User and Permission, you say they have no relationship, however, there's a list of Permission within the User object. Perhaps is it my understanding that tricks me, otherwise this says there are many permission per user, that said, a user has permissions. Let me some time, I will get back to you. Thanks!
Will Marcouiller
This answer is completely wrong, the Permission and Role classes don't have a foreign key to User to map them as such
Jaguar
Now, this way I can't be wrong. I mentioned to JMSA that I will get back to him with a proper solution in some time. I'm currently busy writing an Active Directory library for graduating access models. Sorry.
Will Marcouiller
+3  A: 

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);
        }
    }
}
Lachlan Roche
This is what I have already done. I was just wondering, if it was possible to avoid EnumeratePermissionItems()-like codes.
JMSA
+1  A: 

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);
    }
}
Diego Mijelshon