ActiveRecord多表查询，返回DataTable

一.Hql 返回IList，组合成DataTable

            string hql =" from Item I left join  I.Products P left join  P.FailureModes F "
                        +" where P.ProductName=?";
            Castle.ActiveRecord.Queries.SimpleQuery query =
                new Castle.ActiveRecord.Queries.SimpleQuery(typeof(Item),typeof(IList), hql, "aa");
            IList item = (IList)Castle.ActiveRecord.ActiveRecordBase.ExecuteQuery(query);

        具体要查的字段，要动态传入 
        private DataTable MakeTable(IList fields,IList item)
        {
          
            DataTable table = new DataTable("Data");

            DataColumn column = null;
          
            foreach (QueryField field in fields)
            {
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.Caption = field.DisplayText;
                column.ColumnName = field.DisplayText;
                table.Columns.Add(column);
            }         
          
            DataRow row;
            foreach (object[] ol in item)
            {
                row = table.NewRow();

                int j = 0;
                foreach (object o in ol)
                {
                    row[table.Columns[j]] = o;
                    j++;
                }
                table.Rows.Add(row);
            }
            return table;
        }

           
         

 

二. 通过Sql语句返回DataTable

          string ssql = " select I.ItemName,P.ProductName,F.FailureName "
                       + " from Item I left join Product P on I.ItemId=P.ItemId "
                       + " left join  FailureMode F on P.ProductId=F.ProductId "
                       + " where p.ProductName='aa'";

            Castle.ActiveRecord.Framework.ISessionFactoryHolder sessionHolder =
                       Castle.ActiveRecord.ActiveRecordMediator.GetSessionFactoryHolder();
            NHibernate.ISession session = sessionHolder.CreateSession(typeof(Item));

            try
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandText = ssql;
                IDataReader rdr = command.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(rdr, LoadOption.Upsert);             

                dataGridView1.DataSource = dt;

            }
            finally
            {
                sessionHolder.ReleaseSession(session);
            }        

三。 public static IList<T> GetDataSource<T>(Project curProject)
       {

           try
           {
               Type t = typeof(T);

               if (t == null) return null;

               ParameterExpression param = Expression.Parameter(t, "tt");
               //c.City=="London"
               Expression left = Expression.Property(param,t.GetProperty("Project"));
               Expression right = Expression.Constant(curProject);
               Expression filter = Expression.Equal(left, right);
               Expression pred = Expression.Lambda(filter, param);
               //Where(c=>c.City=="London")
              

               IQueryable<T> query= from tt in ActiveRecordLinq.AsQueryable<T>()                             
                                    select tt;

               Expression expr = Expression.Call(
                                                  typeof(Queryable),
                                                  "Where",
                                                   new Type[] { t },
                                                   Expression.Constant(query),
                                                   pred
                                                  );

               var ls=query.ToList<T>().AsQueryable<T>().Provider.CreateQuery<T>(expr);

               return ls.ToList<T>();                      
            
           }
           catch
           {
              return null;
           }

       }