Results 1 to 2 of 2

Thread: NHibernate: Mapping a join on a joined table

  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Default NHibernate: Mapping a join on a joined table

    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?

  2. #2
    Join Date
    Mar 2012
    Posts
    3

    Default

    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;
    }
    }

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •