NHibernate Mapping: Simple Join on a Foreign Key

Question

The Tables

Currencies
----------
CurrencyId INT IDENTITY PK
IsoCode4217 CHAR(3) -- "USD", "GBP", "EUR", etc

Users
----------
UserId INT IDENTITY PK
CurrencyId FK REFERENCES Currencies (CurrencyId)

The Mapping

The current application has a Currency object that needs the IsoCode4217 value. A Currency is not an entity in this application, it's its own weird hard-coded thing where I need to pass the IsoCode4217 value to a static function and get a Currency instance back. The database Currencies table is more of an enumeration table. So I figured I'd just implement an IUserType and off I'll go, but this does not work as I expect:

[hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"]
  [class
    name="BlahBlah.UserProfile, BlahBlah.Model"
    schema="Core"
    table="Users"
    lazy="false"]
    [id name="Identity" access="field.camelcase" column="UserId"]
      [generator class="native" /]
    [/id]
    [join
      schema="Core"
      table="Currencies" 
      fetch="join"]
      [key column="CurrencyId" property-ref="Currency" /]
      [property
        name="Currency"
        column="IsoCode4217" 
        type="BlahBlah.CurrencyUserType, BlahBlah.Model" /]
    [/join]
  [/class]
[/hibernate-mapping]

(I changed the angle brackets to regular brackets as I don't feel like thinking about what this little Markdown editor is going to do.)

The Ugly

The SQL that I would expect is

SELECT
  u.UserId,
  c.IsoCode4217
FROM Users AS u
INNER JOIN Currencies AS c
   ON u.CurrencyId = c.CurrencyId

but instead NHibernate stubbornly gives me

SELECT
  u.UserId,
  c.IsoCode4217
FROM Users AS u
INNER JOIN Currencies AS c
  ON u.UserId = c.CurrencyId -- DANG IT!

Is this really not supported, or am I overlooking something obvious? Is it possible to just "loop in" an extra column from another table for the purpose of a single entity's mapping?

Thanks!

Answer 1

Join mappings are a PITA! The problem here is that Hibernate expects a join mapping to map from a Parent table to a Child table, whereas you're joining from the child table to the parent table.

Essentially, join mappings were designed to make it easy to join on tables that have a one-to-one relationship and where the parent table is joining to a child table.

I think in your situation you want to have a many-to-one mapping rather than a join