Left Join in NHibernate HQL

Question

Is it possible to perform a left join between two tables that are not related each other through a parent-child or many-to-many relationship ?. All the samples I found around only show those scenarios.

I have the following tables,

Sync -> Id (string) -> EntityId (string) -> OtherInfo

Customer -> Id -> OtherInfo

Project -> Id -> OtherInfo

Sync is a generic table for storing metadata about the rest of the tables. EntityId represents the field "Id" in those tables (There is not a physical foreign key created between sync and the rest of the tables).

I basically want to perform the following query in SQL,

select s., e. from sync s left join entity e on s.entityid = e.id /

(entity in the sql above should be replaced by a real table, customer, project or any other table that contains some data in the sync table).

Could anyone give me some help or guidance about how that query should be expressed in HQL ?.

Thanks Pablo.

Answer 1

To my knowledge, you can't do this directly. HQL is object-oriented and only knows about the entities and relationships that have been mapped. You might be able to get this working by doing a session.CreateSQLQuery and using .AddEntity to have NHibernate build an entity result from the results of the SQL query.

Answer 2

This is not a left join I know, but it might give some pointers:

http://jaychapman.blogspot.com/2007/10/nhibernate-non-mapped-joins.html

It's a long standing issue to have unmapped left join in NH, and be aware that there are caveats to the workarounds. Look here at the issue and the first pair of comments here: http://nhjira.koah.net/browse/NH-514