NHibernate QueryOver和子查询的问题 [英] Issues with NHibernate QueryOver and Subqueries
问题描述
我有以下模型:
public class User
{
public virtual int Id { get; set; }
public virtual string Username { get; set; }
public virtual string Password { get; set; }
public virtual string Email { get; set; }
public IList<SubmissionNote> Notes { get;set; }
}
public class SubmissionNote
{
public virtual Int64 Id { get; set; }
public virtual string Text { get; set; }
public virtual DateTime CreatedOn { get; set; }
public virtual User Creator { get; set; }
public virtual Submission BelongsTo { get; set; }
}
public class Submission
{
public virtual Int64 Id { get; set; }
public virtual DateTime HappenedOn { get; set; }
public virtual int StatusCode { get; set; }
}
我想编写一个查询,该查询将所有最后提交的注释由某个用户或用户列表(由其名称标识)输入的所有内容提交.感谢您为使用NHibernate QueryOver API构建此查询提供的帮助.我已经能够创建一个查询,以使所有最后的注释如下:
I would like to write a query that brings all the Submissions whose last note was entered by some user or a list of users (identified by their names). I would appreciate your help for building this query using NHibernate QueryOver API. I have been able to create a query to bring all the last notes as follows:
SubmissionNote alias = null;
var lastNote = session.QueryOver<SubmissionNote>()
.SelectList(list => list.
SelectGroup(x => x.BelongsTo).WithAlias(() => alias.BelongsTo).
SelectMax(x => x.CreatedOn).WithAlias(() => alias.CreatedOn).
Select(x => x.Creator).WithAlias(() => alias.Creator).
Select(x => x.Id).WithAlias(() => alias.Id).
Select(x => x.Text).WithAlias(() => alias.Text))
.TransformUsing(Transformers.AliasToBean<SubmissionNote>())
.List();
我可以使用指定的查询作为子查询来产生所需的结果吗?还是您建议其他解决方案?
Can I use the specified query as a subquery to produce the required result? Or do you suggest another solution?
推荐答案
我会通过子查询尝试此类操作(虽然未测试)
I would try something like this with a subquery (not tested though)
SubmissionNote subNoteAlias = null, subNoteAliasMax =null;
User userAlias = null;
String[] userNames = new[]{"John","Joe"};
var subquery =
QueryOver.Of(() => subNoteAliasMax)
.Where(subNote => subNote.BelongsTo.Id == subNoteAlias.BelongsTo.Id)
.Select(Projections.Max<SubmissionNote>(subNote => subNote.CreatedOn));
var result = session
.QueryOver<SubmissionNote>(() => subNoteAlias)
.WithSubquery.WhereProperty(subNote=>subNote.CreatedOn).Eq(subquery)
.JoinQueryOver(
subNote=>subNote.Creator
,()=>userAlias
,JoinType.InnerJoin
,Restrictions.In(Projections.Property(() => userAlias.Username ), userNames))
.List();
生成的SQL查询(非常)大致类似于:
The generated SQL query would (very) roughly look like :
SELECT SubmissionNote.*
FROM SubmissionNote INNER JOIN User
ON SubmissionNote.creatorId = User.userId
AND User.userName in ('john','joe')
WHERE SubmissionNote.CreatedOn = (SELECT MAX(CreatedOn) FROM SubmissionNote snAlias
WHERE snAlias.SubmissionId = SubmissionNote.SubmissionId)
希望这会有所帮助.子查询返回日期而不是ID可能存在问题. LMK,我来看看.
Hope this will help. There might be issues with the subquery returning a date instead of an id. LMK and I will give it a look.
这篇关于NHibernate QueryOver和子查询的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!