Left Join NHibernate HQL
Can I make a left join between two tables that are not related to each other through a parent-child or many-to-many relationship? All the samples I found show only these scenarios.
I have the following tables,
Synchronization -> Id (string) -> EntityId (string) -> OtherInfo
Client -> Id -> OtherInfo
Project -> Id -> OtherInfo
Sync is a general table for storing metadata for other tables. EntityId represents the "Id" field in these tables (there is no physical foreign key generated between sync and the rest of the tables).
Basically I want to execute the following query in SQL,
select s., e. from sync s of the left union object e to s.entityid = e.id/
(the object in sql above should be replaced with a real table, client, project, or any other table containing some data in the sync table).
Can anyone give me some help or guidance on how this query should be expressed in HQL ?.
Thanks Pablo.
source to share
As far as I know, you cannot do this directly. HQL is object oriented and only knows about the entities and relationships that have been mapped. You can get this working by doing session.CreateSQLQuery
and using .AddEntity
NHibernate to build the entity result from the results of the SQL query.
source to share
It's not a left join I know, but it might give some pointers:
http://jaychapman.blogspot.com/2007/10/nhibernate-non-mapped-joins.html
This is a long-standing issue with immediate merging in NH and remember that there are workarounds to workarounds. Look here for the question and the first couple of comments here: https://nhibernate.jira.com/browse/NH-514
source to share