How to join Two tables of two non relashinship defined columns using Nhibernate QueryOver

Question

Using NHibernate QueryOver, I want to join two tables using two columns which are not defined in the mapping as a relationship.

E.g. This is not my exact scenario but this can explain that

Tables:

Employee(Id, Name, DepartmentId, SomeCode,Address)

Department (Id, Name, ,Code)

Select

SELECT * 
  FROM EMPLOYEE E 
  JOIN DEPARTMENT D 
    ON D.Code = E.SomeCode

Can someone please tell me how to do this query using NHibernate QueryOver. Note that "SomeCode" in Employee and "Code" in Department are not defined as a relationship. DepartmentId is the foreign key and I can join these using JoinAlias but I want it in a somewhat different way.

Answer 1

There are similar questions, e.g.

• NHibernate (+ FluentNhibernate) : Join two detached tables
• Hibernate Criteria Projection without mapped association of tables

And the answer is:

1. either use HQL and CROSS JOIN (with WHERE)
2. There is NO way how to do that with QueryOver/Criteria)

See the doc:

14.2. The from clause

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

from Formula, Parameter

from Formula as form, Parameter as param

So using HQL in your case, we can do it like this:

SELECT ...
FROM EMPLOYEE E, DEPARTMENT D 
WHERE D.Code = E.SomeCode
...

BUT I would suggest: create that mapping in the code. Simply introduce many-to-one relation. It will be loaded lazily (only if used) and will nicely serve to our needs - be used in QueryOver as a relation for Join

If there is such relation, if this exist in the business object domain, we should not be scared to use it. We can hide it via security etc...