Filtering and projecting an association using NHibernate QueryOver

Question

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?

Subscribers doesnt had a FK to MailingList? Why can't you just do a QueryOver.Where(s => s.MailingListId == myMailinglistId and ...).Take(100).List(); ? — Rafael Mueller, Sep 01 '11 at 13:55
The relation between `Subscriber` and `Mailinglist` is many-to-many. Currently, `Subscriber` does not have any back reference, but I don't see any problem in adding a `Mailinglists` collection property on it. — Jørn Schou-Rode, Sep 01 '11 at 18:39

score 2 · Answer 1 · answered Sep 02 '11 at 13:08

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

score 0 · Answer 2 · answered Sep 04 '11 at 20:18

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..."

Filtering and projecting an association using NHibernate QueryOver

2 Answers2

Linked