NHibernate: Mapping a join on a joined table

[ramesh]

I want to map 5 table in a single mapping file. Below is my requirement query.

Query:

SELECT TSC.*
FROM DBO.TABLE1 TSA
INNER JOIN DBO.TABLE2 TSC WITH (NOLOCK) ON TSC.SUITEID = TSA.SUITEID AND TSC.COMPANYID = TSA.COMPANYID
INNER JOIN DBO.TABLE3 CO (NOLOCK) ON CO.TEMPLATEID = TSC.CRSOPTTEMPID AND CO.COMPANYID = TSC.COMPANYID
INNER JOIN DBO.TABLE4 UG2 ON UG2.GROUPID = TSA.ITEMID
INNER JOIN DBO.TABLE5 P ON P.USERID = UG2.USERID
WHERE TSA.DELFLAG=0 AND TSC.DELFLAG=0 AND TSA.COMPANYID= 'A1'

Any one pls help on this?

[ramesh]

I found the solution for the above query. But, i have omitted the companyid alone from inner join and where condition.

CLASS1 Mapping File:

<class name="CLASS1" table="TABLE1" where ="DELFLAG=0">
<composite-id>
<key-property name="SUITEID" column="SUITEID" type="int"></key-property>
</composite-id>
<property name="ITEMID" column="ITEMID" type="int"></property>

<join table="TABLE2">
<key>
<column name="SUITEID" sql-type ="int"></column>
</key>
<property name="CRSOPTTEMPID" column="CRSOPTTEMPID" type="int"></property>
<many-to-one name="CRS_OPT" property-ref="TEMPLATEID" column ="CRSOPTTEMPID"></many-to-one>

<bag name="USRGRP2" table="TABLE4">
<key property-ref="ITEMID">
<column name="GROUPID" ></column>
</key>
<many-to-many column="USERID" class="CLASS2" property-ref="USERID"></many-to-many>
</bag>
</join>
</class>

Class File:

public class CLASS1
{
public virtual int SUITEID { get; set; }
public virtual int ITEMID { get; set; }

public virtual int CRSOPTTEMPID { get; set; }
public virtual int DELFLAG { get; set; }

public virtual CLASS3 CRS_OPT { get; set; }
public virtual CLASS4 USRGRP2 { get; set; }

public JoinedTrainingSuites() { }

public override int GetHashCode()
{
return SUITEID;
}
public override bool Equals(object obj)
{
if (this == obj)
{
return true;
}
CLASS1 key = obj as CLASS1;

if (key == null)
{
return false;
}
return true;
}
}