使用NativeSQL的Nhibernate

Question

我的sql是：

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 
          INNER JOIN Shift ON Employee.ShiftId = Shift.ShiftId

数据访问代码为：

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

堆栈跟踪：

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)

上面的DomainObjects很少(例如分支机构、部门、EmpType、指定和员工)。但是我想返回一个Branch对象的列表类型。

请任何人给我建议什么是解决方案。

谢谢Rusho

Answer 1

如果你想使用CreateSQLQuery而不是原生sql语句，你必须告诉Nhibernate你想选择哪个实体，因为Nhibernate在实体或对象方面工作，然后应该像这样编写sql语句：

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

然后，您需要像这样告诉query这个实体的名称

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

但是使用像这样的原生sql查询并不是一个好主意，因为这里有Nhibernate的美，这就是我们应该使用NHibernate的原因，为了消除这些庞大的sql语句，并在一些OOP flavor.So中表达我们的表对于这样的基本查询，你最好在sql语句中使用HQL查询语言或QueryOver.Look，不管你写了多少行，但是使用NHibernate你可以用几个单词就能做到，你只使用原生sql语句来处理你不能用Nhibernate实现的查询。