Mapping self-table one-to-many using non-PK clolumns

Question

Hey, i have a legacy DB to which a Person object is mapped, having a collection of family-members, like this:

   class Person
    {
      ...
      string Id; /* 9-digits string */
      IList<Person> Family;
      ...
    }

The PERSON table seems like:

Id: CHAR(9), PK
FamilyId: INT, NOT NULL

and several other non-relevant columns. I'm trying to map the Family collection to the PERSON table using the FamilyId column, which is not the PK as mentioned above. So, i actually have a one-to-many which is self-table-referential.

I'm getting an error saying 'Cast is not valid' when my mapping looks like this:

...
<set name="Family" table="Person" lazy="false"> 
   <key column="FamilyId" /> 
   <one-to-many class="Person" /> 
</set>
...

because obviously, the join NHibernate is trying to make is between the PK column, Id, and the 'secondary' column, FamilyId, instead of joining the FamilyId column to itself.

Any ideas please?

Answer 1

I may not be grasping the data that you're working with here, but it sounds to me like your data model is incomplete. I would expect that you'd have a Family entity as well as a Person entity, such that every Person has a Family property to which they belong and that Family entity has a list of Person objects which belong to the family.

I guess what's missing here is enough information about your database schema that you're trying to map. What are the values that go into the FamilyId and do they reference some other table (ie a Foreign Key to the Family table)?

Answer 2

Better put:

 [ID] - PK, char(9) 
 [FirstName], char(25) not-null 
 [LastName], char(25) not-null 
 [FamilyId], int not-null

Actually, the family-relations could be modeled using a link-table bonding family members using FamilyId and the PersonId. but i cannot create it at the moment as it's an existing production system. Currently, i have to model the Family collection somehow. Aside the solution offered by BryanD, which seems pretty nice, can someone offer a way of joining the table to itself using the FamilyId column, which is not the PK?