Nhibernate with NativeSQL

My sql is:

SELECT Branch.BranchName, Department.DepartmentName, 
       Designation.DesignationName,   EmpType.EmpType, Shift.ShiftName,
       Employee.CardNo,Employee.EmployeeStatus, Employee.JoiningDate, Employee.EmpName
FROM   Branch 
          INNER JOIN Department ON Branch.BranchId = Department.BranchId 
          INNER JOIN Designation ON Branch.BranchId = Designation.BranchId 
             AND Department.DepartmentId = Designation.DepartmentId 
          INNER JOIN Employee ON Branch.BranchId = Employee.BranchId 
             AND Department.DepartmentId = Employee.DepartmentId 
             AND Designation.DesignationId = Employee.DesignationId 
          INNER JOIN EmpType ON Employee.EmpTypeId = EmpType.EmpTypeId 
   开发者_如何学Go       INNER JOIN Shift ON Employee.ShiftId = Shift.ShiftId

Data Access Code is:

IQuery query = Session.GetISession().CreateSQLQuery(sql).AddEntity(typeof(Branch));
return query.List<Branch>();

Stack Trace:

at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names,   SessionImplementor session, Object owner)
at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)

at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)

There are few DomainObjects above (eg Branch, Department, EmpType, Designation and Employee). but i would like to return a list type of Branch object.

Please anyone suggest me what would be the solution.

Thanks Rusho

if you want to use CreateSQLQuery over a native sql statement anyway you have to tell Nhibernate what entity do you want to select becouse Nhibernate is working in terms of entities or objects then shoud write the sql statement like this:

SELECT {Entity.*}, Department.DepartmentName, 
   Designation.DesignationName,   EmpType.EmpType, Shift.ShiftName,
   Employee.CardNo,Employee.EmployeeStatus, Employee.JoiningDate, Employee.EmpName
FROM Branch {Entity}
      INNER JOIN Department ON {Entity.BranchId} = Department.BranchId 
      INNER JOIN Designation ON {Entity.BranchId} = Designation.BranchId 
         AND Department.DepartmentId = Designation.DepartmentId 
      INNER JOIN Employee ON Branch.BranchId = Employee.BranchId 
         AND Department.DepartmentId = Employee.DepartmentId 
         AND Designation.DesignationId = Employee.DesignationId 
      INNER JOIN EmpType ON Employee.EmpTypeId = EmpType.EmpTypeId 
      INNER JOIN Shift ON Employee.ShiftId = Shift.ShiftId

then you need to tell query the name of this entity like this

CreateSQLQuery(sql).AddEntity("Entity", typeof(Branch))

but using a native sql query like this is not a good idea ,becouse here comes the beauty of Nhibernate, this what we should use NHibernate for, to eleminate these massive sql statements and to express our tables in some OOP flavor.So for a basic query like this you better use HQL Query Language or QueryOver.Look at your sql statement how much lines you wrote but with Nhibernate you could do that with a few words, you only use native sql statement for the queries you couldn't achieve with NHibernate.