NHinernate查询总结

多对多关系下的查询(通过catalog表中的id属性来查找PackageDefination表中所关联的记录) packageForPackageDefinition为中

介表，查询过程为首先使用传入的变量catalogId对catalog表进行查询，得到catalog表的Id，用该id与中介表packageForPackageDefinition

进行匹配，得到中介表所映射的一组Id，再用该id对PackageDefinition进行查询得到所要的数据集合。

 public IList<PackageDefinition> GetPackageDefinitions(long catalogId)        

{            

ICriteria query = CurrentSession.CreateCriteria(typeof(PackageDefinition));            

DetachedCriteria packagesForPackageDefinition = DetachedCriteria.For(typeof(PackageDefinition)).SetProjection(Projections.Distinct(Projections.Property("Id")))                

 .CreateCriteria("Catalogs","catalog")                

 .Add(Expression.Eq("catalog.Id", catalogId));            

query.Add(Subqueries.PropertyIn("Id", packagesForPackageDefinition));        

   return query.List<PackageDefinition>();    

单表查询，利用传入的styleCustomId与表OptimizedVariedStyle的customid进行匹配，方法返回

不重复的数据集合。

[Transaction(ReadOnly = true)]      

 public OptimizedVariedStyle GetStyleBy(string styleCustomId)      

 { 

  NHibernate.ICriteria query = CurrentSession.CreateCriteria<OptimizedVariedStyle>();            query.Add(Restrictions.Eq("CustomId", styleCustomId));            

return query.UniqueResult<OptimizedVariedStyle>();      

 }

分页查询（写sql语句）

offset是页数，limit是页的size

        [Transaction(ReadOnly = true)]
        public IList<Plain> GetPlainByFilter(int limit, long offset, string filter)
        {
            string whereClause = " WHERE Active=1 ";
            string queryStr = "";

            if (!String.IsNullOrEmpty(filter)) {
                whereClause += " AND " + filter;
            }
            if (offset == 1)
            {
                queryStr = "SELECT TOP " + limit + " * FROM " + VIEW_NAME + whereClause + " order by isJit ASC,Weight asc";
            }
            else
            {
                queryStr = "SELECT TOP " + limit + " * FROM " + VIEW_NAME + " where id NOT IN(SELECT id from (select top "
                            + (offset - 1) * limit + " id from " + VIEW_NAME + whereClause
                            + " order by  isJit asc,[Weight] asc, Id asc) as T) "
                            + (String.IsNullOrEmpty(filter) ? " " : " AND " + filter)
                            + " and Active=1 order by isJit asc,[Weight] asc, Id asc";
            }
            ISQLQuery query = CurrentSession.CreateSQLQuery(queryStr).AddEntity(typeof(Plain));
            return query.List<Plain>();
        }

分页查询（NHibernate方法）

      [Transaction(ReadOnly=true)]
        public IList<ViewProductReview> GetPagedReviews(int pageNo, int pageSize, string styleCustomId)
        {
            NHibernate.ICriteria query = CurrentSession.CreateCriteria<ViewProductReview>();
            query.Add(Restrictions.Eq("StyleCustomId", styleCustomId));
            //query.Add(Restrictions.Eq("IsApproved", true));
            query.AddOrder(NHibernate.Criterion.Order.Desc("CreateDate"));
            query.SetMaxResults(pageSize).SetFirstResult((pageNo-1) * pageSize);
            return query.List<ViewProductReview>();
        }