使用 NHibernate QueryOver 过滤和投影关联

发布于 2024-12-02 08:41:54 字数 1028 浏览 4 评论 0原文

假设您有一个与此类似的实体：

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

该实体的 NHibernate 映射正如您所期望的那样：Id 是标识符，Subscribers 使用 进行映射; 和引用 Subscriber 实体。

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

我最初的尝试（没有任何过滤）是这样的：

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

这显然是不对的，因为我得到的列表包含 100 个对我已经熟悉的 Mailinglist 的引用。不过，生成的 SQL 看起来相当不错，让我认为我只需要显式添加投影/转换。

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

原文

Suppose you have an entity similar to this:

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

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.

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();

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月寒剑心 2024-12-09 08:41:54

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();

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();

回复收藏 0 原文

海螺姑娘 2024-12-09 08:41:54

HQL不是更容易阅读吗？

尚未测试，但如下所示：

Query query = 
 session.createQuery("from Mailinglist m where m.subscriber = :code ")
.SetMaxResults(100)
.setParameter("code", "%john%");
List list = query.list();

setMaxResults 应转换为 T-SQL“选择前 100 个...”

Would HQL not be easier to read?

Have not tested but something in the lines of:

Query query = 
 session.createQuery("from Mailinglist m where m.subscriber = :code ")
.SetMaxResults(100)
.setParameter("code", "%john%");
List list = query.list();

The setMaxResults should translate to T-SQL "select top 100..."

回复收藏 0 原文

~没有更多了~