NHibernate Left Outer Join Unrelated Entities

Question

I have 2 entities Role & Translation.
Role -> Role_ID, Code
Translation -> Code, Language, Name

The idea is to say for a certain role, that it has English name, French name and so on.
For example:
A Role(1, 'Rol_001') can have the relations: Translation('Rol_001', 'English', '') & Translation('Rol_001', 'French', '').

I would like to express the following SQL query in HQL:


select r.Role_ID, t.Name
from Role r left outer join Translation t 
   on r.Code = t.Code and t.Language = @lang;

In my mapping files I don't have any relation between the 2 entities but the following HQL query works as if it is inner join


IQuery query = session.CreateQuery("select new Lookup(r.Role, t.Name) from Role r, Translation t where r.Code = r.Code and t.Language = :language");

If I change the HQL to left outer join, I get the Path expected for join exception.

Can you help me with the following:
1- Do I need to change my mapping files?
2- If I can keep the mapping files as is, how write such a query in HQL?
3- How does HQL really works? Why such a simple outer join query is not working? I must be missing something here!

Edit:
Now I am using the following code based on the suggetion to use CreateSQL:


ISQLQuery query = session.CreateSQLQuery("select m.MedicineTypeID, t.Name, m.IsDeleted from MedicineType m left outer join Translation t on m.Code = t.Code and t.Language = :language");
query.SetString("language", language);
IList rawLookup = query.List();

IList medicineTypesLookup = new List(rawLookup.Count);
foreach (object[] lookup in rawLookup)
{
    medicineTypesLookup.Add(new Lookup((int)lookup[0], (string)lookup[1], (bool)lookup[2]));
}
return medicineTypesLookup;

This is working however I want to use query.List() to get the result directly instead of converting it myself.
I tried to use query.AddEntity(typeof(Lookup)); but I get the exception NHibernate.MappingException: No persister for: DAL.Domain.Lookup.
The Lookup is just a POCO and doesn't map to any database table. Its mapping file is simply <import class="Lookup" />

HQL uses mapped relationships, not arbitrary join clauses. You can use SQL to achieve that. — Diego Mijelshon, Sep 21 '10 at 19:43
This is a SQL Server CE, can I execute SQL statement using NHibernate directly or shall I use SqlCeConnection and SqlCeCommand to ExecuteReader the sql statement and get the result? — sh_kamalh, Sep 22 '10 at 14:58
I see that I can use session.CreateSQLQuery however the result is an Object[]. I am trying to find a way to get the result as IList. — sh_kamalh, Sep 22 '10 at 23:44
And finally I found the answer for that using query.SetResultTransformer(Transformers.AliasToBean()).List(); — sh_kamalh, Sep 27 '10 at 20:16
This is now possible using NHibernate v3.3.1 and the LINQ API. See the following answer for an example: http://stackoverflow.com/a/17586211/670028 — Randy Burden, Jul 11 '13 at 06:11

score 2 · Accepted Answer · edited Feb 20 '14 at 20:57

Finally I found the answer:

ISession session = NHibernateHelper.Session;
ISQLQuery query = session.CreateSQLQuery("select m.MedicineTypeID as ID, t.Name, m.IsDeleted from MedicineType m left outer join Translation t on m.Code = t.Code and t.Language = :language");
query.setString("language", language);
IList lookup = query.SetResultTransformer(Transformers.AliasToBean()).List();
return lookup;

And the lookup is a POCO class with a parameterless constructor and 3 properties ID, Name and IsDeleted.

I would like to thank Kelly and Diego Mijelshon for their hints. Although they don't provide the full answer the but using Session.CreateSqlQuery() was a very useful hint.

So the complete solution is Session.CreateSQLQuery and query.SetResultTransformer

Note: Transformers.AliasToBean() is so java.

Edit: http://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/impl/SQLQueryImpl.html for correct method of setString()

score 1 · Answer 2 · answered Sep 21 '10 at 02:33

1

You must to define the relationship in the mappings or do a subquery

answered Sep 21 '10 at 02:33

Kelly

187
1
8

1

looking at your example again you would probably have to use 2 separate queries (one for roles and one for language), then a in process linq left outer join. But, this is not a good solution IMO, you should really just define the simple relationship in the mappings or use session.CreateSqlQuery(). – Kelly Sep 21 '10 at 23:12

NHibernate Left Outer Join Unrelated Entities

2 Answers2

Linked