在 NHibernate 上使用 QueryOver 限制儿童计数 [英] Get children count with restrictions using QueryOver on NHibernate

问题描述

如何使用 QueryOver 而没有公式字段执行以下操作.

How can I do the following using QueryOver and no Formula Fields.

我有以下父子关系

public class Club
{
    public string Name { get; set; }

    public IList<Membership> Memberships { get; set; }
}

public class Membership
{
    public boolean Cancelled { get; set; }

    public Club Club { get; set; }
}

我有以下查询，该查询返回 15 个俱乐部并将结果转换为 DTO，我需要向此查询添加属于每个俱乐部且未取消的会员的 RowCount.

I have the following query that returns 15 Clubs and transforms the results to a DTO, I need to add to this query a RowCount of Memberships that belong to each club and that are not Cancelled.

IEnumerable<ClubIndexViewModelLineSummary> results = _querySession.QueryOver<Club>()
    .OrderBy(c => c.IsActive).Desc
    .OrderBy(c => c.Name).Asc
    .SelectList(list => list
                        .Select(c => c.Id).WithAlias(() => sum.Id)
                        .Select(c => c.Name).WithAlias(() => sum.Name)
                        .Select(c => c.IsActive).WithAlias(() => sum.IsActive)
                        .Select(c => c.NumberOfWeeks).WithAlias(() => sum.NumberOfWeeks))
    .TransformUsing(Transformers.AliasToBean<ClubIndexViewModelLineSummary>())
    .Skip(start)
    .Take(15)
    .Future<ClubIndexViewModelLineSummary>();

推荐答案

经过更多研究，这是有效的查询结果:

After some more research, this is the resulting query that works:

Club clubAlias = null;

IEnumerable<ClubIndexViewModelLineSummary> results = 
 _querySession.QueryOver<Club>(() => clubAlias)
    .OrderBy(c => c.IsActive).Desc
    .OrderBy(c => c.Name).Asc
    .SelectList(list => list
                            .Select(c => c.Id).WithAlias(() => sum.Id)
                            .Select(c => c.Name).WithAlias(() => sum.Name)
                            .Select(c => c.IsActive).WithAlias(() => sum.IsActive)
                            .Select(c => c.NumberOfWeeks).WithAlias(() => sum.NumberOfWeeks)
                            .SelectSubQuery
                            (
                                QueryOver.Of<ClubMembership>()
                                    .Where(c => c.Canceled == false)
                                    .And(c=> c.Club.Id == clubAlias.Id)
                                    .ToRowCountQuery()
                            ).WithAlias(()=> sum.ActiveMembers))
    .TransformUsing(Transformers.AliasToBean<ClubIndexViewModelLineSummary>())
    .Skip(start)
    .Take(15)
    .Future<ClubIndexViewModelLineSummary>();

这篇关于在 NHibernate 上使用 QueryOver 限制儿童计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！