fetching data from multiple tables using nhibernate

**Khutija Tayyaba** · Dec 28 '10, 08:45 AM

Hi,
I have 3 tables in mysql
**User**
---------------------
`id` (auto increment)(PK)
`user_name`
`password`
`first_name`
`last_name`
`created_at`
`modified_at`
`email`

User_Role
-----------------------
`user_id` (PK)
`role_id` (PK)

Role
----------------------
`id`(PK)
`name`

The Mapping file User.hbm.xml

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="WorkOrder.Model"
                   namespace="WorkOrder.Model">
  <class name="User" table="user">
    <id name="id">
      <generator class="identity"/>
    </id>
    <property name="UserName"  column="user_name" type="String"/>
    <property name="Password"  column="password" type="String"/>
    <property name="FirstName"  column="first_name" type="String"/>
    <property name="LastName"  column="last_name" type="String"/>
    <property name="CreatedAt"  column="created_at" type="DateTime"/>
    <property name="ModifiedAt"  column="modified_at" type="DateTime"/>
    <property name="Email"  column="email" type="String"/>
  </class>
</hibernate-mapping>
 
The Mapping file User_Role.hbm.xml
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                           assembly="WorkOrder.Model"
                           namespace="WorkOrder.Model">
  <class name="User_Role" table="user_role">
    <composite-id>
      <key-property  name="UserId"  column="id" type="Integer"></key-property>
      <key-property  name="RoleId"  column="role_id" type="Integer"></key-property>
    </composite-id>    
    <many-to-one class="User" name="User_Id" column="User_Id"  cascade="all"> </many-to-one>
    <many-to-one class="Role" name="Role_Id" column="Role_Id"  cascade="all"> </many-to-one>
  </class>
</hibernate-mapping>

The Mapping file Role.hbm.xml
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="WorkOrder.Model"
                   namespace="WorkOrder.Model">

  <class name="Role" table="role">

    <id name="RoleId" column="id">
      <generator class="identity"/>
    </id>
    <property name="Name" column="name"  length="64"/>

  </class>
</hibernate-mapping>

I want to fetch the information from these tables with the query

Code:

SELECT u.*,r.name 
FROM user u
JOIN user_role ur ON UR.user_id=u.id
JOIN role r on UR.role_id=r.id

I tried with this code

Code:

ICriteria Criteria = session.CreateCriteria(typeof(User));
                Criteria.CreateAlias("User_Role", "User_Role");
                Criteria.CreateAlias("User_Role.Role", "UsrRole");
                Criteria.Add(Expression.Eq("id", Uid));

but its showing error
can anyone please help?