使用NHibernate QueryOver过滤和投影关联 [英] Filtering and projecting an association using NHibernate QueryOver

问题描述

假设您有一个与此相似的实体:

Suppose you have an entity similar to this:

public class Mailinglist
{
    public virtual Guid Id { get; set; }
    public virtual ICollection<Subscriber> Subscribers { get; set; }
}

该实体的NHibernate映射与您期望的一样:Id是标识符，并且Subscribers映射有<set>和<many-to-many>并引用了Subscriber实体.

The NHibernate mapping for the entity is as you would expect: Id is the identifier and Subscribers is mapped with <set> and <many-to-many> referincing a Subscriber entity.

现在，我遇到的情况是我有一个Mailinglist实例，需要获取与Subscriber属性上的某些过滤器匹配的前100个订阅者的列表.由于性能限制以及数据库中的数据量，不能选择myMailinglist.Subscribers.Where().Take().因此，我试图对NHibernate进行查询，该查询将从数据库中仅获取100个Subscriber实例.

Now, I am in a situation where I have an instance of Mailinglist and need to obtain a list of the first 100 subscribers matching some filter on Subscriber properties. Due to performance constraints, and the amount of data in the database, myMailinglist.Subscribers.Where().Take() is not an option. Hence, I am trying to put together a query for NHibernate which will fetch just the 100 Subscriber instances from the database.

我的最初尝试(不进行任何过滤)如下:

My initial attempt (without any filtering) goes like this:

var subscribers = session
    .QueryOver<Mailinglist>()
    .Where(m => m.Id == myMailinglistId)
    .JoinQueryOver(m => m.Subscribers)
    .Take(100)
    .List();

这显然是不对的，因为我返回的列表包含100个对Mailinglist的引用，这已经是我的新知识了.虽然生成的SQL看起来不错，但让我认为我只需要显式添加投影/转换.

This is obviously not right, as the list I get back contains 100 references to the Mailinglist which I already new about. The generated SQL looks pretty good though, leaving me to think that I just need to explicitly add a projection/transformation.

我一直在寻找一些相关文档来帮助我，但是似乎找不到解决此类查询的任何内容.有人可以提示我吗?

I've been trying to find some relevant documentation to help me along, but cannot seem to find anything addressing this sort of querying. Can somebody hint me along?

推荐答案

var subquery = QueryOver.Of<Mailinglist>()
    .Where(m => m.Id == myMailinglistId)
    .JoinQueryOver(m => m.Subscribers, () => subscriber)
    .Select(m => subscriber.Id);

var subscribers = session.QueryOver<Subscriber>()
    .WithSubquery.WhereProperty(s => s.Id).In(subquery)
    .Take(100)
    .List();

这篇关于使用NHibernate QueryOver过滤和投影关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！