managing classes when everything is relative to a user in nhibernate (orm)

Firstly I have three entities.

Users, Roles, Items

A user can have multiple Roles. An item gets assigned to one or more roles.

Therefore a user will have access to a distinct set of items.

Now there is a few ways I can see this working.

1. There is a Collection on Users which has Roles via a many-to-many assoc. Then each Role in this collection will have its own collection of Items. So for each user I would have to get the User (using nhib and fetch the roles and items with it) then either do a selectMany on the Items in each Role to get all the Items for the user or do a couple of foreach's to port the data to a view or dto model.

2. Create a db trigger to automatically insert into another table that just has the relationship between user and items so that on my User entity I only have a Items collections which has all the items assigned to me.

3. Some other way that i can't think of yet, because I'm new to nHibernate.

Now i know that the trigger doesn't feel right but I'm not sure how to do this. We also have some hierarchy lat开发者_JAVA技巧er where a user may be in charge of a group of users. If anyone could shed some light on how they go about these scenarios in nhibernate or another orm that would be great, or point be in a direction.

I know that in the past you would have to enter all combinations into a table so that the query worked, but when you know sql its not too bad.

If you need any other info then let me know. Cheers

The logical mapping is User is assigned to many Roles ( the inverse being Role has many Users if you really need to track that ) and Item is visible to many Roles ( again the inverse should only be created if you really need to track that ).

Ayende has solved this problem with Rhino Security. Have a look at http://ayende.com/Blog/category/548.aspx for all his blog posts on it. Essentially he is using a NHibernate interceptor to extend any queries with security information which means once you set up the permissions it "just works" and you don't have to worry about adding security concerns to your queries.

If you don't want to use this to retrieve / display all the items for a given user then you will want to select all items that are visible to a any role that the user is a member of:

1. Find all the roles the current user has

2. Find all the items that contain the list of roles for the user

   var roleIds = DetachedCriteria.For .Add( Restrictions.IdEq( currentUser.Id ) ) .CreateCriteria( "Roles" ) .SetProjection( Projections.Id );

   var items = session.CreateCriteria() .CreateCriteria( "VisibleTo" ) .Add( Subqueries.PropertyIn( "Id", roleIds) ) .List();

Written off the top of my head so may require some further tuning.

Have a look at NhibernateProvider on codeplex. It is an implementation of the ASP.Net Membership. So it has roles, users, applications. That may give you some ideas on how to proceed.