How do I get NHibernate to return data on composite key table?

Question

I have a junction table in my database. I need to read the id values and use them in my program. I cannot get NHibernate to return any records in the database. It appears as though my mapping file "compiles", but no results are returned.

LinkTable

• int id_ObjectA (foreign key)
• int id_ObjectB (foreign key)

LinkDAO Class:

public class LinkDAO
{
    public virtual ObjectADAO ObjectA { get; set; }
    public virtual ObjectBDAO ObjectB { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
        {
            return false;
        }
        var t = obj as LinkDAO;
        if (t == null)
        {
            return false;
        }
        return (t.ObjectA == ObjectA && t.ObjectB == ObjectB);
    }

    public override int GetHashCode()
    {
        return (ObjectA.Name + "|" + ObjectB.Name).GetHashCode();
    }
}

Mapping File 'LinkDAO.hbm.xml':

<class name="LinkDAO" table="LinkTable" lazy="false">
    <composite-id>
      <key-property name="ObjectA" column="id_ObjectA" />
      <key-property name="ObjectB" column="id_ObjectB" />
    </composite-id>
</class>

I'm querying like this:

IList<LinkDAO> links  =
        NHibernateContext.Current().Session.QueryOver<LinkDAO>().List();
//Empty list is returned, but I can check the DB and see records 

Question: How do I read the two columns as int values when the table is comprised of only a composite key? (Having references to the actual object would be useful, but the int ids are sufficient)

Answer 1

I made the class LinkDAO to be Serializable .

Not sure if you intend ObjectADAO and ObjectBDAO and to be like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication3.Models
{
    [Serializable]
    public class ObjectADAO
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    [Serializable]
    public class ObjectBDAO
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }
}

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="ConsoleApplication3"
                   namespace="ConsoleApplication3.Models">

  <class name="ObjectADAO">
    <id name="Id" type="Int32" generator="native"/>
    <property name="Name" />
  </class>

</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="ConsoleApplication3"
                   namespace="ConsoleApplication3.Models">

  <class name="ObjectBDAO">
    <id name="Id" type="Int32" generator="native"/>
    <property name="Name" />
  </class>

</hibernate-mapping>

You seems to have some typos in your LinkDAO.hbm.xml . My version is as follow:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="ConsoleApplication3"
                   namespace="ConsoleApplication3.Models">

  <class name="LinkDAO" table="LinkTable" lazy="false">
    <composite-id>
      <key-many-to-one name="ObjectA" class="ObjectADAO" column="id_ObjectA"/>
      <key-many-to-one name="ObjectB" class="ObjectBDAO" column="id_ObjectB"/>
    </composite-id>
  </class>
</hibernate-mapping>

The Test will will print correctly :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ConsoleApplication3.Models;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;


namespace ConsoleApplication3
{
    class Program
    {

        static void Main(string[] args)
        {
            var cfg = new Configuration();
            cfg.Configure();
            cfg.AddAssembly(typeof(LinkDAO).Assembly);

            new SchemaExport(cfg).Execute(script: true, export: true, justDrop: true);
            new SchemaExport(cfg).Execute(script:true, export:true, justDrop:false);

            var objAs = new ObjectADAO[] { new ObjectADAO { Name = "A1" }, new ObjectADAO { Name = "A2" } };
            var objBs = new ObjectBDAO[] { new ObjectBDAO { Name = "B1" }, new ObjectBDAO { Name = "B2" } };

            using (var _sessionFactory = cfg.BuildSessionFactory())
            {
                using (var session = _sessionFactory.OpenSession())
                {
                    using (var tran = session.BeginTransaction())
                    {
                        objAs.ToList().ForEach(x => session.Save(x));
                        objBs.ToList().ForEach(x => session.Save(x));
                        tran.Commit();
                    }

                    using (var tran = session.BeginTransaction())
                    {
                        session.Save(new LinkDAO { ObjectA = objAs[0], ObjectB = objBs[1] });
                        session.Save(new LinkDAO { ObjectA = objAs[1], ObjectB = objBs[0] });
                        tran.Commit();
                    }

                IList<LinkDAO> links = session.QueryOver<LinkDAO>().List();

                links.All(lk => { Console.WriteLine("{0} {1}", lk.ObjectA.Name, lk.ObjectB.Name); return true; });

                var ids = session.QueryOver<LinkDAO>().Select(x => x.ObjectA.Id, x => x.ObjectB.Id ).List<object[]>();

                ids.All(ary => { Console.WriteLine("{0} {1}", ary[0], ary[1]); return true; });
                }
            }
        }
    }
}

At the end of the test run you will see :

NHibernate: SELECT this_.id_ObjectA as id1_2_0_, this_.id_ObjectB as id2_2_0_ FROM LinkTable this_
A1 B2
A2 B1
NHibernate: SELECT this_.id_ObjectA as y0_, this_.id_ObjectB as y1_ FROM LinkTable this_
1 2
2 1