NHibernate (+ FluentNhibernate) : Join two detached tables

Question

'tI encounter problems to create a join on two entities with a common property but they are not map together.

Say you have an entity Article which contains a property FamilyCode and an entity Family with properties Code and Label.

In my mappings, Article doesn't reference Family and i don't want to change that (to keep compatibility with others internal and legacy methods).

So, i can't translate the below query in Nhibernate :

SELECT f.Code, f.Label
FROM Article a
INNER JOIN Family f ON a.FamilyCode = f.Code
WHERE f.Label LIKE 'p%'

I can't use JoinQuery because i can't inject a QueryOver and i don't konw if it's possible by using WithSubquery.

I tried to Future QueryOver and QueryOver and then perform a Join in memory (after .List() each) but i have too much rows for Article so it takes long time.

Do you have ideas ?

Thanks.

Radim Köhler Radim Köhler · Accepted Answer · 2014-12-17T09:48:10

We can use HQL - but only HQL.

14.2. The from clause (small cite and snippet)

Multiple classes may appear, resulting in a cartesian product or "cross" join.

from Formula, Parameter
from Formula as form, Parameter as param

So that would be the way with HQL

SELECT f.Code, f.Label
FROM Article a,
     Family f 
WHERE a.FamilyCode = f.Code
AND f.Label LIKE 'p%'

Check also this 9.3.2. The IQuery interface and maybe this Q & A

NHibernate (+ FluentNhibernate) : Join two detached tables

1 Answers