Free NHibernate mapping for foreign key

Question

I have a db that I am trying to simulate using Fluent NHibernate.

tables in questions:

User: PK Id, Name, FK accessType, FK authorization

AccessType: PK ID, Name

Authorization: PK ID, Name

Resolution: PK Id, FK menuId, FK accessId, FK authId

User:

public Users() { Permissions = new List<Permissions>(); } public virtual AccessTypes AccessType { get; set; } public virtual Authorization Authorization { get; set; } public virtual string Name { get; set; } public virtual IList<Permissions> Permissions { get; set; } 

Permissions Object:

 public class Permissions : EntityWithTypedId<long> { public virtual Menus Menu { get; set; } public virtual AccessTypes AccessType { get; set; } public virtual Authorization Authorization { get; set; } } 

User Card:

  public UsersMap() { Table("USERS"); Map(x => x.Name, "NAME"); References<AccessTypes>(x => x.AccessType, "ACCESS_TYPE_ID"); References<Authorization>(x => x.Authorization, "AUTHORIZATION_ID"); Id(x => x.Id, "ID") .Column("ID") .GeneratedBy.Assigned(); HasMany<Permissions>(x => x.Permissions) .KeyColumns.Add("ACCESS_TYPE_ID", "AUTHORIZATION_ID") .Inverse() .Cascade.None(); } 

Permission Map:

  public PermissionsMap() { ReadOnly(); Table("PERMISSIONS"); References<Menus>(x => x.Menu, "MENU_ID"); References<AccessTypes>(x => x.AccessType, "ACCESS_TYPE_ID"); References<Authorization>(x => x.Authorization, "AUTHORIZATION_ID"); Id(x => x.Id, "ID") .Column("ID") .GeneratedBy.Assigned(); } 

I got this error: Foreign key (FK79B2A3E83BA4D9E3: PERMISSIONS [ACCESS_TYPE_ID, AUTHORIZATION_ID])) must have the same number of columns as the referenced primary key (USERS [ID])

I need to get a list of permissions by checking the user access type and user authorization. My question is: how can I map the list of permissions in user mapping? Should I use triple association?

Does anyone have an idea on how to do this?

Answer 1

This script is not supported. NHibernate has a function called property-ref that can be used (but should be avoided) in older databases that were poorly designed. However, the ref property only supports a reference to a single column of a non-primary key. Since you are trying to reference two such columns, this will not work.

However, since permissions are clearly not tied to the user as such, you do not even have to match them.

You can still have the property for the list in the Users class and fill it with an additional method that simply reads the permissions using the Where clause in both columns. However, I would advise doing this. I would write such a method (the code was not tested):

 public IList<Permissions> GetPermissionsForUser(Users user) { return session.QueryOver<Permissions>() .Where(p => p.Authorization.Equals(user.Authorization)) .And(p => p.AccessType.Equals(user.AccessType)).List(); }