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

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.

Up vote 3 down vote favorite share g+ share fb share tw.

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 RoleItems { get; set; } public IList PermissionItems { get; set; } public IList 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?

Nhibernate nhibernate-mapping link|improve this question edited Mar 8 '10 at 9:28 asked Mar 4 '10 at 20:00Saqib3,092339101 87% accept rate.

2 Why not just map user to roles and permissions to roles. Then use the roles to get the permissions? A simple linq query: var permissions = user.RoleItems.

SelectMany(x => x. PermissionItems); (or a method on your user class). – Mattias Jakobsson Mar 4 '10 at 20:34.

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. 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 PermissionItems { get { return RoleItems. SelectMany(role => role. PermissionItems); } } public virtual IEnumerable MenuItemKeys { get { return RoleItems.

SelectMany(role => role. PermissionItems, (role, permission) => permission. MenuItemKey); } } }.

This is what I have already done. I was just wondering, if it was possible to avoid EnumeratePermissionItems()-like codes. – Saqib Mar 8 '10 at 17:40.

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 PermissionItems { get { return RoleItems.

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

PermissionItems, (role, permission) => permission. MenuItemKey); } }.

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: 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. 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; 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; 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; 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: And here, I will let you know about the subselect solution, in case it works the way I didn't expect it to.

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 Now, I hope I brought enough details so that it helps you achieve what you want to do or either get on track. =).

I brought some corrections to the elements and added the 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 Mar 4 '10 at 20:41 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. – Saqib Mar 8 '10 at 8:12 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 Mar 8 '10 at 15:38 This answer is completely wrong, the Permission and Role classes don't have a foreign key to User to map them as such – Jaguar Mar 9 '10 at 15:03 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 Mar 9 '10 at 15:32.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.