How to do Inner Join in NHibernate

I had spent a lot of extensive time trying to find how to do a fairly simple inner join from two tables in NHibernate, and because the documentation was so poor for NHibernate, I had made so many attempts, and almost gave up.

Previous attempts: – 

public static IList<Subscription> GetSubscriberDetails(int subscriberID)
{
ICriteria criteria = SessionFactoryContext.GetCurrentSession().CreateCriteria(typeof(Subscription));
criteria.CreateAlias(“SubscriberDetails”, “s”);
criteria.Add(Expression.Eq(“SubscriberID”, 1));
criteria.Add(Expression.Eq(“s.SubscriberID”, 1));

//criteria.CreateCriteria(“SubscriptionPackage”, JoinType.InnerJoin);
criteria.Add(Expression.Eq(“SubscriberID”, subscriberID));
return (IList<Subscription>)criteria.List<Subscription>();

} gave only subscription for Subscriber ID = 1, which is hardcoded!! 😦

public static IList<Subscription> GetSubscriberDetails(int subscriberID)
{
ICriteria criteria = SessionFactoryContext.GetCurrentSession().CreateCriteria(typeof(Subscription));
criteria.CreateAlias(“SubscriberDetails”, “s”);
criteria.Add(Expression.Eq(“SubscriberID”, 1));
criteria.Add(Expression.Eq(“s.SubscriberID”, 1));
return (IList<Subscription>)criteria.List<Subscription>();

} gave an error along the lines of the query being used more than once!!

public static IList<Subscription> GetSubscriberDetails(int subscriberID)
{
ICriteria criteria = SessionFactoryContext.GetCurrentSession().CreateCriteria(typeof(Subscription));
criteria.CreateAlias(“GetSubscriberDetails”, “s”);
criteria.Add(Expression.Eq(“SubscriberID”, “s.SubscriberID”));
criteria.CreateCriteria(“SubscriberDetails”, JoinType.InnerJoin);
return (IList<Subscription>)criteria.List<SubscriberDetails>();

} gave an error 

The value “DatesDB.Subscription.SubscriberDetails” is not of type “DatesDB.Subscription.Subscription” and cannot be used in this generic collection.

Looking at lots of blog posts in Google, people had used detached query, but I hadn’t got this working at all with the following: – 

var query = DetachedCriteria.For<Subscription>()

                .CreateCriteria("SubscriberID", JoinType.InnerJoin)

                    .AddOrder(Order.Asc("SubscriberID"));

 

Instead of using the variable query, I had placed rather than a detached criteria query, an incorporation of a getCurrentSession query like so: – 

 

        public static IList<Subscription> GetSubscriptionFromSelectedSubscriber(int subscriberID)

        {

            ICriteria criteria = SessionFactoryContext.GetCurrentSession().CreateCriteria(typeof(Subscription));

            criteria.CreateCriteria(“SubscriberDetails”, JoinType.InnerJoin);

            criteria.Add(Expression.Eq(“SubscriberID”, subscriberID));

            return (IList<Subscription>)criteria.List<Subscription>();

        }

 

It’s that simple and it works!  This code needs to be placed in the Subscription class.

 

So best practice is not to use the bag for storing IList of data in SubscriberDetails but to have the data stored in the many to one class which is Subscriptions, so you obtain the necessary data and not unnecessary data in the one to many class, if you know what I mean.

You need to have a reference in this case a object reference with getter and setter for SubscriberDetails object in Subscription class, otherwise you get the error: –

 

Could not find a getter for property ‘Subscriber’ in class ‘DatesDB.Subscription.Subscription’

 

One final note is that you must create a mapping in the XML file for Subscription .hbm.xml like so: – 

 

Subscription 

 

<?xml version=”1.0″ encoding=”utf-8″ ?>

<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2″

                   namespace=”DatesDB.Subscription”

                   assembly=”DatesDB”>

 

  <class name=”Subscription” table=”Subscription”>

    <id name=”SubscriptionID” column=”SubscriptionID” type=”int”>

      <generator></generator>

    </id>

    <property name=”SubscriberID”  type=”int” />

    <property name=”ExpiryDateTime”  type=”datetime” />

    <property name=”Info”  type=”string” />

    <many-to-one name=”SubscriberID”

                 column=”SubscriberID” lazy=”false” cascade=”none” />

  </class>

</hibernate-mapping>

 

See the last line of the xml file in terms of property mappings, my one had been many-to-one relationship, but it may be a one-to-one relationship that denotes that one Subscriber can only have one Subscription, and SubscriberID is the association in the join.