NHibernate Mapping multiple tables to one class

Question

In my legacy Database I have a situation like this:

TableA (id_A[PK], cod_A)
TableB (id_B[PK], cod_B, id_A[FK])
TableC (id_C[PK], cod_C, id_B[FK])

For several reasons I need to map these tables into a single class (Foo in this example)

public class Foo
{
    public virtual string IdA { get; set; }
    public virtual string CodA { get; set; }

    public virtual string IdB { get; set; }
    public virtual string CodB { get; set; }

    public virtual string IdC { get; set; }
    public virtual string CodC { get; set; }    
}

By the following mapping I'm able to join Table1 and Table2 but not Table3

<class name="Foo" table="TableA">

    <id name="IdA" column="id_A"/>
    <property name="CodA" column="cod_A"/> 

    <join table="TableB">
      <key column="id_A"/>
      <property name="IdB" column="id_B"/>
      <property name="CodB" column="cod_B"/>      
    </join>

    <!--Here my problem because off course the join will be on TableA instead on TableB-->
    <join table="TableC">
      <key column="id_B"/>
      <property name="IdC" column="id_C"/>
      <property name="CodC" column="cod_C"/>      
    </join>

  </class>

How can I map Table3?

Thanks in advance.

What NH version are you using? Can you add the SQL query you receive? — Mr Mush
– Mr Mush, Commented Nov 13, 2013 at 6:48

Mr Mush · Accepted Answer · 2013-11-14 11:25:45Z

1

NHibernate discourage you to use join as much as possible. But some times there is no way around it.

The first and easiest way to solve it, if you can, is to create a view and map your class to the view.

Second way, is to create an object to each of the other tables and connect them in the proper way, probably - <one-to-one> mapping.

Third way

* Warning - proceed at your own risk *

Download NH source code - https://github.com/Nicaog/nhibernate-core/downloads.

Download the following bug patch fix instructions - https://nhibernate.jira.com/browse/NH-1681

Change the code, compile the DLL and add it to your solution.

Explanation - The patch actully fix using property-ref for <join> mapping. but also using multiple join with property-ref.

edited Nov 14, 2013 at 11:25

answered Nov 13, 2013 at 6:56

Mr Mush

1,5383 gold badges25 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Stefano Leli Over a year ago

At the end I realized that it's not possible to map multiple join tables on a single class. As you suggested I solved the problem using a view. Unfortunately this solution is not the best for my purpose. Thanks.

Mr Mush Over a year ago

There is a third way, a complicated one which I used to join multiple tables, but it involved to make changes to NH DLL.

Stefano Leli Over a year ago

It's a good idea. Could you briefly explain to me what you changed for joining multiple tables? Thanks

Mr Mush Over a year ago

I can add it to my answer, but be warned, this is a dangerous operation, that might not be supported in future versions!

Stefano Leli Over a year ago

I already tried the NH-1681 patch but it's just to change the column name (you can join other column instead the PK only) and not the table name. A possible solution would be permit nested <join> but I didn't find any patch for that.

|

Xiaolong Liu · Accepted Answer · 2017-04-26 16:09:21Z

0

<id name="IdA" column="id_A"/>
<property name="CodA" column="cod_A"/> 

<join table="TableB">
  <key column="id_A"/>
  <property name="IdB" column="id_B"/>
  <property name="CodB" column="cod_B"/>   

  <join table="TableC">
  <key column="id_B"/>
  <property name="IdC" column="id_C"/>
  <property name="CodC" column="cod_C"/>      
  </join>
</join>

Are you able to do this?

answered Apr 26, 2017 at 16:09

Xiaolong Liu

11 bronze badge

1 Comment

David L Over a year ago

Please consider adding an explanation as to why this is the answer.

Collectives™ on Stack Overflow

NHibernate Mapping multiple tables to one class

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related