How would I join a table on a non primary key fiel

Question

I have an employee table:

Employee
{
    Name
    EmployeeId -pk
    PositionId -fk
}

The positionId maps to the position table:

Position
{
    PositionId -pk
    ReportsToId
    PositionName
    PositionDescription
}

The ReportsToId field is a position Id for that positions manager.

I would like to select out an employee, their position and their managers details.

How would this be done using NHibernate's Mapping.ByCode.

The ReportsToId field is not a key field. From what I've read online, this seems to affect the mapping...

Answer 1

The mapping in this case would by 5.1.10. many-to-one with a feature called property-ref:

<many-to-one
    ...
    property-ref="PropertyNameFromAssociatedClass"     (7)

(7) property-ref: (optional) The name of a property of the associated class that is joined to this foreign key. If not specified, the primary key of the associated class is used.

So, the Position class should have ID and property ReportsToId

public virtual int ID          { get; set; }
public virtual int ReportsToId { get; set; }

The Employee C# class would have this property:

public virtual Position ManagerPosition { get; set; }

And the mapping of the Employee's property ManagerPosition, would be (see: Adam Bar, Mapping-by-Code - ManyToOne)

ManyToOne(x => x.ManagerPosition , m =>
{ 
    ...             
    m.Column("PositionId") // column in the Employee table
    m.PropertyRef(propertyReferencedName) // the Property/column in the Position table