Possible solution for NHibernate many-to-many criteria

In one of my .NET projects I’m using the NHibernate library for object-relational mapping. I’m mainly using the ICriteria interface to fetch data from the database. Unfortunately I ran into a function that got really complicated; how to query a many-to-many relationship. For example, I have a table containing posts and a table containing tags. The post datamodel contains a set with tags so in my mapping it’s a many-to-many relationship. I want my query to return all posts tagged with one or more specific tags. On this forum I found a solution. I’m not sure if this is the perfect solution, so feel free to suggest a better one.

4 thoughts on “Possible solution for NHibernate many-to-many criteria

  1. Thank you for sharing this.

    Maybe instead of adding a subquery for each tag, you could combine them all into a single subquery by using “Restrictions.And” between the tag id’s. I guess it could improve the performance of the query, but I’m not sure 😉

    What’s the purpose of the SetResultTransformer call?

  2. You can’t alias out the same table twice in the same criteria set. NHibernate (At least up to the 2.1.2 version my projects use) cannot handle this and it will throw an exception when you call the retrieval method(List, Unique… etc). He has the “Post” entity used twice in this scenario.

  3. @Daniel: Thanks for your comment. I haven’t used this function for a while so I’m not aware of the exception. Do you have a better idea?

  4. @Yann, the SetResultTransformer removes duplicate entries from the result. Keep in mind that will be done client-side and should not be mistaken with the DISTINCT on SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *