Filtering and projecting an association using NHibernate QueryOver

[Jørn Schou-Rode]

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?

[cidico]

You can perform a subquery to select just the 100 and use the subquery in your main query.

Does not it not works for you?

[Jørn Schou-Rode]

Thanks for the quick reply!

It sounds like it might work. Can you give me a hint at how the query
would look? I've tried quite a few things with "WithSubquery", but I
still could not figure out how to make the query return Subscriber
objects rather than Mailinglist objects.

/Jørn

[cidico]

if you do, you can do something like this:

var subQuery = QueryOver.Of<MailingList>()

        .Where(m => m.Id == myMailinglistId)

        .Select(Projections.Id())

var query = Session.QueryOver<Subscriber>().WithSubquery.WhereProperty(x=> x.MailList).In(subQuery).Take(100).List();

Something like that.. :)