3

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

Guillaume
  • 844
  • 7
  • 25

1 Answers1

2

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

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • Ok so i can't use QueryOver notation ? In fact, the basic problem is that i can't use the LIKE with Linq notation. – Guillaume Dec 17 '14 at 10:06
  • For queries like you need the answer is **no** you cannot use query over. just HQL. Every ORM is about mapped relations. This is it. NHibernate has a bonus (HQL) where we can break it. But not for any other queries... – Radim Köhler Dec 17 '14 at 10:07