Mapping a foreign key to something other than a primary key

Question

In my database there are tables Assignment and Workplace, conceptually, every assignment takes place at exactly one workplace. The Assignment table contains a column Workplace, which is a foreign key referencing the Workplace table's WorkplaceName column.

This is the schema:

table Workplace (
  ID int primary key,
  WorkplaceName int
)

table Assignment (
  Workplace int
)

And my mapping:

class Workplace
{
  public virtual int ID { get; set; }
  public virtual int WorkplaceName { get; set; }
} 

class Assignment 
{
  public virtual Workplace { get; set; }
}

class AssignmentMap : ClassMap<Assignment>
{
  public AssignmentMap()
  {
    References(a => a.Workplace);
  }
}

At runtime I get the exception

No row with the given identifier exists[MyProject.Workplace#2001]

The issue here seems to be that FH looks for the value of the Assignment.Workplace property in the Workplace.ID column, the table's primary key; the correct place would be the Workplace.WorkplaceName column. I have tried to use the ForeignName method, thinking I could specify WorkplaceName this way. How can I tell FH how to join the correct columns?

Answer 1

I've found the answer here: FluentNHibernate Many-To-One References where Foreign Key is not to Primary Key and column names are different

The solution was to use PropertyRef; the line reads now:

References(x => x.Workplace).PropertyRef(x => x.WorkplaceName).Fetch.Join();

What I didn't realize was that I needed to give a reference to the client-side object's property WorkplaceName, instead of trying to instruct the mapping to use the server-side column WorkplaceName.