Query By Criteria的限制條件寫法

NHibernate（1） HQL 條件查詢(QBC) - 條件限制

QBC (Query By Criteria) 主要有Criteria,Criterion,Oder,Restrictions，Expresss類組成

比較運算符

   HQL運算符                   QBC運算符                      含義

      =                     Restrictions.eq()                  等于

      <>                   Restrictions.not(Exprission.eq())  不等于

      >                     Restrictions.gt()                  大于

      >=                   Restrictions.ge()                  大于等于

      <                     Restrictions.lt()                  小于

      <=                   Restrictions.le()                  小于等于

      is null             Restrictions.isnull()              等于空值

      is not null      Restrictions.isNotNull()           非空值

      like                 Restrictions.like()                字元串模式比對

      and                Restrictions.and()                 邏輯與

      and                Restrictions.conjunction()         邏輯與

      or                   Restrictions.or()                  邏輯或

      or                   Restrictions.disjunction()         邏輯或

      not                  Restrictions.not()                 邏輯非

      in(清單)          Restrictions.in()                  等于清單中的某一個值

      ont in(清單)         Restrictions.not(Restrictions.in())不等于清單中任意一個值

      between x and y      Restrictions.between()             閉區間xy中的任意值

      not between x and y  Restrictions.not(Restrictions..between()) 小于值X或者大于值y

1、簡單查詢 

//+收貨單

ICriteria iReceivingCriteria = session.CreateCriteria(typeof(IEIReceivingContent));

iReceivingCriteria.Add(Expression.Eq("InvoiceStatus", 3));

 iStockCriteria.Add(Expression.Like("OrgId", criteria.OrgId));

2、時間段查詢

 　　　　iReceivingCriteria.Add(Restrictions.Between("BusinessDate", criteria.BeginDate, criteria.EndDate));

3、HQL 使用 多條件查詢

　　3.1 同一個輸入數值 比對兩個字段

            list = DeliveryDao.CurrentSession.CreateCriteria(typeof(Delivery))

                .Add

                 (

                   NHibernate.Criterion.Restrictions.Or

                    (

                        NHibernate.Criterion.Restrictions.Like("Id", "%" + idOrNo + "%"),

                        NHibernate.Criterion.Restrictions.Like("DeliveryNbr", "%" + idOrNo + "%")

                    )

                 )

　　3.2 兩個以上or 的實作方法 利用Disjunction方法可以實作

　　　　crit.Add(Expression.Disjunction()

　　　　.Add(Expression.Eq("order_state", 1))

　　　　.Add(Expression.Eq("order_state", 2))

　　　　.Add(Expression.Eq("order_state", 0))

　　　　); 

3.3 多個字段不同的值

NHibernate.ICriteria criteria = StorageBalanceDao.CurrentSession.CreateCriteria(typeof(StorageBalance));

        criteria.Add(

               NHibernate.Criterion.Expression.Disjunction()

                    .Add(NHibernate.Criterion.Restrictions.Eq("property_1", "value_1"))

                    .Add(NHibernate.Criterion.Restrictions.Eq("property_2", "value_2"))

                    .Add(NHibernate.Criterion.Restrictions.Eq("property_3", "value_3"))

            );