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?

Answer 1

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