Nhibernate: join tables and get single column from other table

Question

I have the following tables:

create table Users(
 Id uniqueidentifier primary key,
 InfoId uniqueidentifier not null unique,
 Password nvarchar(255) not null
)

Create table UserInfo(
 Id uniqueidentifier primary key,
 Company nvarchar(255) not null,
 ContactPerson nvarchar(255) not null
)

And InfoId is a foreign key referencing UserInfo(Id).

I want to map this to the following class:

public class UserCredentials
{
    public virtual Guid Id { get; set; }
    public virtual string UserName { get; set; }
    public virtual string PasswordHash { get; set; }

    protected UserCredentials() { }
}

I want to following mapping:

Id  --> Users(Id)
UserName --> UserInfo(Company)
PasswordHash --> Users(Password)

I tried the following mapping:

<hibernate-mapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:nhibernate-mapping-2.2">
    <class name="UserCredentials" table="Users">
        <id name="Id" type="Guid">
            <generator class="guid.comb" />
        </id>

    <property name="PasswordHash" not-null="true" column="Password"/>
    <join table="UserInfo">
      <key column="Id" foreign-key="InfoId"/>
      <property name="UserName" column="Company" not-null="true" />
    </join>
  </class>
</hibernate-mapping>

But it seems the <key> element is incorrectly specified (the foreign-key attribute) does not do what I want. If I leave out the foreign-key attribute, it tries to join on the Id columns of both tables, which is not correct.

I don't want to include an InfoId property on my UserCredentials class if it is possible to avoid it.

Can anyone help me achieve the desired mapping?

Answer 1

When you want to join in a realation or just another table like you do here based on not the primary key but on some other column you need to use the property-ref attribute.

like this:

<property name="InfoId" not-null="true" column="InfoId"/>
<property name="PasswordHash" not-null="true" column="Password"/>

<join table="UserInfo">
  <key column="Id" property-ref="InfoId"/>
  <property name="UserName" column="Company" not-null="true" />
</join>

This means that you need to also add InfoId to your UserCredentials class, there is currently no way to mapp a relation or a join like above by using a column name you have to speficy a property (NH will use the column that property refers to)

On a side note, the foreign-key attribute is a little missleading, it only specifies the name of the database foreign key that will be generated if you let nhibernate create the database schema.