NHibernate QueryOver 子查询 [英] NHibernate QueryOver Subquery

问题描述

我看过类似的问题，但找不到简单的解释.我可能会错过它，但我保证我看过.实际上，我什至找不到其他文档，只有一篇博文迅速掩盖了所有内容，并假设您熟悉其他形式的 NH.

I've looked at the similar questions, but can't find a simple explanation. I could have missed it, but I promise I looked. Actually I can't even find the documentation other than a single blog post that glosses over everything rapidly and assumes you're familiar with other forms of NH.

鉴于 Program 和 Topic 之间的多对多，其中后者位于 Topics 的层次结构中，我想检索给定Topic 的所有Programs，可能包括它的子主题.由于一个程序可能会列在给定父主题的多个子主题下，我需要使用子查询或处理必须使用不同的(并且 TransformUsing(Transformers.DistinctRootEntity) 的简单方法没有不行).

Given a many-to-many between Program and Topic, where the latter is in a hierarchy of Topics, I want to retrieve all the Programs for a given Topic, possibly including its subtopics. Since a program may be listed under multiple sub-topics of a given parent topic, I need to use a subquery or deal with having to use distinct (and the simple approach of TransformUsing(Transformers.DistinctRootEntity) didn't work).

原始 SQL 应该类似于

Raw SQL should be something like

SELECT ProgramId, Title, bar, baz, foo FROM Programs 
WHERE ProgramId IN 
 (SELECT ProgramId from Program_Topics WHERE TopicId IN (1, 2, ...))

将结果转换为模型类型以传输到视图.我最初的尝试是这样的:

The results are cast into a model type for transfer to the view. My initial attempt was this:

ProgramDTO pDTO = null;

/* topicIds is List<int> passed into function */

var query = Session.QueryOver<Program>()
.JoinQueryOver<Topic>(p => p.Topics)
.WhereRestrictionOn(pt => pt.Id).IsInG<int>(topicIds)     
.TransformUsing(Transformers.DistinctRootEntity)
.SelectList(list => list
        .Select(program => program.Id).WithAlias(() => pDTO.Id)
        .Select(program => program.Title).WithAlias(() => pDTO.Title)
        .Select(program => program.Location).WithAlias(() => pDTO.Location)
        .Select(program => program.Description).WithAlias(() => pDTO.Description)
)
.TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO)));

return query.List<ProgramDTO>();    

显然，这运行的是连接而不是子查询，但我找不到这样的多对多子查询的示例.

Obviously this runs a join instead of a subquery, but I can't find an example of doing a subquery with a many-to-many like this.

public class Program : Entity {
    public virtual ISet<Topic> Topics { get; protected internal set; }
     ...
}

public class Topic : Entity {
    public virtual ISet<Program> Programs { get; protected internal set; }
    public virtual Topic ParentTopic { get; protected internal set; }
    ...
}

推荐答案

好吧，对这个问题进行了更多讨论，虽然我不喜欢结果的一部分，但它确实有效:

Well, hashed at this some more, and while I don't like one part of the results, it does work:

var distinctProgIdsSubQuery = QueryOver.Of<Program>().
JoinQueryOver<Topic>(p => p.Topics).
WhereRestrictionOn(pt => pt.Id).IsIn(topicIds)
.Select(Projections.Distinct(Projections.Property<Program>(p => p.Id)));


ProgramDTO pDTO = null;
var progQuery = Session.QueryOver<Program>()
    .WithSubquery.WhereProperty(p => p.Id).In(distinctProgIdsSubQuery)
    .SelectList(list => list
        .Select(program => program.Id).WithAlias(() => pDTO.Id)
        .Select(...)
        )
    .TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO)));


return progQuery.List<ProgramDTO>();

这会产生

SELECT this_.ProgramId as y0_, ...
FROM Programs this_ 
WHERE this_.ProgramId in (
        SELECT distinct this_0_.ProgramId as y0_ 
        FROM
            Programs this_0_ 
        inner join
            Programs_Topics topics3_ 
                on this_0_.ProgramId=topics3_.ProgramId 
        inner join
            Topics topic1_ 
                on topics3_.TopicId=topic1_.TopicId 
        WHERE
            topic1_.TopicId in (
                @p1, @p2, ...
            )
    ) 

这可能是 NH 的限制，但是没有需要在子查询中加入 Programs 表.我试图从另一个方向写这个——也就是说，创建一个 QueryOver.Of()，但我无法弄清楚如何在最后选择程序 ID——select 只给我 TopicIds，即使这样查询仍然连接所有三个表.

This may be a limitation of NH, but there's no need to join the Programs table in the subquery. I tried to write this from the other direction -- that is, to create a QueryOver.Of<Topic>(), but I could not figure out how to select the program IDs at the end -- select was only giving me the TopicIds, and even then the query was still joining all three tables.

我不确定 MS-SQL 的查询优化器是否会避免无用的连接，但如果我们不必依赖它就好了.

I'm not sure if MS-SQL's query optimizer will avoid the useless join or not, but it would be nice if we didn't have to rely on it.

不过，就目前而言，这是有效的，希望其他人比我试图解决这个问题时更少头疼.

For now though, this works, and hopefully someone else has fewer headaches than I did trying to figure this out.

这篇关于NHibernate QueryOver 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！