NHibernate左外部联接无关实体 [英] NHibernate Left Outer Join Unrelated Entities
问题描述
我有2个实体Role&翻译.
角色->角色ID,代码
翻译->代码,语言,名称
I have 2 entities Role & Translation.
Role -> Role_ID, Code
Translation -> Code, Language, Name
这个想法是要说某个角色,它有英文名,法文名等.
例如:
Role(1,'Rol_001')可以具有以下关系:Translation('Rol_001','English','')&翻译('Rol_001','法语','').
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', '').
我想在HQL中表达以下SQL查询:
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;
在我的映射文件中,这2个实体之间没有任何关系,但是以下HQL查询就像是内部联接一样工作
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");
如果我将HQL更改为左外部联接,则会得到联接异常预期的路径.
If I change the HQL to left outer join, I get the Path expected for join exception.
您能为我提供以下帮助吗?
1-我需要更改映射文件吗?
2-如果我可以按原样保留映射文件,如何在HQL中编写这样的查询?
3- HQL如何真正起作用?为什么这么简单的外部联接查询不起作用?我一定在这里想念东西!
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!
现在,我根据建议使用以下代码来使用CreateSQL:
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;
这是可行的,但是我想使用query.List()直接获取结果,而不是自己转换结果.
我尝试使用query.AddEntity(typeof(Lookup));
,但出现异常NHibernate.MappingException: No persister for: DAL.Domain.Lookup
.
查找只是一个POCO,不会映射到任何数据库表.它的映射文件就是<import class="Lookup" />
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" />
推荐答案
最后我找到了答案:
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;
查找是一个具有无参数构造函数和3个属性ID(名称和IsDeleted)的POCO类.
And the lookup is a POCO class with a parameterless constructor and 3 properties ID, Name and IsDeleted.
我要感谢Kelly和Diego Mijelshon的提示.尽管它们没有提供完整的答案,但是使用Session.CreateSqlQuery()是一个非常有用的提示.
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.
因此,完整的解决方案是Session.CreateSQLQuery和query.SetResultTransformer
So the complete solution is Session.CreateSQLQuery and query.SetResultTransformer
注意:Transformers.AliasToBean()就是Java.
Note: Transformers.AliasToBean() is so java.
http://docs. jboss.org/hibernate/orm/3.2/api/org/hibernate/impl/SQLQueryImpl.html 获取setString()
这篇关于NHibernate左外部联接无关实体的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!