Many-To-Many association from database

I need assistance with an error that I get when working with many-to-many tables in HQL. Need your input if the tables and the configuration that designer creates are correct.

Tables.

CREATE TABLE [dbo].[T_Nodes]
(
    [Id] INT NOT NULL  IDENTITY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [ParentNodeId] INT NOT NULL, 
    PRIMARY KEY ([Id])
)

CREATE TABLE [dbo].[T_Probes]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [Location] NVARCHAR(50) NOT NULL, 

)

CREATE TABLE [dbo].[T_Nodes_To_T_Probes] (
    [NodeId]  INT NOT NULL,
    [ProbeId] INT NOT NULL,
    CONSTRAINT [PK_T_Nodes_To_T_Probes] PRIMARY KEY CLUSTERED ([NodeId] ASC, [ProbeId] ASC),
    CONSTRAINT [FK_T_Nodes_To_T_Probes_T_Nodes] FOREIGN KEY ([NodeId]) REFERENCES [dbo].[T_Nodes] ([Id]),
    CONSTRAINT [FK_T_Nodes_To_T_Probes_T_Probes] FOREIGN KEY ([ProbeId]) REFERENCES [dbo].[T_Probes] ([Id])
);

//DashboardDBModel.TNodes.cs_____________________________________
using System;
using System.Collections.Generic;
using NHibernate.Cfg;
using NHibernate.Validator.Constraints;

namespace NHDashRepository
{
  [System.CodeDom.Compiler.GeneratedCode("NHibernateModelGenerator", "1.0.0.0")]
  public partial class TNodes
  {
    public virtual int Id { get; set; }
    [NotNull]
    [Length(Max=50)]
    public virtual string Name { get; set; }
    public virtual int ParentNodeId { get; set; }

    private IList<TNodesToTProbes> _tNodesToTProbes = new List<TNodesToTProbes>();

    public virtual IList<TNodesToTProbes> TNodesToTProbes
    {
      get { return _tNodesToTProbes; }
      set { _tNodesToTProbes = value; }
    }

    static partial void CustomizeMappingDocument(System.Xml.Linq.XDocument mappingDocument);

    internal static System.Xml.Linq.XDocument MappingXml
    {
      get
      {
        var mappingDocument = System.Xml.Linq.XDocument.Parse(@"<?xml version='1.0' encoding='utf-8' ?>
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'
                   assembly='" + typeof(TNodes).Assembly.GetName().Name + @"'
                   namespace='NHDashRepository'
                   >
  <class name='TNodes'
         table='`T_Nodes`'
         >
    <id name='Id'
        column='`Id`'
        >
      <generator class='hilo'>
        <param name='table'></param>
        <param name='column'></param>
        <param name='max_lo'>0</param>
      </generator>
    </id>
    <property name='Name'
              column='`Name`'
              />
    <property name='ParentNodeId'
              column='`ParentNodeId`'
              />
    <bag name='TNodesToTProbes'
          inverse='true'
          >
      <key column='`NodeId`' />
      <one-to-many class='TNodesToTProbes' />
    </bag>
  </class>
</hibernate-mapping>");
        CustomizeMappingDocument(mappingDocument);
        return mappingDocument;
      }
    }
  }


}

//DashboardDBModel.TNodesToTProbes.cs___________________________________________________
using System;
using System.Collections.Generic;
using NHibernate.Cfg;
using NHibernate.Validator.Constraints;

namespace NHDashRepository
{
  [System.CodeDom.Compiler.GeneratedCode("NHibernateModelGenerator", "1.0.0.0")]
  public partial class TNodesToTProbes
  {
    public virtual int NodeId { get; set; }
    public virtual int ProbeId { get; set; }

    public virtual TNodes Node { get; set; }

    public virtual TProbes Probe { get; set; }

    public override bool Equals(object obj)
    {
      if (obj == null || obj.GetType() != GetType())
      {
        return false;
      }

      TNodesToTProbes other = (TNodesToTProbes)obj;
      if (other.NodeId != NodeId)
      {
        return false;
      }
      if (other.ProbeId != ProbeId)
      {
        return false;
      }
      return true;
    }

    public override int GetHashCode()
    {
      int hashCode = 0;
      hashCode = 19 * hashCode + NodeId.GetHashCode();
      hashCode = 19 * hashCode + ProbeId.GetHashCode();
      return hashCode;
    }

    static partial void CustomizeMappingDocument(System.Xml.Linq.XDocument mappingDocument);

    internal static System.Xml.Linq.XDocument MappingXml
    {
      get
      {
        var mappingDocument = System.Xml.Linq.XDocument.Parse(@"<?xml version='1.0' encoding='utf-8' ?>
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'
                   assembly='" + typeof(TNodesToTProbes).Assembly.GetName().Name + @"'
                   namespace='NHDashRepository'
                   >
  <class name='TNodesToTProbes'
         table='`T_Nodes_To_T_Probes`'
         >
    <composite-id>
      <key-property name='NodeId'
                    column='`NodeId`'
                    />
      <key-property name='ProbeId'
                    column='`ProbeId`'
                    />
    </composite-id>
    <many-to-one name='Node' class='TNodes' column='`NodeId`' />
    <many-to-one name='Probe' class='TProbes' column='`ProbeId`' />
  </class>
</hibernate-mapping>");
        CustomizeMappingDocument(mappingDocument);
        return mappingDocument;
      }
    }
  }


}

//DashboardDBModel.TProbes.cs__________________________________
using System;
using System.Collections.Generic;
using NHibernate.Cfg;
using NHibernate.Validator.Constraints;

namespace NHDashRepository
{
  [System.CodeDom.Compiler.GeneratedCode("NHibernateModelGenerator", "1.0.0.0")]
  public partial class TProbes
  {
    public virtual int Id { get; set; }
    [NotNull]
    [Length(Max=50)]
    public virtual string Name { get; set; }
    [NotNull]
    [Length(Max=50)]
    public virtual string Location { get; set; }

    private IList<TNodesToTProbes> _tNodesToTProbes = new List<TNodesToTProbes>();

    public virtual IList<TNodesToTProbes> TNodesToTProbes
    {
      get { return _tNodesToTProbes; }
      set { _tNodesToTProbes = value; }
    }

    static partial void CustomizeMappingDocument(System.Xml.Linq.XDocument mappingDocument);

    internal static System.Xml.Linq.XDocument MappingXml
    {
      get
      {
        var mappingDocument = System.Xml.Linq.XDocument.Parse(@"<?xml version='1.0' encoding='utf-8' ?>
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'
                   assembly='" + typeof(TProbes).Assembly.GetName().Name + @"'
                   namespace='NHDashRepository'
                   >
  <class name='TProbes'
         table='`T_Probes`'
         >
    <id name='Id'
        column='`Id`'
        >
      <generator class='hilo'>
        <param name='table'></param>
        <param name='column'></param>
        <param name='max_lo'>0</param>
      </generator>
    </id>
    <property name='Name'
              column='`Name`'
              />
    <property name='Location'
              column='`Location`'
              />
    <bag name='TNodesToTProbes'
          inverse='true'
          >
      <key column='`ProbeId`' />
      <one-to-many class='TNodesToTProbes' />
    </bag>
  </class>
</hibernate-mapping>");
        CustomizeMappingDocument(mappingDocument);
        return mappingDocument;
      }
    }
  }


}


Code
            try
            {
                using (ISession session = DashboardDBNHibernateSessionHelper.OpenSession())
                {

                    string hql = "FROM TProbes p "+
                                    "JOIN p.TNodesToTProbes n " +
                                    "WHERE n.NodeId = :nodeId";

                    IQuery query = session.CreateQuery(hql).SetParameter("nodeId", nodeId);

                    IList<TProbes> probeList = query.List<TProbes>();

                    IEnumerable<Probe> probeCol = probeList.Select(c => new Probe
                    {
                        Id = c.Id,
                        Name = c.Name,
                        Location = c.Location,
                        Data = null
                    });


                    return probeCol.ToList<Probe>();
                }
            }

Trying to achieve in SQL is :

SELECT        T_Probes.Name, T_Probes.Location, T_Probes.Id
FROM            T_Nodes 
INNER JOIN T_Nodes_To_T_Probes ON T_Nodes.Id = T_Nodes_To_T_Probes.NodeId
INNER JOIN T_Probes ON T_Nodes_To_T_Probes.ProbeId = T_Probes.Id
WHERE        (T_Nodes.Id = 2)

Error:

{"Could not execute query[SQL: SQL not available]"} System.Exception {NHibernate.Exceptions.GenericADOException}

{"The value \"System.Object[]\" is not of type \"NHDashRepository.TProbes\" and cannot be used in this generic collection.\r\nParameter name: value"}   System.Exception {System.ArgumentException}

Your help is greatly appreciated!!

Sameer Jaffer

The mapping is fine but you need to use .SetResultTransformer(new DistinctRootEntityResultTransformer()) to filter the resulting entities to a single entity rather than a tuple and then flip the SQL so that Probe is joined from NodesToProbes.

e.g.

string hql = "FROM TNodesToTProbes n "+
                   "JOIN n.Probe " +
                   "WHERE n.NodeId = :nodeId";

IQuery query = session.CreateQuery(hql).SetParameter("nodeId", nodeId).SetResultTransformer(new DistinctRootEntityResultTransformer());

Jeremy