NHibernate HQL subselect left join?

Question

I have the following issue and I would really appreciate your help:

I have all my filtering done through the HQL and one of the filters is a left outer join which must be a sub-select. So my thinking here is that I can create a dummy business object which I would like to populate (with an SP if possible) with data and use it in my left join.

Now how can I do that and still have all of the logic in 1 HQL query?

I guess the biggest issue for me is understanding how can I have this BO (not actually mapped to a table etc.) be used in a query where all the other BOs are mapped to tables etc.

I am trying to avoid doing any filtering in the actual C# code.

Thanks!

Example:

Entity A - mapped to table A

Entity B - mapped to table B

Entity C - mapped to table C

Entity D - not mapped to table - coming from SQL query or SP

HQL:

from A pbo

inner join B.EntityType etype

inner join C.EntityAddressList eadr

left join D.Level lvl

Can you show us your query, relevant entities, and what you'd like to accomplish? Your question is a little vague in its current form. — Vadim

Vadim Vadim · Accepted Answer · 2011-03-03T17:44:23

You cannot join to arbitrary SQL in HQL. So I don't think what you're trying to do is possible. You'll likely either have to map whatever D is, or write your query in SQL using Session.CreateSQLQuery(). You can still specify any entities that come back. See this article for reference. http://www.nhforge.org/doc/nh/en/index.html#d0e10274

NHibernate HQL subselect left join?

HQL:

1 Answers