How can I generate subclass joins in a NHibernate SqlQuery?

Question

I am having problems getting NHibernate to generate a SQL query that actually runs without errors, as the query is missing joins for my subclasses.

Lets take this minimal example:

class Page
{
    public virtual int Id { get; set; }
    public virtual string UrlSegment { get; set;}
    public virtual Page Parent { get; set; }
}

class ContentPage
{
    public string Content { get; set; }
}

Now, imagine a straightforward field-for-field, joined-subclass mapping of this to the database. I came up with this query to find all pages along the path /page/page2/page3:

SELECT p1.* FROM page p1
WHERE
    p1.parent IS NULL
    AND p1.hierarchysegment = 'test'

UNION

SELECT p2.* FROM page p1
JOIN page p2 ON p2.parent = p1.id
WHERE
    p1.parent IS NULL
    AND p1.hierarchysegment = 'test'
    AND p2.hierarchysegment = 'test2'

UNION

SELECT p3.* FROM page p1
JOIN page p2 ON p2.parent = p1.id
JOIN page p3 ON p3.parent = p2.id
WHERE
    p1.parent IS NULL
    AND p1.hierarchysegment = 'test'
    AND p2.hierarchysegment = 'test2'
    AND p3.hierarchysegment = 'test3'
;

Now, this seems to work ok when running the query. Lets try generating this with a SqlQuery (simplified, only shows the first part):

IQuery q = session.CreteSQLQuery("SELECT {p0.*} FROM page {p0} WHERE {p0.Parent} IS NULL" +
                                 "AND {p0.HierarchySegment} = 'page1'", "p0", typeof(Page));

This generates the following query:

SELECT p0.ID            AS id0_0_,
    p0.enabled          AS enabled0_0_,
    p0.linktext         AS linktext0_0_,
    p0.hierarchysegment AS hierarch4_0_0_,
    p0.tooltiptext      AS tooltipt5_0_0_,
    p0.PARENT           AS parent0_0_,
    p0_1_.content       AS content1_0_,
    CASE
      WHEN p0_1_.ID IS NOT NULL
      THEN 1
      WHEN p0.ID IS NOT NULL
      THEN 0
    END AS clazz_0_
FROM   page p0
WHERE  parent0_0_ IS NULL
    AND hierarch4_0_0_ = page1; 

But this query contains SQL errors because NHibernate tries to fetch fields which are only available after NHibernate has also added joins for the subclass, ContentPage. Is it possible to somehow add these joins without relying on a specific naming style that NHibernate happens to be using (ie, generating the necessary joins manually)?

Answer 1

You can get close with a HQL query.

As there is no UNION in HQL, I use MultiQuery. Also the joined-subclass means each of the queries will have a LEFt JOIN to the contentpage table.

var q1 = session.CreateQuery(@"
    from    Page p1
    where   p1.Parent is null
    and     p1.UrlSegment=:s1" );

var q2 = session.CreateQuery(@"
    select  p2
    from    Page p2 inner join p2.Parent p1 
    where   p1.Parent is null
    and     p1.UrlSegment=:s1
    and     p2.UrlSegment=:s2" );

var q3 = session.CreateQuery( @"
    select  p3
    from    Page p3 inner join p3.Parent p2
            inner join p2.Parent p1
    where   p1.Parent is null
    and     p1.UrlSegment=:s1
    and     p2.UrlSegment=:s2
    and     p3.UrlSegment=:s3" );

var results = session.CreateMultiQuery()
    .Add<Page>( q1 )
    .Add<Page>( q2 )
    .Add<Page>( q3 )
    .SetParameter("s1", "test1")
    .SetParameter("s2", "test2")
    .SetParameter("s3", "test3")
    .List();

var l1 = (IList<Page>) results[0];
var l2 = (IList<Page>) results[1];
var l3 = (IList<Page>) results[2];

if (l1.Count != 0)  p1 = l1[0];
if (l2.Count != 0)  p2 = l2[0];
if (l3.Count != 0)  p3 = l3[0];