Fluent NHibernate mapping to return description from lookup table

Question

We have the following database structure:

UserTeam table
Id (PK, int not null)
UserId (FK, int, not null)
TeamId (FK, int, not null)
RoleId (FK, int, not null)

libRole table
Id (PK, int not null)
Description (varchar(255), not null)

And we have an entity as follows:

public class UserTeam
{
    public int Id { get; set; }
    public Team Team { get; set; }
    public User User { get; set; }
    public int RoleId { get; set; }
    public string Role { get; set; }
}

We are using Fluent NHibernate and configuring NHibernate automatically (ie, using Automapping classes with overrides).

We are trying to get JUST the description column from the libRole table into the "Role" property on the UserTeam table, but really struggling. The following is the closest we have got:

public class UserTeamMap : IAutoMappingOverride<UserTeam>
{
    public void Override( FluentNHibernate.Automapping.AutoMapping<UserTeam> mapping )
    {
        mapping.References( m => m.User ).Column( "UserId" );
        mapping.References( m => m.Team ).Column( "TeamId" );

        mapping.Join("Role", join =>
            {
                join.Fetch.Join();
                join.KeyColumn( "Id" );
                join.Map( x => x.Role, "Description" );
            } );
    }

}

Which generates the following SQL:

SELECT
    TOP (@p0)  this_.Id as Id70_0_,
    this_.RoleId as RoleId70_0_,
    this_.TeamId as TeamId70_0_,
    this_.UserId as UserId70_0_,
    this_1_.Description as Descript2_71_0_ 
FROM
    [UserTeam] this_ 
inner join
    libRole this_1_ 
        on this_.Id=this_1_.Id;

Close, but NHibernate is using the Id column on both the UserTeam table and the libRole table in the join, when it should be doing on this_.RoleId=this_1_.Id

What are we missing? We don't really want to create a "libRole" entity within the application, as all we really care about is the description values - which are user configurable, so we can't just use an enum either. Can anyone help?

Answer 1

Join uses the primary key of the parent table. Its not possible to change this to a foreign key. See the docs for further details on what is possible with Join.

In this situation I would recommend creating an entity for the lookup. But if you really want to take this approach you could map the property with a formula, i.e.

Map(x => x.Role).Formula("(select description from libRole where Id = RoleId)");

Note this isn't perfect because it uses RoleId so if the query has another table with a column named RoleId then the DBMS will complain when trying to execute the SQL.