NHibernate一对多关系聚合查询 [英] NHibernate aggregate query for one-to-many relation
问题描述
我还有下一个实体:
class Topic
{
public virtual int Id {get; private set;}
public virtual ICollection<Vote> Votes {get; private set; }
}
class Vote
{
public virtual Topic Topic {get; private set;}
public virtual VoteType VotedTo {get; private set;} // enum VotedUp, VotedDown
}
我需要从数据库中加载下一个信息-所有主题(ID,实际上是名称,但在此演示案例中都没有关系)以及另外两个字段 CountOfVotedUp,CountOfVotedDown(聚合).因此,据我了解,在SQL世界中,我们需要联接,分组,大小写和计数.
I need to load from the db the next info - all topics (IDs, actually Names, but in this demo case it does not matter) and two more fields CountOfVotedUp, CountOfVotedDown (aggregates). So as I understand in SQL world we need joins, group by, case and count.
是否可以通过LINQ进行较少的db操作来获取此信息?我的意思是N + 1,其他选择,联系方式等等.
Is it posible to get this info with LINQ with less operations with db? I mean N+1, additional selects, connections etc.
我所尝试的全部-是使用NH的LINQ,但它的Query汇总仅在Topic.Id上进行,我无法计算任何Votes集合.
All that I tried - is to use NH's LINQ, but it's Query aggregates only on Topic.Id and I could not count any of Votes collection.
推荐答案
提供了一个用于存储结果的摘要类:
Provided you have a summary class to store your result :
public class SummaryDTO
{
public int TopicId { get; set; }
public VoteType TypeOfVote { get; set; }
public int VoteCount { get; set; }
}
然后
Vote voteAlias = null;
SummaryDTO result = null;
youNHSession.QueryOver<Topic>()
.JoinAlias(x=> x.Votes, ()=>voteAlias)
.SelectList(
list=>list
.SelectGroup(topic=>topic.Id).WithAlias(()=>result.TopicId)
.SelectGroup(()=>voteAlias.VotedTo).WithAlias(()=>result.TypeOfVote)
.SelectCount(()=>voteAlias.VotedTo).WithAlias(()=>result.VoteCount ))
.TransformUsing(Transformers.AliasToBean<SummaryDTO>())
.List<SummaryDTO>();
我想这并不是您要找的东西,但是希望这能使您步入正轨.
I guess that's not exactly what you are looking for, but hope this will set you on a good track.
这篇关于NHibernate一对多关系聚合查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!