C# – Nhibernate many to many criteria query with subselect

cnhibernate

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

Best Answer

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