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 later 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