Greetings, all!

I'm working on a legacy database in which an individual has many accounts, and an account belongs to a single individual. There are three tables: Individual, IndividualAccount, and Account:

Code:

+------------+      +--------------------+                 
| Individual |      | IndividualAccount  |      +----------+
+------------+      +--------------------+      | Account  |
| Id (PK)    | <----| IndividualId (FK)  |      +----------+
|            |      | AccountId (FK)     |----> | Id (PK)  |
+------------+      +--------------------+      | Type     |
                                                +----------+

I'd like to have a Individual class and an Account class that look like this:

Code:

public class Individual
{
    public virtual int Id { get; set; }
    public virtual IList<Account> { get; set; }
}

public class Account
{
    public virtual int Id { get; set; }
    public virtual Individual Individual { get; set; }
    public virtual string Type { get; set; }
}

I can't figure out a way to get the mapping to do what I want. Does NHibernate support this?

The best I've been able to do is use a many-to-many relationship in the Individual mapping and a <join> combined with an many-to-one relationship in the Account mapping, like this:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Individual" table="Individual">
    <id name="Id">
      <generator class="identity" />
    </id>
    <bag name="Accounts" cascade="all" table="IndividualAccount">
      <key column="Individual_id" />
      <many-to-many column="Account_id" class="Account" />
    </bag>
  </class>

  <class name="Account" table="Account">
    <id name="Id">
      <generator class="identity" />
    </id>
    <property name="Type" />
    <join table="IndividualAccount" inverse="true">
      <key column="Account_id" />
      <many-to-one name="Individual" column="Individual_id" />
    </join>
  </class>
</hibernate-mapping>

This seems to work, but I'm getting an extra "left outer join" to IndividualAccount when I execute the following HQL, which suggests to me that I'm not doing something right:

Code:

select
  distinct i
from
  Individual as i
  inner join i.Accounts as a
where
  a.Type = 'Checking'

--->

select distinct
    individual0_.Id as Id3_,
    individual0_.FirstName as FirstName3_,
    individual0_.LastName as LastName3_,
    individual0_.DOB as DOB3_,
    individual0_.Addr1 as Addr5_3_,
    individual0_.Addr2 as Addr6_3_,
    individual0_.City as City3_,
    individual0_.State as State3_,
    individual0_.Zip as Zip3_
from
    [Individual] individual0_
    inner join IndividualAccount accounts1_ on individual0_.Id=accounts1_.Individual_id
    inner join Account account2_ on accounts1_.Account_id=account2_.Id
    left outer join IndividualAccount account2_1_ on account2_.Id=account2_1_.Account_id
where
    (account2_.Type='Checking' )

What's the right way to be doing this? Thanks in advance.

- chad

You probably need a many-to-many on Account, too (marked as inverse="true"). But you can map it as a private field instead of a property and use a property that returns the first (and only) item of that collection.

Wolfgang --

Thanks for the reply! I've taken that approach and it seems to be working reasonably well -- it's generating the exact SQL that I would expect. However, what I lose is the ability to do HQL queries like this:

Code:

from Account a where a.Individual.FirstName='bob'

Is there way to overcome this?

- chad

Try