NHibernate-左联接 [英] NHibernate - Left joins
问题描述
我有以下两个表:
职位,区域ID,职位编号(复合键)
Jobs AreaID, JobNo (composite key)
日志,LogID,AreaID,JobNo
Logs LogID, AreaID, JobNo
我需要获取没有任何与其关联的日志的所有作业.在SQL中,我可以这样做:
I need to get all jobs that don't have any logs associated with them. In SQL I could do:
SELECT Jobs.AreaID,
Jobs.JobNo
FROM Jobs
LEFT JOIN Logs
ON Jobs.AreaID = Logs.AreaID
AND Jobs.JobNo = Logs.JobNo
WHERE Logs.LogID is null
但是我不确定如何使用NHibernate做到这一点.谁能提供任何指针?
But I'm not sure how to accomplish this with NHibernate. Could anyone offer any pointers?
这是我的映射:
<class name="Job" table="Jobs">
<composite-key name="Id">
<key-property name="JobNo"/>
<key-many-to-one name="Area" class="Area" column="AreaID"/>
</composite-key>
</class>
<class name="Log" table="Logs">
<id name="Id" column="LogID">
<generator class="identity"/>
</id>
<property name="JobNo"/>
<many-to-one name="Area" class="Area" column="AreaID"/>
</class>
谢谢
更新
好的,我稍微修改了Nosila的答案,现在这就是我想要的:
OK, I modified Nosila's answer slightly, and this is now doing what I wanted:
Log logs = null;
return session.QueryOver<Job>()
.Left.JoinAlias(x => x.Logs, () => logs)
.Where(x => logs.Id == null)
.List<Job>();
我还必须将其添加到我的工作映射中:
I also had to add this to my Job mapping:
<bag name="Logs">
<key>
<column name="JobNo"></column>
<column name="DivisionID"></column>
</key>
<one-to-many class="Log"/>
</bag>
感谢您的帮助. :)
推荐答案
我不熟悉复合标识符,因为我不使用它们,因此,我所知道的所有NHibernate都会自动创建正确的左联接.尽管如此,下面的(未经测试的)查询应该可以帮助您入门.
I'm not familiar with composite identifiers as I don't use them so for all I know NHibernate will automatically create the proper left join. None the less, the (non-tested) query below should get you started.
Job jobAlias = null;
Log logAlias = null;
YourDto yourDto = null;
session.QueryOver<Job>()
// Here is where we set what columns we want to project (e.g. select)
.SelectList(x => x
.Select(x => x.AreaID).WithAlias(() => jobAlias.AreaID)
.Select(x => x.JobNo).WithAlias(() => jobAlias.JobNo)
)
.Left.JoinAlias(x => x.Logs, () => logAlias, x.JobNo == logAlias.JobNo)
.Where(() => logAlias.LogID == null)
// This is where NHibernate will transform what you have in your `SelectList()` to a list of objects
.TransformUsing(Transformers.AliasToBean<YourDto>())
.List<YourDto>();
public class YourDto
{
public int AreaID { get; set; }
public int JobNo { get; set; }
}
注意:您需要NHibernate 3.2才能设置连接条件.
Note: You need NHibernate 3.2 in order to set join conditions.
这篇关于NHibernate-左联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!