NH-3188 - Joining multiple tables with mapping by code does not work properly.

[nhibernate-bot]

Juan Carlos Correa created an issue — 14th June 2012, 15:18:24:

We need to map 3 tables to one class with Mapping By Code using the fluent API.

This is the basic mapping for the principal table

           // schema
            Schema("dbo");
            // table
            Table("TEST_Principal");
            // id
            Id(i => i.Id, m => m.Column("PrincipalID"));

and we want to get properties from two more tables. So we do

            Join(
                "TEST_PrincipalExtra",
                m => {
                    m.Key(k => { k.Column("PrincipalExtraID"); });
                    m.Property(i => i.Prop1, n => { n.Column("Column1"); });
                });
// and
            Join(
                "TEST*Principal*Aud",
                m => {
                    m.Key(k => { k.Column("Principal_AudID"); });
                    m.Component(i => i.AuditInformation);
                });

Based on the output from NHibernate profiler we get:

select
/// properties here
from
     TEST_Principal p inner join
     TEST*PrincipalExtra pe on p.PrincipalID = a.Principal*AudID inner join
     TEST*Principal_AuditID a on p.PrinciaplID = a.Principal*AudID

Notice how it correctly gets the tables for the join and also it associates correctly the columns from each table. But the m.Key statement from the second join overrides the previous one and makes the query fail because the ID for TESTPrincipalExtra is not Principal_AudID. We have found that if you swap the order of the join statements then we get the same error but now on TEST_PrincipalAud. So NHibernate stays with the last Key statement.
But if we do the mapping on xml it works perfectly.

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

    <class name="TESTPrincipalClass" table="TESTPrincipal">

        <id name="Id" column="PrincipalID" />

        <join table="TEST_PrincipalExtra">
            <key column="PrincipalExtraID" />
            <property name="Prop1" column="Column1" />
        </join>

        <join table="TEST*Principal*Aud">
            <key column="Principal_AudID" />
            <component name="AuditInformation" />
        </join>
    </class>
</hibernate-mapping>

---

Alexander Zaytsev added a comment — 13th May 2013, 14:28:26:

It seems related to NH-3269.

Can you please check if fix in pull-request #189 works for you.

Please provide a test case if fix does not work.

---

sca_tone added a comment — 9th July 2014, 9:33:20:

Got a similar issue with version 4.0.0.1000 of NHibernate.
Except for me the second mapping of the join overwrites the first.

So my output is the following:
{quote}
select
/// properties here
from
TEST_Principal p inner join
TEST_Principal_AuditID pe on p.PrinciaplID = a.Principal_AudID inner join
TESTPrincipal_AuditID a on p.PrinciaplID = a.PrincipalAudID
{quote}

Notice how the first inner join is a duplicate of the second join except for the alias.

---

Alexander Zaytsev added a comment — 24th November 2014, 11:07:56:

I could not reproduce this issue with 4.0.2, can you please provide a failing test case? /cc <_{sca_tone> [}correa.cherone]

[ricovivid]

I'm experiencing the same behavior and I'm using version 4.1.1.

[craigfowler]

I have also reproduced this issue, and I have been able to create a minimal reproduction case. This contains two NUnit tests (which may be run via dotnet test). One test is a reference-case showing that NHibernate does not raise an exception when using HBM XML mappings. The other test demonstrates the bug when an equivalent set of Mapping By Code mappings are used instead of XML.

In doing so, I have confirmed that this is still an issue as of NHibernate 5.3.2.

Workaround

I have been able to work around this by simply not using Mapping By Code for entities in which I need more than a single Join mapping. Thankfully needing multiple joins in a single class is a quite rare scenario.

In those cases I just produce HBM XML mappings for the affected entities. It is currently possible for MbC and HBM XML mappings to coexist in the same configuration/session factory. However, beware that when combining MbC & HBM XML mappings, there is a different issue to deal with, related to the usage of joins. That issue does have a workaround of its own available, though.