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应该类似于

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<Topic>()，但是我不知道如何最后选择程序ID-select仅给了我TopicId，即使这样，查询仍在联接所有三个表.

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屋！