Nhibernate foreign key to foreign key collection mapping ?

Question

I have a Staff and e SecuredPage entity and the properties are below

Staff

id Name LastName Level // SecuredPage.RoleId

SecuredPage

id PageId RoleId // Staff.Level

I want to have a collection of SecuredPage in Staff entity, so it s a one-to-many but i couldnt figure it out how to handle with it in mapping.

Staff.hbm.xml

<bag name="SecuredPages"  lazy="true" inverse="false" cascade="none">
      <key column="RoleId" />
      <many-to-many class="RealEstate.Core.Domain.SecuredPage,RealEstate.Core" />
</bag>

with the code above Nhibernate gets all the records from SecuredPage table where

SecuredPage.RoleId = Staff.id

But i want it to be

SecurePage.RoleId = Staff.StaffLevel

I didnt create a Role Entity for some reasons that's why i am directly trying to get secured pages for Staff based on its Level(Role)

I hope i could tell you about my problem. I have checked Nhibernate guide, read all about collections mappings but i couldnt figure it out.

Thanks

Answer 1

I think the problem is that it's not really a one to many. In reality, that is an expression of a many-to-many relationship.

I know this doesn't sound like what you wanted to hear (as you said you didn't create a Role entity) but you may want to consider doing so for your own sake.

Is there a "Role" table that would act as the cross-reference between these two tables you presented?

If not, you should make one and that will help to resolve the problem. If so, you may want to consider the relationship between employees and roles as being direct, and employees and SecuredPages as being indirect (through roles, which it is).

EDIT: Can you tell us why you didn't create a "Role" entity? That may help to clarify for us.

Answer 2

Thanks for your reply,

There is no Role table because i just have 3 roles (Admin,Secretary,Advisor). I defined them as Enums.

I know that, this isnt the way for roles, staff and securedpages. I just want to have a custom Collection thing inside my Staff Entity.

or

I could get that collection manually on Staff.Login() method ?