NHibernate QueryOver 子查询仅返回最新记录

Question

我在 Nhibernate QueryOver 中有一个查询，它带回了一组剧集对象(剧集是一个护理咒语)，这些对象又具有一组剧集状态作为每个剧集的属性。但是我想改变这一点，以便每一集只带回该集的最新状态更新，而不是所有这些。

执行此操作的 SQL 如下:

SELECT  *
FROM    DIPEpisode e
INNER JOIN DIPEpisodeStatus s on s.EpisodeID = e.SequenceID
WHERE e.ClientID = '1000001'
AND s.SequenceID IN (
SELECT TOP 1 SequenceID
FROM DIPEpisodeStatus s
WHERE s.EpisodeID = e.SequenceID
ORDER BY StatusRecordedDate DESC
)

我编写了以下查询，它几乎完全满足了我的需求

var statuses =
            QueryOver.Of<DIPEpisodeStatus>()
            .OrderBy(x => x.StatusRecordedDate).Desc
            .Select(x => x.Id).Take(1);

DIPEpisodeStatus statusAlias = null;

        return
            session.QueryOver<DIPEpisode>()
            .JoinQueryOver(x => x.DIPEpisodeStatuss, () => statusAlias)
            .Fetch(x => x.AgencyID).Eager
            .Fetch(x => x.DIPEpisodeStatuss).Eager
            .Where(e => e.ClientID.Id == this.clientId)
            .WithSubquery.WhereProperty(x => x.Id).Eq(statuses)
            .List();

这将生成以下 SQL:

SELECT *
FROM   DIPEpisode this_
   inner join DIPEpisodeStatus statusalia1_
     on this_.SequenceID = statusalia1_.EpisodeID
WHERE  statusalia1_.ClientID = '1000001' /* @p0 */
   and statusalia1_.SequenceID = (SELECT TOP (1 /* @p1 */) this_0_.SequenceID as y0_
                                  FROM   DIPEpisodeStatus this_0_
                                  ORDER  BY this_0_.StatusRecordedDate desc)

如您所见，唯一缺少的是子查询中的 where 子句。我需要对查询进行哪些更改才能生成此额外的 where 子句并仅拉回最近的状态更新？

谢谢

本

Answer 1

收藏DIPEpisodeStatuss始终使用所有实体进行初始化，否则会破坏更改跟踪。您可以为集合定义过滤器，也可以返回带有所需内容的 DTO。此外，fetch 将被忽略，因为它不能在一个 sql 语句中预先加载和过滤。

NHibernate filters are explained here

defining Filters in FNH

如何使用 DTO 完成

// assuming SequneceID and StatusRecordedDate correlates
var subquery = QueryOver.Of<DIPEpisode>()
        .Where(e => e.ClientID.Id == this.clientId)
        .JoinAlias(e => e.DIPEpisodeStatuss, () => statusAlias)
        .Select(Projections.Max(() => statusAlias.SequenceID));

// or as in question
var subquery = QueryOver.Of<DIPEpisode>()
        .Where(e => e.ClientID.Id == this.clientId)
        .JoinAlias(e => e.DIPEpisodeStatuss, () => statusAlias)
        .OrderByDescending(() => statusAlias.StatusRecordedDate)
        .Select(() => statusAlias.SequenceID)
        .Take(1);

DIPEpisodeDto dto = null;
DIPEpisodeStatus statusAlias = null;
return session.QueryOver<DIPEpisode>()
        .Where(e => e.ClientID.Id == this.clientId)
        .JoinQueryOver(e => e.DIPEpisodeStatuss, () => statusAlias)
        .WithSubquery.WhereProperty(estatus => estatus.Id).Eq(statuses)
        .SelectList(list => list
            .Select(e => e.Whatever).WithAlias(() => dto.Whatever)
            .Select(() => statusAlias.SquenceId).WithAlias(() => dto.StatusId)
            ...
        )
        .TransFormUsing(Transformers.AliasToBean<DIPEpisodeDto>())
        .List();

或使用 LINQ

var query = from e in session.Query<DIPEpisode>()
            from s in e.DIPEpisodeStatuss
            where e.ClientID.Id == this.clientId
            where s.Id == (
                from e2 in session.Query<DIPEpisode>()
                from s2 in e2.DIPEpisodeStatuss
                orderby s2.StatusRecordedDate descending
                select s2.Id)
                .First()
            select new DIPEpisodeDto
            {
                e.Prop1,
                Status = s,
            };
return query.List<DIPEpisodeDto>();