nHibernate QueryOver Subselect or Join

Question

I am trying to use query over to retrieve a collection of entities based upon a join/subquery as per the example below:

var types = new List<ActivityType>{ActivityType.CommentMedia, ActivityType.KeepMedia};

return _sessionFactory.GetCurrentSession()
                .QueryOver<Activity>()
                .Where(a.Type.IsIn(types))
                .WithSubquery.WhereExists(QueryOver.Of<Resource>()
                    .Where(k => k.MemberKey == userId)
                    .Where(k => k.ResourceKey == activity.ResourceId)
                    )
                .Take(take)
                .List();

In other words retrieve all activities which are in the resource table matching the user and resource id.

I would do this in raw sql by either joining to Resource, or a subquery:

where a.ResourceId in (select resourceKey from resource where resource.memberkey = a.MemberId)

Not sure how to proceed in nhibernate though. Any suggestions?

(We don't want to use a mapping, as we want to keep the Activity entity very simple for performance reasons)

Thanks in advance

Is it required to use QueryOver for this, or HQL is also an option? — Maksim Gladkov
Query Over would be preferable. It seems to me that it ought to be straightforward. Cheers — Jon Eastwood

Firo Firo · Accepted Answer · 2012-01-19T09:51:58

Activity activity = null;
return _sessionFactory.GetCurrentSession()
            .QueryOver(() => activity)
            .Where(a.Type.IsIn(types))
            .WithSubquery.WhereProperty(a => a.ResourceId).In(QueryOver.Of<Resource>()
                .Where(k => k.MemberKey == userId)
                .Where(k => k.ResourceKey == activity.ResourceId)
                )
            .Take(take)
            .List();

nHibernate QueryOver Subselect or Join

1 Answers