Fluent NHibernate mapping foreign key

Question

I have a db that I'm trying to model using Fluent NHibernate.

the tables in questions are:

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

AccessType: PK Id, Name

Authorization: PK Id, Name

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

User Entity:

    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; }

Permission Entity:

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

    public virtual AccessTypes AccessType { get; set; }

    public virtual Authorization Authorization { get; set; }
}

User Map:

    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 same number of columns as the referenced primary key (USERS [ID])

I need to get a list of permission by checking the user accessType and user authorization. My question is: How can I map the Permission list in User mapping? Should I Use the ternary association?

Does anyone have any insight on how to do this?

Florian Lim Florian Lim · Accepted Answer · 2011-03-25T21:40:48

This scenario is not supported. NHibernate has a feature called property-ref which can be used (but should be avoided) on old databases that were designed poorly. However, property-ref only supports referencing one non-primary-key column. Since you are trying to reference two such columns, this will not work.

However, since the permissions are obviously not tied to the user per se, you should not even map them.

You could still have the property for the list in the Users class and fill that with an extra method that simply reads the Permissions using a Where-condition on both columns. Still, I would advise against this. I would write a method like this (code 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();
}

Fluent NHibernate mapping foreign key

1 Answers