NHibernate - Mapping a String Foreign Key

Question

The legacy database I've inherited contains the following tables:

Teams ( 
 TeamId INT PRIMARY KEY,
 Name VARCHAR(30)
)

Players (
 PlayerId INT PRIMARY KEY,
 Team VARCHAR(30)
)

The foreign key in the players table refers to the team name, rather than teamId.

I've attempted to map from Team to Players using a bag:

<bag name="Players">
    <key column="Team" foreign-key="Name" />
    <one-to-many class="DataTransfer.Player, DataTransfer" />
</bag>

But I get SqlException: Conversion failed when converting the varchar value 'Arsenal' to data type int

I've been able to use a bag to map string foreign keys in other areas, but in those cases the foreign key referred to the primary key of the parent table.

Edit: I'm using NHibernate 2.0.1

Hi, We are having the same problem, have you found any solution to this? — fredrik
I ended up just creating a new column of type int for the foreign key and using that instead, never managed to get a string foreign key working. — Mr Plough

Frederik Gheysels Frederik Gheysels · Accepted Answer · 2009-01-20T19:11:01

I think the property-ref attribute exists to solve this problem.

<bag name="Players">
   <key column="Team" property-ref="Team" />
   <one-to-many class="Player" property-ref="Team" />
</bag>

NHibernate - Mapping a String Foreign Key

4 Answers