Nhibernate many to many criteria query with subselect

Question

I have a simple example of a blog: a Post table, a Tag table and a Post_Tag_MM lookup table linking the two tables.

I use this hql query in order to fetch all posts, that DONT have some tags:

            var result = session
                            .CreateQuery(@"
                                select p from Post p 
                                join p.Tags t 
                                where (select count(ti) from p.Tags ti where ti.Uid in (:uidList)) = 0
                            ")
                            .SetParameterList("uidList", uidList)
                            .SetResultTransformer(new DistinctRootEntityResultTransformer())
                            .List<Post>();

How can this many-to-many query and the subselect translated into a criteria query?

I dont quite understand the DetachedCriteria API yet and could not get it to return the right resultset.

Thank you very much in advance.

Regards,

Max

Answer 1

Try the below. Assuming uidList is a collection of tag Ids.

var results = S.CreateCriteria(typeof(Post))
               .CreateCriteria("Tags")
               .Add(
                   Expression.Not(
                     Expression.In("Uid", uidList)
                   )
                )
               .List<Post>();

There is probably a more efficient way to do it, although this has worked for me in the past. It may not be syntax perfect, but the general principle should get you moving again, I hope.

Answer 2

You need to use a DetachedCriteria to represent the subselect. Then add that query to a CreateCriteria with a Subqueries restraint.

Answer 3

If I've understood the question correctly, you have a join table containing the PostId, TagId relationship called Post_Tag_MM, and you want all the Posts that do NOT have any of the tags identified by a collection of tag ids. Then, in plain sql you could do:

select * from posts where id not in (select distinct PostId from Post_Tag_MM where TagId in (1,2,3));

Using the DetachedCrietria API, that subquery would look like this, assuming you've mapped the Post_Tag_MM table to a PostTag class:

var subCriteria = DetachedCriteria.For(typeof (PostTag))
    .SetProjection(Projections.Property("PostId"))
    .Add(Restrictions.PropertyIn("TagId", tagIdList));

attach and negate the 'property in' subquery criteria to your main criteria like this

var result = session.CreateCriteria.For(typeof(Post))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .Add(Subqueries.PropertyNotIn("Id", subCriteria))
    .List<Post>();