NHibernate 多表查询解决

在项目中应用NHibernate架构时,会经常遇到多表查询,使用select new ObjectEntity这种语法,根据查询的内容自己构建相应的实体类.
这是一个小型家庭办公中的实例。有员工表Employee，姓名表Project，工作记录表EmpWorkRecord。一个员工有多条工作记录，工作记录与项目有关。
Employee实体类与映射文件如下。
    public class Employee
    {
        private IList emprecord;

        public Employee()
        {
            emprecord = new ArrayList();
        }
        private int id;

        public virtual int Id
        {
            get { return id; }
            set { id = value; }
        }
        /// <summary>
        /// 员工姓名
        /// </summary>
        private  string empname = "";

        public virtual string EmpName
        {
            get { return empname; }
            set { empname = value; }
        }
        /// <summary>
        /// 性别
        /// </summary>
        private string sex = "";

        public virtual string Sex
        {
            get { return sex; }
            set { sex = value; }
        }
        /// <summary>
        /// 地址
        /// </summary>
        private string address = "";

        public virtual string Address
        {
            get { return address; }
            set { address = value; }
        }
        /// <summary>
        /// 家庭电话
        /// </summary>
        private string housephone = "";

        public virtual string Housephone
        {
            get { return housephone; }
            set { housephone = value; }
        }
        /// <summary>
        /// 手机号码
        /// </summary>
        private string telephone = "";

        public virtual string Telephone
        {
            get { return telephone; }
            set { telephone = value; }
        }
        /// <summary>
        /// 电子邮件
        /// </summary>
        private string email = "";
        public virtual string Email
        {
            get { return email; }
            set { email = value; }
        }

        public virtual IList EmpRecord
        {
            get { return emprecord; }
            set { emprecord = value; }
        }
        private  IList<EmpGetMonyRecord> empgetmonyrecord;

        public virtual IList<EmpGetMonyRecord> EmpGetMonyRecord
        {
            get { return empgetmonyrecord; }
            set { empgetmonyrecord = value; }
        }

    }
Employee.hbm.xml文件：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Finance.Model.Employee,Finance.Model" table="Employee">
    <id name="Id" type="Int32">
      <generator class="identity"/>
    </id>
    <property name="EmpName" type="String" length="16"  />
    <property name="Address" type="String" length="100"/>
    <property name="Sex" type="String" length="2"/>
    <property name="Housephone" type="String" length="13"/>
    <property name="Telephone" type="String" length="12"/>
    <property name="Email" type="String" length="50" />
    <bag name="EmpRecord" table="EmpWorkRecord"  >
      <key column="EmpId"></key>
      <one-to-many class="Finance.Model.EmpWorkRecord,Finance.Model"/>
    </bag>
    <bag name="EmpGetMonyRecord" table="EmpGetMonyRecord"   >
      <key column="EmpId"></key>
      <one-to-many class="Finance.Model.EmpGetMonyRecord,Finance.Model"/>
    </bag>
  </class>
</hibernate-mapping>
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
工作记录实体类EmpWorkRecord以及hbm.xml文件如下：
   public  class EmpWorkRecord
    {

        private Employee emp;
        private int id;
        private int empid;
        private decimal wage;
        private DateTime workdate;
        private string worktype;
        private Project project;

        public EmpWorkRecord()
        {
            emp = new Employee();
            project = new Project();
        }   
       
        public virtual int Id
        {
            get { return id; }
            set { id = value; }
        }
       

        public virtual int EmpId
        {
            get { return empid; }
            set { empid = value; }
        }
       

        public virtual decimal Wage
        {
            get { return wage; }
            set { wage = value; }
        }
       

        public virtual DateTime WorkDate
        {
            get { return workdate; }
            set { workdate = value; }
        }

        public virtual string WorkType
        {
            get { return worktype; }
            set { worktype = value; }
        }
        public virtual Project Project
        {
            get { return project; }
            set { project = value; }
        }
        public virtual Employee Emp
        {
            get { return emp; }
            set { emp = value; }
        }
    }
－－－EmpWorkRecord.hbm.xml文件－－－－－－－－－－－－－－－－－－－－－－
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <import class="Finance.Model.EmpWorkJoinEmpProject,Finance.Model"/>//引入构造实体类EmpWorkJoinEmpProject
  <class name="Finance.Model.EmpWorkRecord,Finance.Model" table="EmpWorkRecord" >
    <id name="Id" type="Int32">
      <generator class="identity"/>
    </id>
    <property name="Wage"  type="Decimal"  />
    <property name="WorkDate" type="DateTime" />
    <property name="WorkType" type="String" length="10" ></property>
    <many-to-one name="Emp" column="EmpId" class="Finance.Model.Employee,Finance.Model" lazy="false"></many-to-one>
    <many-to-one name="Project" column="ProjectId" class="Finance.Model.Project,Finance.Model" lazy="false"></many-to-one>
  </class>
</hibernate-mapping>
现在要获取员工的工作记录信息以表的形式显示出来或者绑定GridView上：员工名（EmpName），工作日期（WorkDate），工作类型（WorkType），姓名名称（ProjectName）。如果就做关联查询的结果并不能显示出表的形式，必须把查询出的数据进行处理。
（1）一种方法是把查询的结果循环遍历构造出表的形式再绑定到gridview。
       //获取数据
       public IList GetPage(int pageIndex, int pageSize, string where, string orderBy)
        { 
            string hql = "select  m.Name,m.Type,m.Price,cost.Num,cost.PurchaseTime,cost.PurchasePeople,cost.Id from Material m             join m.MaterialCost cost";
            return control.GetManyTablePage(hql, where, orderBy, pageIndex, pageSize, "Finance.Model.MaterialCost");
        }
       //绑定GridView
        private void BindGv()
        {
            string where = null;
            if (this.txtMaterialName.Text.Trim().Length > 0)
            {
                where = "Name='" + this.txtMaterialName.Text.Trim() + "'";
            }
            int pageIndex = Finance.StringHandling.Integer.GetInteger(Request.QueryString["Page"], 1);
            IList list = materalcostbll.GetPage(pageIndex, 15, where, "cost.Id desc"); 
            List<MaterialJoinCost> materialjoincost = new List<MaterialJoinCost>();
            MaterialJoinCost m = null;
            IEnumerator e = list.GetEnumerator();
            while (e.MoveNext())
            {   //给查询出来的每行赋值
                m = new MaterialJoinCost();
                object[] obj = (object[])e.Current;
                m.Name = obj[0].ToString();
                m.Price = decimal.Parse(obj[2].ToString());
                m.Num = Convert.ToInt32(obj[3].ToString());
                m.PurchaseTime = Convert.ToDateTime(obj[4].ToString());
                m.PurchasePeople = obj[5].ToString();
                m.Id = int.Parse(obj[6].ToString());
                materialjoincost.Add(m);
            }

            GridView1.DataSource = materialjoincost; 
            GridView1.DataBind();
            pageset.Text = materalcostbll.GetPageSet(pageIndex, 15, where, "MaterialCost.aspx?Page=$", 2);
        }

（2）运用NHIbernate查询语法：select new EmpWorkJoinEmpProject(...)。
 这里主要介绍方法二，这种方法也是比较常用的方式。
 首先构造EmpWorkJoinEmpProject实体。在EmpWorkRecord.hbm.xml中加上
<import class="Finance.Model.EmpWorkJoinEmpProject,Finance.Model"/>语句，为了是构造实体类时找到该类。
 public class EmpWorkJoinEmpProject
    {
        private int id;
        private int empId;
        private string empName;

       
        private decimal wage;

        private DateTime workDate;

       
        private string pname;
        private string worktype;

        public string WorkType
        {
            get { return worktype; }
            set { worktype = value; }
        }
       
        public EmpWorkJoinEmpProject(int id, int empid, string empName, decimal wage,string worktype, DateTime workDate, string pname)
        {
            this.id = id;
            this.empId = empid;
            this.empName = empName;
            this.wage = wage;
            this.workDate = workDate;
            this.pname = pname;
            this.worktype = worktype;
        }
        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        public int EmpId
        {
            get { return empId; }
            set { empId = value; }
        }
        public string EmpName
        {
            get { return empName; }
            set { empName = value; }
        }

        public decimal Wage
        {
            get { return wage; }
            set { wage = value; }
        }
        public DateTime WorkDate
        {
            get { return workDate; }
            set { workDate = value; }
        }
        public string PName
        {
            get { return pname; }
            set { pname = value; }
        }

    }
       1)获取数据DAL
       public IList GetDataByQuery(string where)
       {
            string hql =" select new EmpWorkJoinEmpProject(work.Id, emp.Id,emp.EmpName,work.Wage,work.WorkType,work.WorkDate,p.ProjectName) from EmpWorkRecord work join work.Emp emp,work.Project p";
            if (!string.IsNullOrEmpty(where) && where != "")
            {
                hql+= " where " + where;
            }
            return control.GetDataByQuery(hql);
        
        }
       2)EmpWorkRecordBLL代码：
       public IList GetDataByQuery(string where)
        {
            return dal.GetDataByQuery(where);
        }

      3)绑定GridView
       private void BindGv()
        {
            string where = null;
            if (this.txtGemoneyPerson.Text.Trim().Length > 0)
            {
                where = "EmpName='" + this.txtGemoneyPerson.Text.Trim() + "'";
            }
            int pageIndex = Finance.StringHandling.Integer.GetInteger(Request.QueryString["Page"], 1);
            IList list = workbll.GetPage(pageIndex, 15, where, "work.Id desc"); 
           
            GridView1.DataSource = list; 
            GridView1.DataBind();
            pageset.Text = workbll.GetPageSet(pageIndex, 15, where, "EmpWorkRecordInfo.aspx?Page=$", 2);  
          
        }