Querying many-to-many relations in NHibernate

I’ve ran into a querying scenario with NHibernate that was much less obvious to solve with the NHibernate query API than it would have been with SQL – for me at least.

In my model I’ve had a simple many-to-many relation: Entries that had multiple Categories each and Categories that belonged to multiple Entries as well.

Categories and Entries: an Entity-Relationship Diagram

Categories and Entries: a many-to-many relationship

In the underlying SQL model the many-to-many relationship was implemented via a relationship table:

Many-to-many relationship table structure

Categories and Entries table structure

Now I wanted to find all entites within a given category. Pretty simple, right? In SQL it would have been a simple join:

SELECT Entry.* FROM Entry JOIN Category_rel_Entry ON Category_rel_Entry.Entry_ID = Entry.ID WHERE Category_rel_Entry.Category_ID = @CategoryId

In Nhibernate this query is a bit more tricky, let me share how it can be done.

Note: this post is also a good example of how to define a many to many relationship schema in NHibernate.


My NHibernate model looked like the following:
The Category entity:

 

The Enity entry:

 

To do query all entites of a given category, the Restrictions class has to be used for the categories property of the Entity class. For me it took a while to figure out this solution so here is the code:

Category category; // The category of which its entities wanted to  be retreived
IList nodes = NHHttpModule.CurrentSession.CreateCriteria(typeof(Entity))
                            .CreateCriteria("categories")
                           .Add(NHibernate.Criterion.Restrictions.IdEq(category.ID))
                           .List();

In the end the NHibernate code seems cleaner than the SQL one regarding semantics. However figuring out the syntax of these not so everyday queries is not so self explanatory.

Update (2009. 04. 09.) to have the example working you need to code the the NHttpModule class as well. The code is as follows:

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using NHibernate;
 
namespace NHibernateApp
{
    /// 
    /// NHibernateHttpModule
    /// 
    public class NHHttpModule : IHttpModule
    {
        public const string KEY = "_TheSession_";
        private static ISession _session;
 
        private void context_BeginRequest(object sender, EventArgs e)
        {
            HttpApplication application = (HttpApplication)sender;
            HttpContext context = application.Context;
            context.Items[KEY] = NHSessionHelper.OpenSession();
        }
 
        public static ISession CurrentSession
        {
            get
            {
                if (HttpContext.Current == null)
                {
                    if (_session != null && _session.IsOpen)
                        return _session;
 
                    _session = NHSessionHelper.OpenSession();
                    return _session;
                }
                else
                {
                    HttpContext currentContext = HttpContext.Current;
                    ISession session = currentContext.Items[KEY] as ISession;
                    if (session == null || !session.IsOpen)
                    {
                        session = NHSessionHelper.OpenSession();
                        currentContext.Items[KEY] = session;
                    }
                    return session;
                }
            }
        }
 
        private void context_EndRequest(object sender, EventArgs e)
        {
            HttpApplication application = (HttpApplication)sender;
            HttpContext context = application.Context;
 
            ISession session = context.Items[KEY] as ISession;
            if (session != null)
            {
                try
                {
                    session.Flush();
                    session.Close();
                }
                catch { }
            }
            context.Items[KEY] = null;
        }
 
        public void Init(System.Web.HttpApplication app)
        {
        }
 
        public void Dispose()
        {
        }
    }
 
}

And you will also need to create the NSessionHelper class as well. In this class replace MyNHibernateAppAssembly with the name of your assembly that contains NHibernate definitions.

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using NHibernate.Cfg;
 
namespace NHibernateApp
{
 
    /// 
    /// Summary description for SessionHelper
    /// 
    public class NHSessionHelper
    {
        private static ISessionFactory _sessionFactory = null;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    Configuration config = new Configuration();
                    config.AddAssembly("VizsgTervNHibernate");
                    _sessionFactory = config.BuildSessionFactory();
                    if (_sessionFactory == null)
                        throw new InvalidOperationException("Could not build SessionFactory");
                }
                return _sessionFactory;
            }
 
        }
 
        public static ISession OpenSession()
        {
            ISession session;
            session = SessionFactory.OpenSession();
            if (session == null)
                throw new InvalidOperationException("Cannot open session");
 
            return session;
        }
 
    }
 
}
5 Responses to Querying many-to-many relations in NHibernate
  1. Thommie

    i have the same problem i want to display products that belongs to a user, where i have a user table and a product table and there between user_products table that hols the id of the user and the products. But i cant figure out how to display the products that belongs to the current logged in user, i have mappings and everything similar to yours, but how do i write the code to bind this?

  2. Gergely Orosz

    You have to create an ISession session which you can query. In my example (even though I did not note this) the NHttpModule is a class I developed and the CurrentSession static variable is a singleton that instances an ISession. I have updated the sample with this code as well.

    However if you are new to NHibernate I would suggest to go through this tutorial: http://www.hibernate.org/362.html

  3. thommie

    thank you, will check this in next week, left the comp at home..

  4. Yaya Rabiu David

    have u considered drilling down straight using Expression Class? see the code below. I think it is more straight forward.

    Category category; // The category of which its entities wanted to be retreived
    IList nodes = NHHttpModule.CurrentSession.CreateCriteria(typeof(Entity))
    .CreateCriteria(“categories”)
    .Add(Expression.Eq(“ID”,category.ID))
    .List();

  5. Quang

    Hi Greg,

    Thanks for useful example. How to make mapping file based on your example?

    Thanks