NHibernate (3.1) subquery on subclass not joining base class table

Question

The following is an abstraction of an actual problem I'm having.

public class Base
{
    public virtual string Id { get; set; }
    public virtual string Foo { get; set; }
}

public class Sub : Base
{
    public virtual string Bar { get; set; }
    public virtual Other Other { get; set; }
}

public class Other
{
    public virtual string Id { get; set; }
    public virtual ICollection<Sub> Subs { get; set; }
}

mapping:

<class name="Base" table="base_table" xmlns="urn:nhibernate-mapping-2.2">
  <id name="Id" column="id">
    <generator class="assigned" />
  </id>
  <property name="Foo" column="Foo" />

  <joined-subclass name="Sub" table="sub_table">
    <key column="id" />
    <property    name="Bar"   column="Bar" />
    <many-to-one name="Other" column="other_id" />
  </joined-subclass>

</class>

<class name="Other" table="other_table" xmlns="urn:nhibernate-mapping-2.2">
  <id name="Id" column="id">
    <generator class="assigned" />
  </id>
  <set name="Subs" inverse="true" lazy="true">
    <key column="other_id" />
    <one-to-many class="Sub" />
  </set>
</class>

The following fails to do the join on the joined-subclass inside the subquery:

Session.Query<Other>().Where(o => o.Subs.Any(s => s.Foo == "xyz"));

Sql

select
    other0_.id as id60_
from
    other_table other0_
where
    exists (
        select
            subs1_.id
        from
            sub_table subs1_
        where
            other0_.id=subs1_.other_id
            and subs1_1_.Foo=:p0
    );
:p0 = 'xyz' [Type: String (0)]

A GenericADOException is thrown because subs1_1_ (e.g. sub_table) in the subquery doesn't have Foo. Is there something I have to do in Other's mapping to cause the Subs to be fully joined with Base in the subquery?

Do you have the exact SQL? It might just be lazy loading the Base properties. — kͩeͣmͮpͥ ͩ, May 13 '11 at 16:13
did you mapped the base propperty of the entity you mapping? — cpoDesign, May 13 '11 at 17:12
@TravisHeseman Did you find a solution to this ultimately? I am facing a similar problem and being compelled to used `table-per-hierarchy` which breaks in some other Linq situations. — Suhas, May 01 '14 at 06:53
I moved away from this project some years back and I honestly can't say if this has be resolved by the NHibernate team. — Travis Heseman, May 01 '14 at 21:15

score 1 · Answer 1 · edited Nov 02 '12 at 12:57

Maybe it is an linq provider issue related to joined subclass mapping. The query works well for table per hierarchy. Try this:

<class name="Base" table="base_table" xmlns="urn:nhibernate-mapping-2.2">
    <id name="Id" column="id">
        <generator class="assigned" />
    </id>

    <discriminator column="Type" type="String" />

    <property name="Foo" column="Foo" />

    <subclass name="Sub" discriminator-value="Sub">
        <property    name="Bar"   column="Bar" />
        <many-to-one name="Other" column="other_id" />
    </subclass>
</class>

<class name="Other" table="other_table" xmlns="urn:nhibernate-mapping-2.2">
    <id name="Id" column="id">
        <generator class="assigned" />
    </id>
    <set name="Subs" inverse="true" lazy="true">
        <key column="other_id" />
        <one-to-many class="Sub" />
    </set>
</class>

Unfortunatelly joined subclass cannot be replaced by table per hierarchy in all cases...

There are some issues registered in NH jira that looks similar (NH-2564, NH-2491) but not sure they are really related.

EDIT: The same problem is with HQL: from Other o where exists(from o.Subs s where s.Foo = 'xyz') therefore it is not an issue related to linq provider.

NHibernate (3.1) subquery on subclass not joining base class table

1 Answers1