Search code examples
joinnhibernatesubqueryqueryover

NHibernate (+ FluentNhibernate) : Join two detached tables

'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.

Solution

  • We can use HQL - but only HQL.

    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