CODEWITHSUNDEEP
nhibernatequeryover
Nlr
Nlr

Reputation: 213

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

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.

Upvotes: 1

Views: 3751

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

There are similar questions, e.g.

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

Upvotes: 1

Related Questions